Friday 9 August 2013

Linked Server for Analysis Services

Following command will create a linked server on your database server instance named “SSASSERVER”.
EXEC master.dbo.sp_addlinkedserver
@server = N'SSASSERVER', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser
@datasrc=N'ServerName', -- machine or instance name  that host Analysis Services
@catalog=N'CubeDatabase' -- 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 transferred from one server to the other).

An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Connectionlnfo)
Cannot initialize the data source object of OLE DB provider MSOLAP’ For linked server "SSASSERVER",
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 remotely 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:
EXEC master.dbo.sp_addlinkedserver
@server = N'SSASSERVER', -- name of linked server
@srvproduct=N'MSOLAP',
@provider=N'MSOLAP', -- see list of providers available on SQL Server under Linked Server node in SSMS Object Browser
@datasrc=N'ServerName', -- machine or instance name  that host Analysis Services
@catalog=N'SimplifiedCube' -- Analysis Services database (cube)

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname=N'SSASSERVER',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myDomain\Login',
@rmtpassword='########'



Now you may be able to execute queries using linked server by openquery method.
Select * From OpenQuery(SSASSERVER, 'mdx query')




1 comment:

  1. What if SSAS is set to accept anonymous connections? Would that work as a fourth option?

    ReplyDelete

Azure AzCopy Command in Action

Azure AzCopy Command  in Action -  Install - Module - Name Az - Scope CurrentUser - Repository PSGallery - Force # This simple PowerShell ...