Monday, March 3, 2014

SSAS - When processing cube(s), receive "Login failed for user 'DOMAIN\COMPUTERNAME$'"

Pretty simple, but writing this up because I'm diving back into SSAS for some performance testing. This information is for SQL Server 2012, but will be similar for previous versions.

You've created a new SQL Server database where your cubes will pull their data from, as well as a new SSAS database. When you create a datasource, the impersonation information tab is set to (default) "Use the service account". Normally I would create an network user to do this, but installing it locally and running tests, I went with the standard impersonation configuration.

Open your computer services to determine the name of the service account. I do this by right clicking on My Computer -> Manage, and expanding Services. For SQL Server 2012, look for this entry: 

SQL Server Analysis Services (if you have multiple versions installed, choose the version you are using, in my case 2012. I can see the Log On As is set to: NT Service\MSOLAP$SQL2012

Go to SSMS and expand the security tab, if you do not see the above login, create a new one and make sure they are mapped to the database where the data is being pulled into your cube: the one you created for the data source. Make sure the user has read access to the database by adding db_datareader role. Hit Ok to save.

Try to process your cube, you should be golden now.