Service Account for processing Analysis Services cubes/databases

Yesterday  I worked onsite at a client with my boss to deploy an SSIS masterpackage. During the deployment it turned out that cube processing is an issue because it cannot be done using the SQL service account despite the fact that it's the same one for AS and the database engine. Or at least that was the case there but I didn't want to believe there is no solution for that.

It always failed with the somewhat famous error:

OLE DB error: OLE DB or ODBC error: Login failed for user 'servername\sql_svc'.; 28000; Cannot open database "DataBase" requested by the login. The login failed.; 42000.

After googling to find some clever stuff about that, a Microsoft KB came up but it gave me no help. On the net I've found very little information about this error in terms of Analysis Services so I had to come up with a useable method myself. Though I'm sure not I'm the first using it so it's not a big deal but might help to people stuck with that for some time and looking for a solution on the net.

(I highlight with blue the parts which are displayed in SSMS.)

  1. Create a brand new, clean service account on my server
  2. I gave no membership to it (no Admin, no SQLServerMSSQLUser, ...)
  3. Change the service account usage in SQL Server Configuration Manager
  4. Create a login in SSMS on the very instance the data warehouse is sitting
    • Open SSMS
    • Connect to the appropriate database instance
    • Expand (instance level) Security
    • RightClick Logins => New Login...
    • Click the Search button next to the Login name field
    • find and add the service account recently created
    • navigate to User Mapping
    • tick all the databases that are used for processing the AS cube(s) and tick the db_datareader role one-by-one
    • the result of this that a new user is created in each of the databases as db_datareader but it's easier to manage it on instance level if you need to use multiple databases for AS cubes.
  5. Connect to the Analysis Services instance
  6. Expand Databases, navigate to your AS database\Data Sources and double click on the data source(s)
  7. Find Impersonation Info under Security Settings and click on it so the 3 dots on the right are now displayed.
  8. Click on the 3 dots, choose the "Use the service account" option and then OK it.

Voilá, try processing the cube and you should find no error! If you do just flick me an email and I'll try to help but basically this should work. My server does not really have any special settings that differs from usual installations but "You never can tell with..." Microsoft products :)

Published Thursday, June 24, 2010 7:26 AM by MartinIsti


No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems