Wow…that’s a long blog post title. First off, here are a few scripts that will show information about linked servers on the instance of SQL you’re connected to, create a linked server connection and even delete one if necessary:
/* Show information about linked servers */
/* Add new linked server */
@server=‘lsSSAS’, – local SQL name given to the linked server
@srvproduct=”, – not used
@provider=‘MSOLAP’, – OLE DB provider (the .4 means the SQL2K8 version)
@datasrc=‘ssas.domain.localinstance1′, – analysis server name SERVERINSTANCE
@catalog=‘AnalysisServicesCubes’ – default catalog/database
/* Add login credentials for linked server */
/* This DROPS a linked server */
–EXEC sp_droplinkedsrvlogin ‘lsAnalysisServicesCubes’,NULL
–EXEC sp_dropserver @server=’lsAnalysisServicesCubes’
The two “add” stored procedures are the ones I would use to create the linked servrer. I recently removed the “.4” from the @provider parameter of the sp_addlinkedserver query. Even though it’s designed for SQL2K8, which I am using, it seemed to cause some problems…not sure why.
I was still getting errors when pulling data and had read a few posts that suggested checking the “Allow Inprocess” check box. I already had it checked. However, I found that if I cleared it, clicked “OK” and then checked it again that it solved my problem. Open SQL Mgmt Studio and connect to the server, then browse to Server Objects, Linked Servers, Providers, then MSOLAP.
Next, double click MSOLAP, clear the “Allow inprocess” checkbox, click “OK”, then repeat the process so that “Allow inprocess” is CHECKED.
Red Gate & SSAS Linked Views
With this implementation I was I able to setup a SQL view to pulls data from SQL Server Analysis Services via the Linked Server and an MDX query. Basically, it’s pulling data from a cube directly into a view in SQL. The performance on the view isn’t the greatest, but it’s only used nightly as part of an update method in a Windows service.
Our team uses RedGate to synchronize schemas and data from our development environment to production. However, when it would try to synchronize these views, I’d run into the dreaded Kerberos double-hop issue. If there is a change in ANY of these views, the change must be made on all other instances of SQL ON the SQL server. RedGate will error out when trying to sync these views because of Kerberos double-hop. Also, if you try and copy the “ALTER” query to update the view on other servers, and run it from SQL Mgmt Studio on your local machine, you’ll run into the Kerberos double-hop issue as well. These view changes will ONLY work if you are logged into the SQL server locally. Hence, if the views are all the same, then RedGate won’t have anything to update.
This is primarily the case because SQL and SSAS are on separate servers. If they were on the same server (which is NOT recommended), then you’d probably never see the Kerberos issue.
 Could not find server ‘lsSSAS’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
 Cannot initialize the data source object of OLE DB provider “MSOLAP” for linked server “lsSSAS”.
OLE DB provider “MSOLAP” for linked server “lsSSAS” returned message “Either the user, DOMAINusername, does not have access to the DataWarehouse database, or the database does not exist.”
 The requested operation could not be performed because OLE DB provider “MSOLAP” for linked server “lsSSAS” does not support the required transaction interface.