SQL SSAS - Deploying error

2
votes

When I start deploying, I get this error:

Internal error: The operation terminated unsuccessfully.

Errors in the high-level relational engine. The database operation was cancelled because of an earlier failure.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Dim Customer', Name of 'Customer' was being processed.

OLE DB error: OLE DB or ODBC error: Login failed for user 'NT Service\MSSQLServerOLAPService'.; 28000; Cannot open database "AdventureWorksDW2012" requested by the login. The login failed.; 42000.

What's wrong? and how can I fix it?

4
sql-serverdeploymentssas
See that Login Failed for user ...... message - that's what you need to address; the user currently trying to connect has no permissions to that database in question. Fix this, and you'll have your solution - marc_s

4 Answers

2
votes

Had the same problem and Daniel's Meresa answer was helpful. The detailed resolution in may case was to run SQL Management Studio and in Object Explorer select Security - Logins - Right click - "New Login...". Fill in the following login name "NT Service\MSSQLServerOLAPService" and check sysadmin in Server Roles. It should be enough.

2
votes

The error says, you need to add 'NT Service\MSSQLServerOLAPService' user to "AdventureWorksDW2012" database. You can do this in SSMS. Connect to your "AdventureWorksDW2012" database. Under Security-> User, add new user 'NT Service\MSSQLServerOLAPService' with db_datareader, db_owner(Optional) property [Membership, Owned_Schemas tab]

0
votes

Have a look at the impersonation options on the data source you defined in analysis services that holds the source data of the customer dimension. There you can configure what credentials SSAS should use to connect to the data source.

MSDN entry on impersonation options

0
votes

make sure you add the user 'NT Service\MSSQLServerOLAPService' into the database and assign the appropriate role read...also make sure to select use service account option under impersonation option in the cube source.