Linked Server from SQL Server to SSAS not working - what is missing?

by marcello miorelli   Last Updated November 02, 2017 08:06 AM

I have a SQL Server 2012 Standard edition sp1 that has ssas also installed.

From this server I create a linked server to a ssas server called BIREPLON1, but when I try to access it, I get the error message shown below.

the funny thing is that when I go through GUI, by clicking Server Objects\linked servers\BIREPLON1 all seem to be ok (as you can see below on picture number 4).

But when I run the simple select:

SELECT CATALOG_NAME 
FROM OPENQUERY([BIREPLON1], 'SET FMTONLY OFF;SELECT [CATALOG_NAME] FROM $SYSTEM.DBSCHEMA_CATALOGS') as Radhe

I get this error message:

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "BIREPLON1".

Have I missed out on something?

enter image description here

enter image description here

enter image description here

enter image description here



Answers 2


I found this article here.

There it states that:

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:

Create the linked server:

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)

and adding a linked server login:

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

I have basically just done the number 3 and the linked server is working fine for me now. As you can see on the picture below.

enter image description here

Also I had problems with the timing out so I had to change this setting as well: (the default is 600)

EXEC sys.sp_configure N'remote query timeout (s)', N'6000'
GO
RECONFIGURE WITH OVERRIDE
GO

This can also be done through the GUI as per the picture below:

enter image description here

marcello miorelli
marcello miorelli
November 11, 2014 15:33 PM

thanks a lot ur star , save my life

user137801
user137801
November 02, 2017 07:42 AM

Related Questions





Analysis Services Project: Data Source per Enviroment

Updated February 20, 2016 03:02 AM