Friends,
We are gonna discuss about the use of Impersonation tab in Data Source wizard of SSAS solution in this post. First lets discuss about WHY we need to impersonate and then different types in which we can impersonate.
When we design a cube in which data source is using windows authentication then it will be executed as current user. But, when you deploy the database to the server there will be no current user. In this case when a user requests AS to process an object it needs to know under what security context to connect to the data source. Essentially you need to get the AS service to impersonate another user in order to retrieve data from an external source on a user’s behalf.
There are FOUR types available in which you can impersonate Data Source.
1) Use a specific Username and Password.
2) Use the Service Account.
3) Use the credentials of the Current User.
4) Inherit (in SQL 2008 and above)/ Default (SQL 2005).
Now lets see where you can provide this impersonation information in SSAS Solution.
1) Open New Solution.
2) Add New Data Source.
3) Provide Database connectivity Details.
4) After providing data source details if you click on next then you can see a Wizard page to provide impersonate info for the data source as shown below.
Now lets see in detail the use of each impersonation type.
Use a specific Username and Password –
Select this option to have the Analysis Services object use the security credentials of a specified Windows user account.
• User name – Type the domain and name of the user account to be used by the selected Analysis Services object. The domain and name of the user account uses the following format:
<Domain name>\<User account name>
• Password – Type the domain and name of the user account to be used by the selected Analysis Services object.
When Analysis Services account does not have relational data base access then Use Specific User Name and password option is used.
Specific user name and password is passed to analysis server but it will be encrypted due to security. Example like when the data sources are like –
1) OLE DB/Microsoft Directory Services
2) SQL Server Compact Edition
Use the Service Account –
Select this option to have the Analysis Services object use the security credentials associated with the Analysis Services service that manages the object. The service account credentials will be used for processing, ROLAP queries, remote partitions, linked objects, and synchronization from target to source. For DMX OPENQUERY statements, local cubes, and mining models, the credentials of the current user will be used. This option is not supported for out-of-line bindings.
“Use the Service Account” option is mostly used by developers. In this, Analysis Services startup account information will be used to connect relational database.
Use the credentials of the Current User –
This is similar to the first option what we discussed i.e Specify a user account. If you selects this option then it will take the current logged in user details. Remember that this option is not supported for processing, ROLAP queries, remote partitions, linked objects, and synchronization from target to source.
Default/Inherit –
Analysis services will have Service Account and User Account. If you select this option then it will take the details of default user account . This option uses the default setting for the database for processing objects, synchronizing servers, and executing the OpenQuery data mining statements.
So guys, based on requirement Pick the best one that suits you. Happy Coding !!
Regards,
Roopesh Babu V
