Setting up SQL Linked Server to SSAS and Avoiding Kerberos Double-Hop in RedGate

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:

USE master

/* Show information about linked servers */
EXEC sp_linkedservers
EXEC sp_helpserver
EXEC sp_helplinkedsrvlogin

/* Add new linked server */
EXEC sp_addlinkedserver
@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 */
EXEC sp_addlinkedsrvlogin
@rmtsrvname= ‘lsSSAS’,
@useself= ‘TRUE’,
@locallogin= NULL,
@rmtuser= ‘username’,
@rmtpassword= ‘password’

/* 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.

Address errors:

[7202] 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.

[7303] 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.”

[7390] The requested operation could not be performed because OLE DB provider “MSOLAP” for linked server “lsSSAS” does not support the required transaction interface.

About these ads

3 thoughts on “Setting up SQL Linked Server to SSAS and Avoiding Kerberos Double-Hop in RedGate

  1. Pingback: Tweets that mention Setting up SQL Linked Server to SSAS and Avoiding Kerberos Double-Hop in RedGate | TechNullogy --
  2. awesome thanks for posting, you put me on the right track for a double hop issue we had for using the MSOLAP driver. I ended up using a local sql server account do run my scripts rather than a domain account

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s