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 ««12345»»»

A brief explanation and solution for the Double Hop problem Expand / Collapse
Author
Message
Posted Friday, December 9, 2011 5:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:11 AM
Points: 3,464, Visits: 1,801
george sibbald (12/9/2011)
Kenneth, thanks for the article. couple of points: are you sure the SQL restart is necessary to get this working, and the error you will often see returned with the double hop scenario is 'cannot generate SSPI context'

Heres another good source on kerberos, linked servers and double hop

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx.

Has anyone got this working when the first hop is to SQL server and the second to AD itself (i.e. an ADSI linked server set up)


The SQL restart is necessary in order for SQL to create the SPNs. Without those you won't be able to see the Delegation tab and create the "trust" between the two servers.

I've seen 'cannot generate SSPI context' before and I believe its a DNS problem. I get it every now and again even on servers that have been set up correctly. You will also frequently see it right after you have completed all of the steps but the information hasn't propagated around the network completely. On my network I have to wait about 15 minutes before testing.
Here is a good KB article on the subject though:
http://support.microsoft.com/kb/811889


Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
Post #1219271
Posted Friday, December 9, 2011 5:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:18 PM
Points: 1,248, Visits: 6,694
Note that Domain Administrators are usually needed to create SPN's.
And servers and service accouts must be allowed to delegate.
Usually by default this is not the case.
There is a white paper for setting up Kerberos for Sharepoint 2010 which might be a good reference.
Although very long, it covers some new services (Claims to Windows), along with IIS and SSAS.
There are also some tools like KerbBuddy that prove useful too.
3 keys I like to set in the registries to help troubleshoot - are ones for logging, forcing kerberos to use TCPIP, and max packet size.
Good job giving a short overview. Many give up trying to set this up, and resort to workarounds.
Post #1219274
Posted Friday, December 9, 2011 5:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, May 9, 2014 8:31 AM
Points: 861, Visits: 544

Apologies my mistake, I mis-read the article to imply that SQL always got a new port with dynamic allocation on restart.



Post #1219280
Posted Friday, December 9, 2011 6:28 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 6:35 AM
Points: 282, Visits: 2,211
Greg, I assume are talking about this whitepaper for Sharepoint 2010?

Haven't heard of the KerbBuddy tool.


MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Post #1219303
Posted Friday, December 9, 2011 6:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:31 AM
Points: 50, Visits: 542
One question I imagine other readers might have, "how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".


They will not differ. Although our servers now run under different service accounts it was not always the case.

Russell Fields
Post #1219306
Posted Friday, December 9, 2011 6:41 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 37,102, Visits: 31,655
I'm the same way... really good at database stuff... really bad with Windows and Network Security. So let me ask the question... will this also solve the similar problem that occurs when using things like BULK INSERT even though I've created the appropriate "Share"?

<DESKTOP> - HOP - <SQL SERVER> - HOP - <Some Server On The Domain With a File On It To Be BULK INSERTED>


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1219323
Posted Friday, December 9, 2011 7:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, June 12, 2014 2:58 PM
Points: 92, Visits: 170
Very timely since this topic came up just this week - thanks!
Post #1219356
Posted Friday, December 9, 2011 7:05 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:39 PM
Points: 1,657, Visits: 4,740
Greg Edwards-268690 (12/9/2011)

...
Good job giving a short overview. Many give up trying to set this up, and resort to workarounds.

I'm a DBA on some development and QA servers but not in production, so I have to request permission before I develope a database or job that leverages a linked server. Considering the issues involved, it's no wonder why many DBAs punt and configure the linked server to use SQL Server account authentication, or they just disallow the use of linked servers all together. This article is succinct and could be very helpful.
Post #1219359
Posted Friday, December 9, 2011 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:14 AM
Points: 78, Visits: 306
I'll just add this info about Kerb, in case someone's looking...

Your two servers must have their system times set closely...Within two mins, I believe. If not, you will get the infamous "Cannot generate SSPI context" message.

-Grubb
Post #1219374
Posted Friday, December 9, 2011 7:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:49 AM
Points: 519, Visits: 2,817
I think it should be noted that the "hop" problem pops up not just between instances of SQL Server...it can happen anywhere that credentials need to be delegated between "things". For instance, it can happen with a web application with a SQL Server back-end.


Post #1219376
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse