Data Warehousing and Business Intelligence

27 December 2010

Q&A on: Impersonation

Filed under: Analysis Services — Vincent Rainardi @ 10:05 am
Tags:

Q: What’s the difference between impersonation and connection string?

A: Let’s assume that the source database is in Oracle, running on UNIX. In BIDS, in the data source we have Connection String and Impersonation Information:

  1. Connection String: The credential specified here is the database user account (user ID and password) that Oracle will accept, allowing to read the rows on its tables.
  2. Impersonation Info: This the Windows credential/user account under which Analysis Services will be running when it tries to connect to that UNIX server to talk to Oracle.

For the number 1 above, we need to ask the Oracle DBA (or Sybase, Informix, DB2, etc depending on what the source RDBMS is) to create a database user account for us. If the RDBMS is running on Windows, this can be a Windows user account, if this account is authenticated by the RDBMS. If the source RDBMS is a SQL Server, then this can be a SQL Server login.

Number 2 must be a Windows account, because Analysis Services runs on Windows. Let’s say that our SSAS server is running (all the time) under a service account called Domain1\ServiceAccount1. In the example above, when SSAS connects to the UNIX server where the Oracle is running, we may want SSAS to connect using Domain1\ServiceAccount2 which has fewer privileges than ServiceAccount1. In this case ServiceAccount2 is the impersonation account.

Q: Are the connection string & impersonation in SSMS the same as the ones in BIDS?

A: Yes, they are the same. BIDS is just a tool to define connection string & impersonation. On the BIDS’ Solution Explorer, when we right click on Data Source, we are defining the connection string and the impersonation information. When we deploy the AS DB, these 2 things are copied across (implemented) to the AS server. That’s what we see in SSMS.

Q: Can we process a cube from a SQL server source using SQL Server authentication?

A: No, we can’t “process” a cube using a SQL Server account. SSAS needs a Windows account to impersonate when processing a SSAS database. “Process” doesn’t mean just connecting to the data source. “Process” means connect, read data, populate the dimensions and cubes, calculate aggregate and indexes, etc.

So yes, you can connect to the SQL Server source database using SQL Server authentication, but you can’t process the cube using a SQL Server account.

Q: If we can access the source database using connection string, why do we need impersonation?

A: If you use user name and password in the connection string, then they are used to connect to the source database. The impersonation account is not only used to connect to the source relational database, but to maintain the AS OLAP DB.

Q: Why can’t I use my Windows user account for impersonation?

A: You can use your Windows account, but there is a disadvantage when the cube is migrated to Production. Because a) if you use your user account to connect to the relational DB, other developers can’t maintain the BIDS project, and b) when the project is deployed to Production it won’t run because your account has access to the dev database, but not to the prod database. Using service account solves these problems. Impersonation is to specify which account to be used by the cube to connect to the relational database.

Analysis Services is a Windows service, running as SSAS service account. (to view this, double click the SSAS Service from control panel, then click on the Log On tab, see screenshot below).

The SSAS service account does not necessarily have access to the database. Hence the concept of impersonation: at run time the SSAS service account pretend to be (impersonate) or uses the impersonation account to access the database.

Q: How many modes of impersonation are there and what are the differences between them?

A: There are four impersonation modes:

a) Use a specific Windows user name & password: a domain user account created specifically for this purpose. This is the best practice because all the impersonation account need is a read access to the source database.

b) Use the service account: the SSAS service account usually set as a powerful domain account which has access to multiple SQL Servers. Using the SSAS service account gives you access to many databases in many SQL Server. You don’t have to worry about which account have access to which databases as a) above

c) Use the credential of the current user
SSAS service will use your windows account to access the database. When the cube is under development this is handy if you have access to the dev database. This way you can start the development straight away, with out waiting for any account to be setup. If you handover the BIDS project to your colleague developer however, your colleague needs to have access to that source database too. Otherwise he can’t process the cube.
Note that if you do this, when the cube is moved to production, you will need to change the account to the proper production account like a) above, or use the service account like b) above. As a developer, your Windows account does not normally have access to production databases. I’ll be surprised if it does because it creates a security issue.

d) Inherit
SSAS will use whatever specifies on the Database Properties. To view the Database Properties used for this, in SSMS, right click a SSAS database and choose Properties. Under Security Settings, there’s Data Source Impersonation Info. This is what Inherit uses. See screenshot below.

5 Comments »

  1. Vincent, this was great timing as it’s something I was looking into today. Nicely summarized, as usual.

    As a sidenote, I really like how your site is structured. The subject-oriented nature is refreshing since it’s different from so many other blogs.

    Keep up the great content!

    Comment by Melissa Coates — 30 December 2010 @ 8:45 pm | Reply

  2. Awesome article, I’m bookmarking, thanks ! (currently at work and I cuoldn’t find better info online, the microsoft website is so awful at explaining this stuff)

    Comment by alpha — 17 August 2012 @ 8:42 am | Reply

  3. […] security and impersonation between SQL Server service, analysis services, and front end; Link, Link2, Link3, • Implement Dynamic Dimension Security within a cube (data level security); Link, Link2 […]

    Pingback by Preparation for the 70-467 SQL BI exam | x86x64 — 1 November 2015 @ 4:51 pm | Reply

  4. Very nice article. But my question is why is SSAS different from SSIS? Even SSIS also runs as a service and it also connects to data sources, but it does not use “Impersonation” option in it.

    Comment by Natarajan — 26 May 2016 @ 12:54 pm | Reply

    • Hi Natarajan, because unlike SSIS, SSAS doesn’t only need to connect to the data sources, but also need to process the cube which can’t be done using the connection account.

      Comment by Vincent Rainardi — 28 May 2016 @ 10:48 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: