Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A brief explanation and solution for the Double Hop problem


A brief explanation and solution for the Double Hop problem

Author
Message
Kenneth.Fisher
Kenneth.Fisher
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3592 Visits: 2021
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
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8261
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.
stevehindmarsh
stevehindmarsh
SSC Eights!
SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)SSC Eights! (900 reputation)

Group: General Forum Members
Points: 900 Visits: 584
Apologies my mistake, I mis-read the article to imply that SQL always got a new port with dynamic allocation on restart.



Jason Crider
Jason Crider
SSC Veteran
SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)SSC Veteran (285 reputation)

Group: General Forum Members
Points: 285 Visits: 2229
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
Russell Fields-270604
Russell Fields-270604
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 591
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45116 Visits: 39917
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Craig A. Silvis
Craig A. Silvis
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 170
Very timely since this topic came up just this week - thanks!
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4621 Visits: 9556
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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
@SixStringSQL
@SixStringSQL
SSC Journeyman
SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)SSC Journeyman (91 reputation)

Group: General Forum Members
Points: 91 Visits: 371
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
dmbaker
dmbaker
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 3439
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search