Tuesday, August 21, 2012

SSAS 2012 Gotcha: Impersonation Info in Data Sources

I don't know if SQL Server 2012 got pickier about this or what. When we did our upgrade to SQL 2012 we actually replaced our Development and Quality Assurance (QA) servers. As a result, there weren't any databases on there to work with. In order to allow developers to deploy to these servers we created blank AS databases and added a role in the new DB that would allow them to deploy. Unfortunately, this didn't work like it used to (or at least how I thought it used to). Instead, the developers kept getting failures when they tried to deploy along with the following error message:
The ImpersonationInfo for datasource 'ASDBName' contains an ImpersonationMode that can only be used by a server administrator

The solutions (there are two or three) aren't terribly painful but they both require some form of administrator intervention.

All the solutions require two things:
  1. The data sources should be configured in the SSAS project to impersonate the service account. Yes, I know, that's what caused the error I mentioned earlier.
  2. A role in the AS database project that grants the developers full control over it.

The first solution is probably easier for the developers. In this one the BI Admin gets a copy of the SSAS project (from source control, right?) and deploys it to the server. Once it's deployed once by the administrator the developers can deploy over the top of it as long as they don't add any new data sources or change the impersonation setting for the existing ones. If the data sources change, the admin has to perform the deployment again.

The second solution requires a little back-and-forth between the developers and the admin. In this scenario the administrator creates the blank database as before. After that, the developers change the data sources to impersonate the current user. They will also want to set the project not to process upon successful deployment; otherwise, it will cause the whole thing to fail. A developer then deploys the database to the server and hands the baton off to the BI Admin. Once the database is in place, the administrator can change the data sources in the database to impersonate the service account and the developers do the same in the AS project. The developers should now be able to deploy to the server all they want (with the same caveats as option "one").

The third option -- I wouldn't call it a solution -- is to give you developers admin access to the server. I don't like this method but sometimes the hassle of the other choices just aren't worth it. You have to be careful that there are no databases with sensitive data on the server. If necessary, create separate instances of SSAS to hold sensitive data and only grant admin access to the developers working on those particular projects.

All that said, none of these are options for your Production environment. None. Of. Them. Only the BI administrator should be deploying to Production unless, of course, your builds are automated and go through a strict vetting process.

Got a better solution? I would love to hear it. I am stuck in option number three in the moment but bouncing some ideas around my head on how I might automate #1 or #2.

No comments:

Post a Comment