When trying to Create/Update a Cube you receive the following error:
Cube generation failed: 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 '<YourPMDatamartName>' requested by the login. The login failed.; 42000. Errors in the high-level relational engine. A connection could not be made to the data source with a DataSourceID of '<YourPMDatamartName>'. Name of '<DataSourceName>
- This can happen when choosing 'Windows Authentication' to access SQL Server and the Analysis Services (SSAS) was set to run as 'NT AUTHORITY\NETWORK SERVICE' (see SQL Server Configuration Manager) and SQL Server is running as a different account and has not given the NETWORK SERVICE sufficient authority to read or update the DataMart database.
Note that in Analytics Maestro, the DataMart can still be created because it uses the current User's credentials. But when the cube is generated, it uses the SSAS 'service' logon credentials to access the SQL Server. In an installation using default settings this would normally be set up to work.
Steps to Resolve the Issue:
- The quickest solution is to use SQL Authentication – such as the 'sa' account or a SQL user ID specifically set up for this and given database read/write access. Then both the DataMart creation and the SSAS cube generation would use the same authorization against the SQL Server database.
If that is not desirable, or not within company policies, then do ONE of the following:
- In SQL Server Management Studio, add the 'NT AUTHORITY\NETWORK SERVICE' to the SQL Server; Security; Logins, and give it a sysadmin Server Role.
- You can alternatively give the 'NT AUTHORITY\NETWORK SERVICE' very specific access (i.e. db_datareader and db_datawriter) in the Security; Login; Properties; 'User Mapping' pane for the named DataMart database. However, you would have to update this if you created a DataMart database with a different name.
- In SQL Server Configuration Manager, you can also change the SQL Server to log on as 'NT AUTHORITY\NETWORK SERVICE' so it is the same as SSAS. You can also set both of them to use the LocalSystem account if they are on the same machine. This will automatically give SSAS the Windows Authentication that it needs to read the SQL Server DataMart database.