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
SpringTownDBA
SpringTownDBA
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: 1540 Visits: 1499
Hey Kenneth,

Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.

As for automatic spn registration, there is a recommendation against using automatic registration on a cluster:
http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx (which I just found about 15 minutes ago, the rationale is in the comments)

My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).

As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)

If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well.

I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it.

thanks!
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 8599
Jason Crider (12/9/2011)
Greg, I assume are talking about this whitepaper for Sharepoint 2010?

Haven't heard of the KerbBuddy tool.


Yep - that's the one. 241 pages.:-)
And still missing a bit.

I think I found KerbBuddy on Codeplex.
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 8599
bruce.l.pettus (12/9/2011)
I'm in the process of implementing a solution so I'm not entirely confident I'm correct; however, I wanted to point out an issue with the SPN comment.

To my knowledge, the ability to write an SPN to the AD requires domain admin permissions and I'm fairly sure the AD admins will have a problem with this. My solution (still in progress) is to...

1. Specify a static IP port for the SQL instance(s).
2. Request creation of SPNs for servers involved in the double-hop connection.
3. Request the SQL service accounts be granted "trust for delegation" permissions.

If you change the IP port of the SQL instance you'll need to request the SPN be updated to match the new IP port.


Any domain admin should recognize kerberos as a much more secure method than allow anonymous.
And far more secure than using basic on a web site too.
The service accounts and the machines need to be allowed to delegate.
And there is also contrained delegation, which is much more specific.
I have never had any push back in my company on using Kerberos.
And the domain admins were thankful for my help in setting up and troubleshooting.
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 8599
ALZDBA (12/9/2011)
Nice article. And what's most important .... It will help people diagnose and solve this kind of issues !

With fixed ports, you can also register the spn yourself ( after install of the instance ) using this
DOS script

echo off
rem http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
rem To create an SPN for SQL Server, enter the following code at a command prompt:
rem setspn -A MSSQLSvc/Host:port serviceaccount
rem For example:
rem setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount
echo on


rem C:\Program Files\Support Tools\setspn -A MSSQLSvc/yourservername.yourdomain.yourdomainsuffix:2388 DOMAIN\SERVICEACCOUNT


So you are a domain admin?
SpringTownDBA
SpringTownDBA
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: 1540 Visits: 1499
Greg Edwards-268690 (12/9/2011)
bruce.l.pettus (12/9/2011)
I'm in the process of implementing a solution so I'm not entirely confident I'm correct; however, I wanted to point out an issue with the SPN comment.

To my knowledge, the ability to write an SPN to the AD requires domain admin permissions and I'm fairly sure the AD admins will have a problem with this. My solution (still in progress) is to...

1. Specify a static IP port for the SQL instance(s).
2. Request creation of SPNs for servers involved in the double-hop connection.
3. Request the SQL service accounts be granted "trust for delegation" permissions.

If you change the IP port of the SQL instance you'll need to request the SPN be updated to match the new IP port.


Any domain admin should recognize kerberos as a much more secure method than allow anonymous.
And far more secure than using basic on a web site too.
The service accounts and the machines need to be allowed to delegate.
And there is also contrained delegation, which is much more specific.
I have never had any push back in my company on using Kerberos.
And the domain admins were thankful for my help in setting up and troubleshooting.


If the service is running under a domain account, the machine account plays no role in kerberos authentication.
Greg Edwards-268690
Greg Edwards-268690
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4366 Visits: 8599
SpringTownDBA (12/9/2011)
Hey Kenneth,

Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.

As for automatic spn registration, there is a recommendation against using automatic registration on a cluster:
http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx (which I just found about 15 minutes ago, the rationale is in the comments)

My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).

As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)

If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well.

I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it.

thanks!



I prefer ADSI Edit to enter SPN's to SETSPN.exe.
Learning to setup Kerberos properly and troubleshoot issues is well worth learning.
It allows user level security based on the users credentials.
We also use SSAS, and it is pretty much a requirement to have this workng properly to expose the data.
We also leverage AD Groups, which also can have advantages.
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6445 Visits: 2062
SpringTownDBA (12/9/2011)
Hey Kenneth,

Thanks for your reply. I apologize if my original post seemed harsh, I've fought some extremely frustrating battles with kerberos in the past and want others to avoid my pain and anguish.

Absolutely. I spent a year and a half figuring out my solution, and that was with the help of someone who had already managed it. The whole purpose of this was to give other DBAs a cookbook abroach to fixing the problem 90% of the time. And that is with the realization that a lot of DBAs are going to look at an in depth explanation of Kerberos and their eyes are going to glaze over right before the move on to the next article. I know I did several times.

As for automatic spn registration, there is a recommendation against using automatic registration on a cluster:
http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx (which I just found about 15 minutes ago, the rationale is in the comments)

I'll have to read this when I have some time. Clusters do tend to be an exception to the rule frequently.

My original thoughts on manual being better are due to consistency with other Microsoft products that don't auto-register themselves (SSRS, SSAS, Sharepoint 2010 components, etc), and explicit registration forcing DBA's and Sys Admins to become more familiar with kerberos configuration. Unfortunately, the easy way to get spn's registered is to run sql as a domain admin account. The second easiest is to ask a system administrator to run "setspn.exe ...". The third is to grant the specific permissions to the service account (as you mentioned).

My office doesn't do much with SSRS or SSAS and I haven't gotten to play with our Sharepoint yet so I can't talk about those myself. Using the domain admin account is definably the easiest method. Of course so would eliminating all security. And believe me I've wished I could several times.;-) Asking the system administrator to create the SPN is only easy if they are willing to construct the command or if you know enough to construct it yourself. I'm getting closer on that myself, but I'm not still not confident in it. For me the easiest method was to ask for a specific permission. Of course I'm very glad that several people have mentioned the method for creating the SETSPN.exe command since I think that everyone should be able to make their own choice.

As for constrained vs unconstrained,the current guidance from MS for Sharepoint configuration strongly pushes constrained delegation. Because constrained -> unconstrained doesn't work (and is very hard to trace as the culprit), I would only recommend configuring constrained delegation on your sql service accounts. I believe that the guidance from MS recommending unconstrained delegation for SqlServer hasn't been revisited in several years (could be wrong though)

Wouldn't surprise me in the slightest if they needed to update their documentation. I agree that constrained delegation is best. Of course at the time it threw me a bit, so I decided I should put in both options.

If you wrote a follow-up article on configuring kerberos for SSRS and/or SSAS, that might be helpful as well.

If I ever have the time to play with it with SSRS and SSAS I'll give it a shot :-)

I hate seeing people avoid kerberos because it's too hard to get configured or they don't know how to troubleshoot it.


Absolutely! And please understand I appreciate every response people have made to this article. I love responses that continue to help people further understand the problem and it's solutions, all of the solutions, not just the one I wrote about.

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
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30611 Visits: 8986
Greg Edwards-268690 (12/9/2011)
ALZDBA (12/9/2011)
Nice article. And what's most important .... It will help people diagnose and solve this kind of issues !

With fixed ports, you can also register the spn yourself ( after install of the instance ) using this
DOS script

echo off
rem http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
rem To create an SPN for SQL Server, enter the following code at a command prompt:
rem setspn -A MSSQLSvc/Host:port serviceaccount
rem For example:
rem setspn -A MSSQLSvc/server1.redmond.microsoft.com sqlaccount
echo on


rem C:\Program Files\Support Tools\setspn -A MSSQLSvc/yourservername.yourdomain.yourdomainsuffix:2388 DOMAIN\SERVICEACCOUNT


So you are a domain admin?


For the required permissions, have a look at :http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx

more details for domain admins to delegate this to a sqlserver sysadmin can be found at http://technet.microsoft.com/en-us/library/cc773257(WS.10).aspx#BKMK_Delegating

Being domain admin would make this easy, but chances are I would break more things by simply not knowing what AD or a domain holds, needs and how simple it is to mess it up, so ... no thanks.Hehe

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30611 Visits: 8986
Just today I've seen a tweet pointing to a delegation troubleshooting blog:

http://blogs.msdn.com/b/sqlserverfaq/archive/2011/12/12/troubleshooting-kerberos-delegation-using-delegconfig.aspx

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Theo Ekelmans
Theo Ekelmans
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1082 Visits: 802
Hi Kenneth,

Thanks for the in depth explanation. I was just about to setup an "poller" of sorts on a central SQL server that would have exactly this problem.

Theo (NL)
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