Understand the concept behind Impersonation within a Tabular Model

Question

Hello,

I'm in need of understanding how impersonation works with a tabular model and Analysis Services Server. I have a tabular model that connects to a data source using SQL Authentication. For impersonation, I use the Service Account option. Also, the tabular model is in DirectQuery mode. From the documentation, it seems impersonation is used to fetch data from the data source and process the data on the Analysis Services Server. However, if the tabular model is in DirectQuery mode, the data is not processed or stored on the Analysis Services Server. How does impersonation work in this scenario?

Moreover, by using the Service Account option as my impersonation, does this account need to have access to the data source? I have reports that connect to the tabular model where the reports run successfully. The service account does not have access to the data source. So, I'm wondering how the reports are running successfully.

Answer 1

However, if the tabular model is in DirectQuery mode, the data is not processed or stored on the Analysis Services Server. How does impersonation work in this scenario?

No it's not, but the data is fetched in real time from the data source. However impersonation really only applies if you are using Windows Authentication. If you are using SQL Authentication the connection will use the username/password you have stored against the data source.

Moreover, by using the Service Account option as my impersonation, does this account need to have access to the data source?

Only if you are using Windows Authentication. If you are using other authentication modes where you save a fixed username/password with the connection the impersonation account does not need it's own access to the data source.

Answer 2

In terms of connecting to the data source it does not really matter.

In terms of security - using the Unattended Account or a specified Windows account with low privileges is probably safest. It's a really small risk, but if there was a bug in the data provider and that could be exploited somehow then an attacker could maybe run code on the server in the context of the impersonation account. So using a low privilege account gives you better protection from those sorts of attacks than using the service account.

Answer 3

As per the doc of SSAS impersonation :

Impersonation is the ability of a server application, such as Analysis Services, to assume the identity of a client application. Analysis Services runs using a service account, however, when the server establishes a connection to a datasource, it uses impersonation so that access checks for data import and processing can be performed.

Also there is an important tips from the doc :

When authoring a model, ensure the credentials you are signed in with and the credentials specified for impersonation have sufficient rights to fetch the data from the datasource.

Pratically we would suggest not using the SSAS Service Account and the impersonation account. It should be set with lowest permission and only run the SSAS Service.