PRB: OLAP: MSSQLServerOLAPService Should Use Domain Account When Using Integrated Security

• Microsoft SQL Server OLAP Services version 7.0

SYMPTOMS

When using OLAP Services to pull data from a Microsoft SQL Server using trusted or Windows NT authentication security, the MSSQLServerOLAPService service must be configured to run under a domain or local user account. OLAP may fail to process dimensions or cubes if the MSSQLServerOLAPService service is configured to run under a local system account. The processing will fail with the following error message:

ODBC error:: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '\'.;28000;Time: current date and time
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '\'.;28000;Time: current date and time

MORE INFORMATION

By default the MSSQLServerOLAPService service is installed to run under a local system account. When accessing a SQL Server that is configured to use Windows NT authentication, the OLAP Manager will not be able to process any data on SQL Server. This occurs because OLAP uses the currently logged in user account when defining and building dimensions and cubes. However, the task of processing is handed off to the MSSQLServerOLAPService service.

When the MSSQLServerOLAPService service is configured to run under a local or domain user account, the account must be a member of the OLAP Administrators group on the OLAP server. This will grant it permissions to access the repository, registry, and data directory on the OLAP server.

In addition to being a member of the OLAP Administrators group, the account must also have the appropriate permissions on SQL Server. The permissions required will vary depending upon the type of storage structure selected for the OLAP cube. When using MOLAP storage, the account must have at least SELECT permissions on the source database. If ROLAP or HOLAP storage is used, the account must have at least SELECT and CREATE TABLE permissions on the source database.

By changing the MSSQLServerOLAPService service to run under a domain or local user account that is a member of the OLAP Administrators group on the OLAP server and that has privileges on SQL Server, OLAP Services will be able to successfully process.

Additional query words: online analytical processing

Keywords :
Version : winnt:7.0
Platform : winnt
Issue type : kbprb