Browse SSAS Tabular model in SSMS

Fix “login failed” error while editing Table Properties in SSAS Tabular via Visual Studio

Just a quick post regarding a strange problem I encountered while working on an SSAS Tabular model project.  Built on the same Vertipaq technology as Power Pivot, it’s very easy to get started and produce quick, efficient data models.  Unlike Power Pivot, which runs as an Excel add-in, SSAS Tabular models are developed in Visual Studio via the SQL Server Data Tools (SSDT) Business Intelligence add-on.  However, once you load up the development environment, it’s almost exactly the same, the only difference really being that when you build a tabular model in VS, it is developed against a temporary cube on a pre-installed SSAS Tabular instance.  Power Pivot just works entirely in memory, without requiring an SSAS instance.

Unable to edit Table Properties?

Somewhere along the line, this different approach to the Tabular model meant that I started encountering an unusual error whenever I tried to edit any of my model’s tables.  Once you create a table in your data model in either Power Pivot or SSAS Tabular, you can edit the properties of that table by changing the underlying query to bring more columns from the data source or change the data selected via filters.  However, I found that whenever I tried to change the column definition in my tabular model, I was greeted with the following error:

OLE DB or ODBC error: Login failed for user ‘xxxxxxxx’.; 28000.

A connection could not be made to the data source with the DataSourceID of ’2741e576-ed3f-43dc-863b-17715374870b’, Name of ‘my_connection_name’.
An error occurred while processing the partition ‘Data_654aaced-1fd2-4dd3-9bbd-adb028448b83′ in table ‘Data_654aaced-1fd2-4dd3-9bbd-adb028448b83′.
The current operation was cancelled because another operation in the transaction failed.

The weird thing is, that I can still process the table and load in new data from the data source, I just can’t change the table definition at all.  This suggests that processing the table connects in a different manner to changing the table structure.

Working with SSAS Tabular in Visual Studio

Browsing an SSAS Tabular model in SSMS

Working on a tabular model cube in Visual Studio creates a temporary cube on your specified workspace SSAS instance, which you can access via SSMS

When you first open or create an SSAS Tabular project, it asks you to connect to an SSAS Tabular instance that can be used as a workspace.  This is so that VS can generate a temporary tabular cube while you’re working away.  If you connect to your SSAS Tabular instance using SQL Server Management Studio (SSMS), you’ll see the temporary cube on there.  In the interest of whittling down the variables, I decided to make sure that I could process the tables okay from the temporary cube via SSMS.  And guess what?  Same error.  That seemed to suggest that the connection between the temporary cube and the data source wasn’t right, and that processing my table in Visual Studio used a direct connection, while changing table definition via Table Properties goes through the temporary cube.

So, I checked the connections section of the temporary cube, and re-entered the password for my SQL login there, which worked!

It looks like Visual Studio hadn’t copied the password information from my connections in Visual Studio across to the temporary cube.  Adding the password to the connection in the temporary cube solved the issue.  I tried playing around with the “Save password” checkbox but neither option seemed to make any difference.  As a quick test, I tried to process the tables again via SSMS, which also worked this time.

Solving the “login failed 28000″ issue

SSAS Data source connection

Try adding the password again to the data source’s connection string property via SSMS

So, if you encounter the above error while trying to change your Table Properties in an SSAS Tabular project in Visual Studio, try the following steps:

  1. Connect to your workspace SSAS Tabular instance using SSMS.
  2. Try and process one of your tables via SSMS, see if you get the error.
  3. Expand the “Connections” tree item and right-click the offending connection > Properties.
  4. In the “Connection String” setting, click the ellipsis and enter your password on the resulting dialog.
  5. “Test Connection” to confirm, and “OK” out of there.
  6. You should now be able to edit your table in Visual Studio!

 

While this workaround gets past the issue, I’m not sure if there’s a setting somewhere to do this automatically, or why exactly it happens.  If you’ve run into this before, or you have some answers/better solution, please get in touch via the comments form below.

Business Intelligence and so-called "Big Data" Architect/Developer. Mainly work with Microsoft's BI stack but love to expand my horizons with different tech. Football daft, and also an NFL fan. Partial to a spot of golf and Xbox, too.

Leave a Reply

Next ArticleOptimising SSIS to read from a view using OLE DB Source