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 data source, it uses impersonation so that access checks for data import and processing can be performed.
Credentials used for impersonation are different from the credentials of the user currently logged on. Logged on user credentials are used for particular client side operations when authoring a model.
It is important to understand how impersonation credentials are specified and secured as well as the difference between contexts in which both the current logged on user’s credentials and when other credentials are used.
Understanding server side credentials
In SQL Server Data Tools (SSDT), credentials are specified for each data source by using the Impersonation Information page in the Table Import Wizard or by editing an existing data source connection on the Existing Connections dialog.
When data is imported or processed, the credentials specified in the Impersonation Information page are used to connect to the data source and fetch the data. This is a server side operation running in the context of a client application because the Analysis Services server hosting the workspace database connects to the data source and fetches the data.
When you deploy a model to an Analysis Services server, if the workspace database is in memory when the model is deployed, the credentials are passed to the Analysis Services server to which the model is deployed. At no time are user credentials stored on-disk.
When a deployed model processes data from a data source, the impersonation credentials, persisted in the in-memory database, are used to connect to the data source and fetch the data. Because this process is handled by the Analysis Services server managing the model database, this is again a server side operation.
Understanding client side credentials
When authoring a new model or adding a data source to an existing model, you use the Table Import Wizard to connect to a data source and select tables and views to be imported into the model. In the Table Import Wizard, on the Select Tables and Views page, you can use the Preview and Filter feature to view a sample (limited to 50 rows) of the data you will import. You can also specify filters to exclude data that does not need to be included in the model.
Similarly, for existing models that have already been created, you can use the Edit Table Properties dialog to preview and filter data imported into a table. The preview and filter features here use the same functionality as the Preview and Filter feature on the Select Tables and Views page of the Table Import Wizard.
The Preview and Filter feature, and the Table Properties and Partition Manager dialog boxes are an in-process client side operation; that is, what is done during this operation are different from how the data source is connected to and data is fetched from the data source; a server side operation. The credentials used to preview and filter data are the credentials of the user currently logged on. Client side operations always use the current user’s Windows credentials to connect to the data source.
This separation of credentials used during server side and client side operations can lead to a mismatch in what the user sees using the Preview and Filter feature or Table Properties dialog (client side operations) and what data will be fetched during an import or process (a server side operation). If the credentials of the user currently logged on and the impersonation credentials specified are different, the data seen in the Preview and Filter feature or the Table Properties dialog and the data fetched during an import or process can be different depending on the credentials required by the data source.