Jul 26, 2011

Linked Server for Analysis Services

Following command creates a linked server named AM_OLAP:
@server = N'AM_OLAP', -- name of linked server
@provider=N'MSOLAP', -- see list of providers avaoilable on SQL Server under Linked Server > Prover node in SSMS Object Browser
@datasrc=N'MyOlapServer', -- machine that host Analysis Services
@catalog=N'MyOlapDatabase' -- Analysis Services database (cube)

If database server and Analysis Services are on different machines, queries executed through it may have a problem with "double hop" (fact that security context cannot be trasfered from one server to the other):

An exception occurred while executing a Transact-SQL statement or batch.
Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server "OLAP_AM",
OLE DB provider “MSOLAP” For linked server “OLAP_AM” returned message “An error was encountered in the transport layer.”.
OLE DB provider “MSOLAP” for linked server “OLAP_AM’ returned message “The peer prematurely closed the connection.”. (Microsoft SQL Server, Error: 7303)

To resolve that problem you have 3 options:
1. Run SQL queries from data server (you need to be remotly connected to the database server)

2. Enable use of Kerberos on the database server

3. Set proxy account for linked server, so that MDX queries are executed in its context instead of in context of the user that is issuing t-sql query:  

EXECmaster.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AM_OLAP',@useself=N'False',@locallogin=NULL,@rmtuser=N'myDomain\MyLogin',@rmtpassword='########'
Now you may be able to use it in queries like this:
Select * From OpenQuery(AM_OLAP, 'mdx query')

No comments:

Post a Comment