DirectQuery impersonation options explained

In my previous post about impersonation, I mentioned that DirectQuery impersonation was a bit tricky. That is because there is a special project property called Impersonation Settings that determines the credentials to use when connecting to the data source. The MSDN documentation of this property is a little thin, so let’s walk through the details.

If I were to write the MSDN on the Impersonations Settings property, I might write something like this:

Even that description is a bit convoluted. Here is a pictorial example, which may aid understanding.

Let’s assume we have the following:

• There is a user that has admin access to the AS instance and read access to the SQL Server data source. In this example, it is me – REDMOND\cathyk. Also, for illustration purposes, I am running the AS service account as myself.
• There is a second user that does not have access to either the AS instance or the SQL Server data source. In this example, it is REDMOND\sqlcl01.

Now we can create our model. I create a tabular model and set it to DirectQuery mode by selecting the Model.bim file, viewing the property grid, then setting the DirectQuery Mode property to On. Next, I import data from my SQL Server data source, specifying either REDMOND\cathyk or the service account for the impersonation credentials (in this case they are the same). I then open the Role Manager and create a Read role with REDMOND\sqlcl01 as a member. Now let’s run some queries for REDMOND\sqlcl01 in three scenarios.

Scenario 1: Using default impersonation settings

I right click the project, then select Properties. Here are my settings:

Query Mode=DirectQuery means that a VertiPaq cache is not constructed for the model, so end users always use DirectQuery to connect to the data source at query time. Impersonation Settings=Default says use the credentials from the import wizard to connect.

F5 to deploy the model. Now let’s see what happens when REDMOND\sqlcl01 tries to connect:

Success. REDMOND\sqlcl01 sees the data because the credentials on the data source, and not the current user’s credentials are used. To verify that this is indeed the case, we need two profiler traces.

First, let’s look at the Analysis Services Profiler trace. You will need to customize the trace events by clicking “Show all events”, then selecting Direct Query Begin, Direct Query End, VertiPaq SE Query Begin, VertiPaq SE Query End. Now check the results of the trace for the DAX query issued above.

You can see from the trace that DirectQuery was used to connect to the data source. Now we check the Profiler trace for the SQL Server instance, which shows the credentials:.

You see that the credentials specified on the data source, in this case REDMOND\cathyk, are passed along and used to execute the query. Thus, we have shown that using the Default impersonation setting allows users without read access to the underlying data sources to get data from SQL Server.

Scenario 2: Impersonating the current user in a DirectQuery only model

Now, let’s reconfigure our model and try a different setting. Let’s change the impersonation credentials to impersonate the current user, as pictured below.

F5 to redeploy the model. Now let’s see what happens when REDMOND\sqlcl01 tries to connect:

You can see that the query fails. The complete error message in this case was “A connection could not be made to the data source with the DataSourceID of 'bc0bbbec-9cc4-4c53-aca9-b3569b0edeb3', Name of 'SqlServer localhost BikeSales'. OLE DB or ODBC error: Login failed for user 'REDMOND\SQLCL01'.; 28000.” If you look in Profiler for the SQL Server data source, you see that there was no query executed by Analysis Services this time.

Observe the Profiler trace for AS:

What that shows is that AS tried but failed to connect directly to the data source as REDMOND\sqlcl01. Thus, we have showed that impersonating the current user can cause connection attempts to fail even though the data was imported successfully in the import wizard.

Scenario 3: Impersonating the current user in a hybrid model

Just to make things more complicated, now let’s consider what happens if we have configured our DirectQuery model to work in a hybrid mode, so that users can hit either the VertiPaq cache or the DirectQuery data source directly. Let’s change our deployment settings as pictured, setting the Query Mode to "In-Memory with DirectQuery”.

F5 to redeploy. This time you see that processing happens after deployment, which makes sense because we have a hybrid mode. Now let’s see what happens when REDMOND\sqlcl01 runs a query:

Query succeeds. Why? The Profiler trace on the SQL Server data source yields no clues, as there are no trace events showing.  The Analysis Services Profiler trace, however, is much more illuminating:

User REDMOND\sqlcl01 hit the VertiPaq cache to get the query results. This is as expected for the hybrid mode. So, even though the current user is not authorized to connect to the SQL Server data source, she can still see the data because it already existed in the VertiPaq cache and the results were fetched from there.

What’s the bottom line – which impersonation settings should be used?

Impersonating the current user has its pros and cons:

• Con: you have to configure access on both Analysis Services and the SQL Server data source
• Pro: the security model on SQL Server is much fancier than the one for tabular models, so you might find the pain of configuring access worth the gain of the fine grained security model
• Con: the behaviour of Analysis Services can be confusing in hybrid mode, as hitting the VertiPaq cache yields extremely different results from hitting the data source directly

Using default impersonation settings has its pros and cons:

• Pro: conceptually much simpler, same account is always used to access data, and it’s the same account you specify in the import wizard
• Con: all users with read access to the model can read everything. Row level security is not allowed for DirectQuery models in SQL Server 2012. Limiting access to a subset of data to some users is impossible.

The right answer for you depends on your use case. Have fun