Recently I was helping a client with their Analysis Services database structure. In this particular environment, there are a lot (and I mean A LOT) of AS db’s. One of the first things I noticed is that almost every single one was using the service account to do the impersonation when processing, which is a very very bad idea. “But Chris, why is that bad idea? Don’t we want to use dedicated service accounts for our SSAS stuff?” The short answer is yes, you do want dedicated service account for SSAS, just not that one. Below I’d like to highlight each of the different impersonation modes available and there use. And what’s the difference between the data source screen with credentials and the impersonation anyways?
First off, the difference between the credentials on the data source screen and the impersonation mode. When you are developing an Analysis Services database, you can configure and develop against that database using either a) windows authentication, or b) SQL Server Authentication (or Oracle, or whatever). This is what AS uses to read the database from the data source so you can develop against it. Impersonation is what happens server side after you deploy. Once you’ve deployed it to your environment, there is no real “user” (i.e. you) reading from the data source. Impersonation is what SSAS uses to do this read after deployment from the data source to get the data it needs. You can also think of it as “After I’ve deployed this, Monseur Analysis Services, I want you to pretend to be X”, where X is one of those impersonation modes we’re about ready to cover.
Use a specific windows username/password:
This is the Microsoft recommended best practice. Don’t just use a regular user though, like you or me or the production DBA’s credentials. You want to create a designated service account for Analysis Services to use that it can use to read from an EDW or your source. Ideally, your data source is coming from an EDW where 1 login should cover multiple SSAS databases. If you have say, 10 different SSAS database in your environment all coming from different datamarts/databases/servers, you should create a separate SSAS service account for each one. But that’s a lot of maintenance!!!! Well, hold on and think about what’s actually in your database. A lot of the times, SSAS databases/cubes are built for mega important business areas that directly impact the bottom line of your company. Do you really want 1 login to have access to everything!? Would you give that to a user? Probably not.
Use the service account:
When you are installing Analysis Services, you have to set up the service account that Analysis Services uses to actually run. For example, go to Start –> Services, find SQL Server Analysis Services, and click on properties. Click on the Log On tab. This is the account that was configured when SSAS was installed. In short, this means this is the account that is uses to run msmdsrv.exe, which lives in your install directory under $\Program Files\Microsoft SQL Server\Instance\OLAP\bin. If you ever wanted to narrow down to 1 thing on Analysis Services and say what runs it, this is it. (There’s a config file in there and some other stuff, but that would digress too much and is best to cover in another post :)) So in short, what does this mean? And why do care about it when you’re just trying to process a cube? This means that the service account that you select to just do a process operation also runs the heart of Analysis Services. You’d have to give this account access to any data source where SSAS would need to reference data from. Couple in an Enterprise with lots of servers and databases and you have an Analysis Services administrative account that runs the environment having at least read access to multiple servers/databases.
Now one of those servers has IIS on it, it gets a SQL injection attack, they steal the SSAS admin password, and now they have unrestricted free access to afore-mentioned mega important business areas that impact the bottom line (probably some financial data and account numbers too). All from hacking a cube.
In short, don’t use the service account in order for your environment to remain secure.
I left inherit and “Use the credentials of the current user” out of this post, as they’re pretty uncommon to use and additionally, “Use the credentials of the current user” can’t even be used to process things server side.