Development

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

By Graham 12 September 2013

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

Screenshot of SSAS Tabular models in the SSMS Object Explorer

Tabular Models can be browsed via Object Explorer just like regular database objects.

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

Screenshot of the Connection Properties dialog in SSMS

You can modify connection details via the Connection Properties dialog to resolve any issues

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, then 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.

comments powered by Disqus