Post reply

Kerberos SQL 2008 Linked Server to MSOLAP

  • April 23, 2009 at 3:24 pm

    #208535

    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.

  • April 23, 2009 at 9:52 pm

    #983368

    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.

    DBDigger Microsoft Data Platform Consultancy.

  • April 24, 2009 at 9:29 am

    #983632

    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.

  • May 17, 2010 at 8:33 am

    #1168296

    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.

  • May 18, 2010 at 6:25 am

    #1168750

    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

    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

  • May 18, 2010 at 11:36 am

    #1168992

    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

  • March 6, 2014 at 2:55 am

    #1694579

    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

  • March 6, 2014 at 5:45 am

    #1694662

    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.

  • March 6, 2014 at 6:28 am

    #1694683

    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

  • March 6, 2014 at 7:16 am

    #1694704

    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.

  • March 6, 2014 at 7:54 am

    #1694732

    yeah, from a 3rd machine

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply