INF: How to Set Up Security for Linked Server to OLAP Services (242025)

SUMMARY

A linked server configuration for OLAP Services allows SQL Server to execute commands against OLAP Services using the OLE DB provider for OLAP. The following article in the Microsoft Knowledge Base provides information on how to set up linked server to OLAP services and send distributed queries:

218592 HOW TO: Perform a SQL Server 7.0 Distributed Query with OLAP Server

This article explains some security considerations when linking to OLAP Services.

MORE INFORMATION

When setting up linked services to OLAP Server, the following should be considered:

If the login is made to SQL Server using SQL Server security (login name and password), SQL Server passes the credentials of the SQL Server services startup account to OLAP services for authentication. If SQL Server authentication is used, then setup SQL Server services to run under either a local or domain user account rather than using SYSTEM account.
If the login is made to SQL Server using Microsoft Windows NT authentication, then SQL Server passes the credentials of this Microsoft Windows NT account to OLAP services.

Depending on the login made to SQL Server, either the SQL Server services startup account or the Microsoft Windows NT account should have access to OLAP services. This can be done in one of the following two ways:

Make this account part of the 'OLAP Administrator' local group on OLAP server computer.

-or-
Create a database role within OLAP Manager and provide this role with read/write access to the cubes.

Also, note that since the login name and password cannot be used to access OLAP services, the linked server login mappings do not serve any purpose.