SQL Clone
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
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6337 Visits: 2059
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 FisherI 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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4224 Visits: 8593
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! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)SSC Eights! (936 reputation)

Group: General Forum Members
Points: 936 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
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1517 Visits: 2232
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
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 605
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 Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214946 Visits: 41979
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 252 Visits: 170
Very timely since this topic came up just this week - thanks!
Eric M Russell
Eric M Russell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29005 Visits: 11508
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-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 389
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
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1362 Visits: 3585
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