Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Kerberos SQL 2008 Linked Server to MSOLAP


Kerberos SQL 2008 Linked Server to MSOLAP

Author
Message
Greg Grow
Greg Grow
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 957
Can someone please help me with the steps to allow for the "Double Hop" scenario using Linked Servers to MSOLAP.

Our Environment:
Computer A - SSAS (SQL 2008 64bit, uses a service account for log on)
Computer B - Has a Linked Server to Computer A (SQL Server 2008 Enterprise 64bit, uses a service account for log on)
Computer C - My Desktop with SSMS to test the linked server (2008 Developer 32bit)

When I open my SSMS session and connect to computer B to test the linked server it fails with the following error:

"Cannot set the initialization properties for OLE DB provider "MSOLAP.4" for linked server.. etc... etc... OLE DB provider for linked server returned message "The following system error occured: The system cannot find the file specified." Error 7373

Its interesting; if I log onto the host where the linked server is at with the same domain credentials, the test of the linked server works on the computer B host. And, it now works from my desktop computer C. Once I log off of Computer B, my computer C test begins to fail again. I am guessing that my credentials and profile is authenticating properly.

The Windows Adminsitrators say that everything is set up correctly with Kerberos and SPNs, but I still am not sure since I am unable to test successfully. Not sure what to try next.

Does anyone of have any lists or steps they used to get the double hop working from personal desktops?

Thanks
Greg G.



AShehzad
AShehzad
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 1138
The 7373 error code may refer to any errors in the ole db provider itself. Also you may check that ports for your server (1434 and 2383) are listening proper locally and externally.

=============================================================
Atif Shehzad
DBA
Zin Technologies
Greg Grow
Greg Grow
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 957
Thanks. I will have our network guy verify the ports are open. If I add Anonymous Users to the cube, they have access. I would imagine, that if the ports were having issues, it would prevent anonymous as well. We have a case with Microsoft open. Once I hear back from them, I will post the fix.



James Perry
James Perry
SSC Veteran
SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)SSC Veteran (216 reputation)

Group: General Forum Members
Points: 216 Visits: 234
Did you ever get this resolved. I have the same issue with SQL 2008 linked server to SSAS 2008. The linked server works fine on the server but when trying to activate it remotely get the cannot be intialized error. Also tried this on a test box where everything is local so SSAS and SQL on same server and again it works fine locally but from a client trying to use linked server it fails to initialise again.
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8258
You might look through a few articles like this to check your setup.
There's also a white paper our on Publishing Performance Point Server in an Extranet Environment that also applies to Sharepoint Server.
You don't give much details on your setup.
Here's a very Rough outline -
Servers and Service Accounts (Domain Account, not Local) need to be allowed to delegate.
SPN's need to be setup - takes Domain Admin.
If a web site is involved, this will need SPN's setup too.


http://support.microsoft.com/kb/319723



[url=http://msdn.microsoft.com/en-us/library/ee191523.aspx]


I also find setting this registry key on all machines helpful.
This forces Kerberos to use TCPIP instead of UDP.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters]
"MaxPacketSize"=dword:00000001

Greg E
Greg Grow
Greg Grow
Say Hey Kid
Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)Say Hey Kid (705 reputation)

Group: General Forum Members
Points: 705 Visits: 957
You may want to look around for the Double Hop issue. Once the SPNs were set up correctly, I didn't have any issues passing through authentication.

http://jesseorosz.spaces.live.com/Blog/cns!E322FD91218E57CF!329.entry



jeremyking77
jeremyking77
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 97
another thing i found you could do is use a sql server account which doesnt require kerberos delegation. This way you by pass the double hop issue
Well it worked for me anyway
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8258
jeremyking77 (3/6/2014)
another thing i found you could do is use a sql server account which doesnt require kerberos delegation. This way you by pass the double hop issue
Well it worked for me anyway


Maybe it would work for a linked server to SQL, but SSAS requires a Windows Domain Account.
SSAS knows absolutely nothing about SQL accounts, even SA account.
You might want to test a bit more connecting to SSAS.
jeremyking77
jeremyking77
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 97
in our case the linked server still uses a domain account for dealing with the MSOLAP driver

so in the TSQL query i could
1. Run the tsql query with a sql user account
2. This would then tell the linked server (via the domain service account) it needed to query the OLAP cube
3. Analysis Services (also running under another domain account) would respond

So in the case of the 1 step here, i was running it with a domain account intially, which wasnt working (and it is a sysadmin)
So i switched to using a sql user account, which did work
And i think its because there was some form of kerberos delegation trying to happen on my domain account i was using in step 1
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8258
So you were running the TSQL query from a 3rd machine as outlined in the original post?
That is where the double hop comes in.

Generally, with SSAS, you have some cube security setup to users or groups in AD.
So it would be of benefit to be passing the user credentials running the query in most cases.
In our case, it was required, as very few users were allowed to see all companies, regions, etc. in the cube.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search