Impersonation in tabular models

  • Article

Here is a pop quiz on impersonation when connecting to a data source for tabular models.

Question 1: How many impersonation options are supported for tabular models?

  1. 2, as shown in the tabular designer
  2. 3
  3. 4, as shown in SSMS

Question 2: My impersonation credentials are validated in the Import Wizard.

  1. True
  2. False

Question 3: If I see data in the Preview and Filter pane in the Import Wizard, then the user specified in the impersonation page has access to the data source and my data will be imported.

  1. True
  2. False

Question 4: The account that I select for the impersonation option on the data source is always used when the engine connects to the data source to fetch data.

  1. True
  2. False

Got your answers? Good. The correct (and possibly surprising) answer is b for all. Let’s review our answers.

On question 1, the number of impersonation options. Tabular models support the following three options for connecting to a data source: impersonating a specific Windows user, impersonating the service account, and default (which for tabular models means inherit the credentials specified at the database level).

We only expose the first two options in the Import Wizard/Existing Connections dialog because we did not implement any UI that allowed users to enter the Data Source Impersonation Info property at the database level. Without a way to enter the database credentials, we figured it made no sense to show the default option in the Import Wizard since the option is useless at design time. This leaves us with the two options in the import wizard that you see today.

We expose four options in SSMS (impersonating the current user + the three supported options) because we share the impersonation option selection UI with the multidimensional and data mining projects. Since data mining projects support impersonating the current user when connecting to the data source, we show it in the dialog. We did not have time in Denali to implement a custom UI in SSMS for tabular models with only three options, so you’re stuck with the extra useless option.

On question 2, the tabular designer does not validate the impersonation credentials. The designer just passes the credentials along to the engine blindly. You won’t know that the credentials are bad until you try and fail to import the data.

On question 3, import can fail even after successful preview and filter. This is because preview and filter always uses the current user’s credentials when connecting to the data source. Why is this? Well, the tabular designer has to start the data provider itself so it can fetch the data for the preview and filter operation. We decided not to use the credentials on the impersonation page because you would not ever be able to preview and filter if you are impersonating the service account – the engine cannot share with us those credentials. Using the current user’s credentials was simpler.

On question 4, credentials used to connect to the data source. When your model is an in-memory (VertiPaq) model, yes the credentials specified on the data source are always used by the engine when connecting to the data source. However, when your model is a DirectQuery model, things get a bit trickier. There is an Impersonation Settings deployment property that determines the credentials used to connect to the data source. I will go over DirectQuery impersonation in another post.