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:
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.
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.
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.
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.
Impersonating the current user has its pros and cons:
Using default impersonation settings has its pros and cons:
The right answer for you depends on your use case. Have fun