December 9, 2011 at 4:59 pm
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.
December 10, 2011 at 7:08 pm
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]
December 11, 2011 at 11:32 am
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
Learn to play, play to learn !
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[/url]
- 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
December 13, 2011 at 12:00 am
Just today I've seen a tweet pointing to a delegation troubleshooting blog:
Johan
Learn to play, play to learn !
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[/url]
- 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
December 28, 2011 at 11:41 pm
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)
December 29, 2011 at 6:17 am
My problem as a consultant is finding an AD admin that even knows what an SPN is, much less understanding how they work with applications like MSCRM, Share Point and Business Portal.
Sometimes I do not see an SPN for the default instance but there are SPNs for subsequent instances. It's the result of using accounts for the services that do not have the right to create the attributes in the LDAP database.
December 29, 2011 at 8:03 am
ALZDBA (12/13/2011)
Just today I've seen a tweet pointing to a delegation troubleshooting blog:
Unless I'm missing something it looks more like an advertisement for something called DelegConfig. I don't now anything about the tool or its usefulness though.
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]
December 29, 2011 at 9:20 am
osgcurt (12/29/2011)
My problem as a consultant is finding an AD admin that even knows what an SPN is, much less understanding how they work with applications like MSCRM, Share Point and Business Portal.Sometimes I do not see an SPN for the default instance but there are SPNs for subsequent instances. It's the result of using accounts for the services that do not have the right to create the attributes in the LDAP database.
I would expect, as a consultant, you would be able to 'guide' any domain administrator with setting this up.
That 'problem' might be part of why someone might use a consultant.
Don't complain too loudly about this.:-D
Very few accounts have the ability to write information to AD.
And running a service under an account that can do this might open up some security holes.
Not a best practice.
NT Authority System comes to mind, along with SQL Injection.
December 29, 2011 at 9:22 am
Kenneth.Fisher (12/29/2011)
ALZDBA (12/13/2011)
Just today I've seen a tweet pointing to a delegation troubleshooting blog:Unless I'm missing something it looks more like an advertisement for something called DelegConfig. I don't now anything about the tool or its usefulness though.
Maybe you should take a closer look. I think you are missng something.
December 29, 2011 at 10:17 am
Yes,I've seen that enough as well. I see both sides of the security mirror. Often I've been in an IT department with very experienced folks and DBAs and they tell me they have never heard of delegation issues. I blame the "VENDOR" for not educating the professionals.
Some of the deployment guides are very general and scanty. A friend of mine, Mark gives a great talk on Windows Logins which is way past the "logging into the machine" stage.
He is way into the packet area of the subject.
A few years back, we had many double hop Kerberos issues on SQL based application servers.
His talks really helped me understand the issues.
Seems I learn something new everyday from some one here or some place else.
December 29, 2011 at 6:00 pm
In my opinion one of the largest problems DBAs have with server level security (Kerberos, Service Account permissions etc) is that it is "Server Level", meaning it's outside of SQL Server. Most DBAs I've met over the years have no interest in learning anything that isn't directly part of SQL. If it can't be updated using the SQL Server toolset then they don't want to mess with it. That is something for the "server guy/team".
Then on the other side, the server support people generally have no interest in learning (and probably with better justification) what the SQL Servers/Service Accounts need by way of security.
Because of that you only have the rare DBA who started as a server admin, or who becomes a server admin that will actually understand both sides of the puzzle. This of course means that what documentation there is, is directed to the server admins, and is much harder to understand for us poor DBAs.
When I went to the Pass Summit there was a session I had looked forward to seeing called "Windows Operating Systems Internals for Database Pros" by Brian Kelly. Unfortunately it was canceled. One of these days when I have some free time I'll have to see if he has it on line somewhere.
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]
December 29, 2011 at 8:05 pm
I have a very intelligent friend who is a Server/systems/directory MVP.
At the MVP summit he makes a comment to me that MSSQL is just like a spread sheet, to me and one of the SQL MVPs.
We looked at him with disbelief. No WAY. My happenstance came from doing system and database work starting in the DBASE III Plus days and JCL/IBM QUERY on AS/400s. Getting the data processed and delivered seems like a logical pair like cooking the meal and serving it.
So I have often wondered why it was separated? We have better tools then ever to do it. But I guess I'm just an odd one out.
March 24, 2017 at 8:53 pm
This was removed by the editor as SPAM
March 24, 2017 at 8:58 pm
This was removed by the editor as SPAM
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply