pointers, solutions and scripts for the SQL DBA
not intended to replace msdn, common sense or oxford commas

Thursday, June 11, 2015

Error Upon First Processing Your SSAS Database With A Service Account

This blog post is to document the easy fix to a problem that has become a rite of passage for the good folks (including myself) on the data warehouse development team I've been working with recently.

Here's the error text you've probably received the first time you try to process your Analysis Services database using a job or, in this case, an SSIS package calling an Analysis Services Execute DDL Task:
Executing ExecutePackageTask: D:\whatever.dtsx
Error: 0xC1060000 at Analysis Services Processing Task, Analysis Services Execute DDL Task: OLE DB or ODBC error: Login failed for user 'NT SERVICE\MSOLAP$SQL2K14'.; 28000; Cannot open database "DataWhatever" requested by the login. The login failed.; 42000.
Error: 0xC1120064 at Analysis Services Processing Task, Analysis Services Execute DDL Task: A connection could not be made to the data source with the DataSourceID of '4c67b909-250d-4b14-95cc-451d1c95bc9f', Name of 'SqlServer .sql2k14bi DataWhatever'.
Error: 0xC11F0056 at Analysis Services Processing Task, Analysis Services Execute DDL Task: An error occurred while processing the partition 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f' in table 'DimDate_168cd22b-ae72-4730-a054-4fd0fa6acc2f'.
Error: 0xC11C0006 at Analysis Services Processing Task, Analysis Services Execute DDL Task: The current operation was cancelled because another operation in the transaction failed.
Task failed: Analysis Services Processing Task
Warning: 0x80019002 at Main: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
The solution is quite simple.

The SSAS data cube might have been developed and processed just fine with your personal user account, but now that you've executed it with a service account for the first time, you need to grant it permissions. The SSAS Service account does not have rights to access your data source, in this case, your SQL Server 2014 instance.

Grant read-only permissions to the SSAS service account to the source database, for example, by creating a login and adding that login to the db_datareader role of the database, or granting SELECT permissions to the tables or schema needed to process the SSAS cube.

No comments: