Double Hop problem!

Question

Double Hop problem!

GRussell31

SSCrazy

Points: 2116
More actions
August 8, 2012 at 6:38 am

#392913

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

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

GRussell31 SSCrazy Points: 2116 More actions · Answer 2

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

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

GRussell31 SSCrazy Points: 2116 More actions · Answer 4

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

GSquared SSC Guru Points: 260824 More actions · Answer 5

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

GRussell31 SSCrazy Points: 2116 More actions · Answer 6

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