Double Hop problem!

  • Hi,

    I'm trying to overcome a 'Double Hop[/url]' issue without any luck, and I'm hoping that someone can help identify where I am going wrong.

    I have a SQL Server (SQL01) which runs under the domain account mydomain\SQLUser

    This SQL Server has a linked server (called MyLinkedServer) to SQL Server02 (SQL02) which has Analysis Services installed. Analysis Services runs under the account mydomain\SQLAnalsyisUser

    When I login to SQL01 as a domain admin account and connect to SQL01 using Enterprise Manager and run a SELECT * FROM LinkedServer this is successful.

    When I login to SQL02 as a domain admin account and connect to SQL01 using Enterprise Manager and run the same query I get the following errors:

    OLE DB provider "MSOLAP" for linked server "MyLinkedServer" returned message "An error was encountered in the transport layer.".

    OLE DB provider "MSOLAP" for linked server "MyLinkedServer" returned message "The peer prematurely closed the connection.".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "MSOLAP" for linked server "MyLinkedServer".

    I've searched Google which suggests that I create SPNs manually which I have done (as a domain admin), currently my account have the following SPNs (achieved using SETSPN -L accountname):

    mydomain\SQLUser:

    mssqlsvc/SQL01.mydomain.domain

    mssqlsvc/SQL01

    mydomain\SQLAnalsyisUser:

    MSSQLSvc/SQL01.mydomain.domain:1433

    mssqlsvc/SQL02.mydomain.domain

    mssqlsvc/SQL02

    msolapsvc.3/SQL02

    The accounts in AD are also set to 'Trust this user for delegation to any service (Kerberos only)' for both SQLUser and SQLAnalsyisUser. Also 'Account is sensitive and cannot be delegated' is not ticked.

    As the SQL02 box which SQLAnalysusUser connects to has Analysis Services installed I think I've got a few to many SPN's in there, but I wouldn't have thought having to many would cause it not to work.

    Can anyone please suggest anything I've got wrong or areas I can look into?

    Many thanks,

    Graham

  • Have you tried the solutions proposed here: http://ssas-olap.blogspot.com/2011/07/linked-server-for-analysis-services.html

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • FANTASTIC!

    I _really_ can't thank you enough.

    I went for the proxy account route which seems to work for me, no more faffing with SPNs!

    Thank you!

    Graham

  • You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I'm still having some weird issues...

    When I login to the server SQL01 as the user the linked server is using the query will not run from another machine, but when the server is at the login prompt it will run.

    The error message has changed to:

    OLE DB provider "MSOLAP" for linked server "MyLinkedServer" returned the message "The following system error occurred: ".

    Msg 7373, Level 16, State 2, Line 1

    Cannot set the initialization properties for OLE DB provider "MSOLAP" for linked server "MyLinkedServer".

    Any idea why it would not work when the user was logged in?

    Thanks,

    Graham

  • I'd be guessing.

    First two thoughts are permissions on one end or the other, or some connection option that isn't set correctly.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • I have no idea why, but when the SQL Server is logged in as the same user that another machine is logged in as (and connecting to the cube) the query fails.

    Thankfully we are able to state that the SQL server must not be logged in as the user but I', still interested to find out why this happens (and how to resolve it).

    Graham

Viewing 7 posts - 1 through 6 (of 6 total)

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