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

Question

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

• Marcello Miorelli: the SET FMTONLY OFF; does not work with SSAS.I have removed it from my queries to the SSAS servers.

Answer 1

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