Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Kerberos SQL 2008 Linked Server to MSOLAP Expand / Collapse
Author
Message
Posted Thursday, April 23, 2009 3:24 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 1:45 PM
Points: 700, Visits: 817
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.



Post #703643
Posted Thursday, April 23, 2009 9:52 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, August 15, 2014 5:29 AM
Points: 431, Visits: 978
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
Post #703732
Posted Friday, April 24, 2009 9:29 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 1:45 PM
Points: 700, Visits: 817
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.


Post #704072
Posted Monday, May 17, 2010 8:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 4, 2014 4:00 AM
Points: 215, Visits: 194
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.
Post #922937
Posted Tuesday, May 18, 2010 6:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 1,249, Visits: 6,699
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
Post #923503
Posted Tuesday, May 18, 2010 11:36 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, September 15, 2014 1:45 PM
Points: 700, Visits: 817
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




Post #923779
Posted Thursday, March 6, 2014 2:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 3:11 AM
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
Post #1548122
Posted Thursday, March 6, 2014 5:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 1,249, Visits: 6,699
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.
Post #1548218
Posted Thursday, March 6, 2014 6:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 3:11 AM
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
Post #1548242
Posted Thursday, March 6, 2014 7:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 1,249, Visits: 6,699
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.
Post #1548267
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse