Symptoms
When attempting to execute a project the following error message may appear:
Excerpt text from error message:
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Login failed for user "NT AUTHORITY\NETWORK SERVICE".: 28000; Cannot open database "{database name}" requested by the login. The login failed: 42000
The user name may also appear as "DOMAIN\COMPUTERNAME$" in which cause a virtual service account is in use for the SQL Server Analysis Services Service
Cause:
This error occurs because proper permissions have not been set for the account that is running the Analysis Services service. Ideally, this would be a managed service account, but may be "NT AUTHORITY\NETWORK SERVICE" or "NT Service\MSSQLServerOLAPService".
Resolution
-
Validate which account is running SQL Server Analysis Services.
- This can be done by opening the Services (services.msc) application on the server
- Locating the proper service for SSAS will reveal which account is in use.
In the case above, this account is NT Service\MSSQLServerOLAPService which is a virtual service account
Once the account has been identified, open SQL Server Management Studio (SSMS) and log onto the database engine.
-
Expand the Security folder, and if the user running the service already exists, right click on the user outlined in the error message and click Properties . In the below example the account selected was NT AUTHORITY\NETWORK SERVICE .
-
If the account does not already exist in SSMS, it needs to be added. Standard Windows authentication accounts can be added by right-clicking Logins>New Login... and then typing the fully qualified user name. If a virtual service account is in use, instead of using a Windows username, copy and paste the account directly into the user name box.
-
Once the account has been created, click on the User Mapping page and in the Map column check the Data Warehouse database. In this example the database ProdNavDwh was chosen.
-
In the "Database role membership for:" section, select the db_datareader permission.
-
Click OK.
Re-enter the Jet Data Manager and attempt to execute the project again. The process should complete this time without errors.
Comments