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 was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]