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

A brief explanation and solution for the Double Hop problem Expand / Collapse
Author
Message
Posted Friday, December 9, 2011 4:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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!

Post #1219732
Posted Friday, December 9, 2011 4:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 1,211, Visits: 6,541
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.
Post #1219734
Posted Friday, December 9, 2011 4:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 1,211, Visits: 6,541
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.
Post #1219736
Posted Friday, December 9, 2011 4:39 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 1,211, Visits: 6,541
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?
Post #1219738
Posted Friday, December 9, 2011 4:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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.
Post #1219741
Posted Friday, December 9, 2011 4:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:10 AM
Points: 1,211, Visits: 6,541
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.
Post #1219744
Posted Saturday, December 10, 2011 7:08 PM


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: Today @ 11:01 AM
Points: 3,461, Visits: 1,785
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 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 #1219877
Posted Sunday, December 11, 2011 11:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 7,001, Visits: 8,439
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.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1219944
Posted Tuesday, December 13, 2011 12:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:40 AM
Points: 7,001, Visits: 8,439
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1220617
Posted Wednesday, December 28, 2011 11:41 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 28, Visits: 449
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)
Post #1227650
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse