How to resolve connection errors when loading data in SSAS Tabular project

This post actually applies to both multidimensional (cube) projects and Tabular projects which manage data source connectivity in a similar fashion.  The multidimensional design experience is different but the mechanics of the SSAS engine are essentially the same.

When loading data from a SQL Server data source into a Tabular model project, chances are very good that you’ll see an error on the first attempt.  I’ve been creating SSAS projects for over over 15 years and, even though this is a relatively simple process, I still usually work through the initial configuration in trial-and-error mode.  So, let’s call this a normal part of the process.

A Word of Advice

In Tabular model design, a common practice is to select all the tables and columns, rename objects and apply filters before finalizing the import step.  If something goes wrong and you can’t connect to the data source, you’ll lose all this work and will need to start over.  Test connectivity with one table before going through this aggravating cycle.

Here’s an example of an “ouch” moment after taking a half hour to make all the table/column selections.  Note the lack of a “Back” button!

Getting Connected

When connecting to a SQL Server data source in the SSAS project designer, there are two different identities used at different times.  When you initially create and test the connection in the designer, you will authenticate as yourself – the user logged into the local computer.  However, when you actually load data into the table or process the model, even in the designer, Analysis Services authenticates using the identity of the SSAS service.  If the SSAS instance is installed on your local development machine and you just used the default settings during setup, this is an account generated by SQL Server setup.  If you’re using a production-like SSAS server, your system administrator may have configured the service to run under a specifically-defined domain principal.  If the latter is the case, you’ll need to get your DBA or sys admin involved.  If you have an isolated development environment, with everything install on one computer, the following instructions will take care of it.

I’m running all my SQL Server services on my local development machine and created a Tabular project that will use the AdventureWorksDW2012 sample database. The SSAS instance I plan to use for my workspace database is the local instance.  First, let’s get the service account for the SSAS instance.  The easiest way to view local services is to right-click “My Computer” or “This PC” in Windows Explorer and select “Manage”.  Navigate to the Services node like so:

I have two SSAS instances installed and the default instance is called MSSQLSERVER.  Double-click to view the Properties dialog and select the Log On page:

As you can see, the default service account is “NT Service\MSSQLServerOLAPService”.  I’ve selected this text and copied it to the clipboard.  I’m done with the Services and Properties windows.

Now, I open SSMS and view the Logins for the SQL Server instance my SSAS project uses for the data source.  After connecting to the relational instance, expand the object explorer, Security and Logins.  Two logins are needed; one for myself and one for the SSAS service account.  Since this is my development machine and  had installed SQL Server, I already have a login for my Windows user account and I’m a member of the sysadmin server role which grants me access to every database on the server.  If you’re not a privileged user on this SQL Server instance, you will need a login created for your Windows credential and will need to at least be in the db_datareader role for the target database (example below).

To create the service account login, right-click Logins and choose New Login…  Paste the service account name here and move to the User Mapping page.  Select the target database and then check the box next to the db_datareader database role.

Click OK to save changes and you should be good to go.

Back in the Tabular model designer, I’m going to import only one small table to check connectivity.  Once verified, I’ll click the Existing Connections toolbar icon, select all my tables, select and de-select columns, apply filters and then load the data with successful results.  As soon as I see record counts, I know I’m in the clear…

Just One More Thing

(Columbo reference)

On a different but related topic since you’re getting your development environment ready… If you’re not a local administrator on the SSAS machine you’re using for the Tabular workspace database (typically a development machine), you need to make sure your Windows account is in the the Server Administrators role for the SSAS instance.  If this isn’t the case, you’ll get an error when you process the model.  To do this, connect to the SSAS instance in SSMS, right-click the server and open Properties.  On the Security page, use the Add button to add your Windows account.