December 9, 2011 at 9:51 am
While everyone is discussing this I'm going to throw out a problem I had while working on this that I never really understood. I had a server where when I configured it to a static port, I forgot to remove the entry under Dynamic Port in configuration manager. The two port numbers of course were different.
The SPN created was under the static port number, and everything I looked at showed SQL running under that port number. However about half of my other servers wouldn't "hop" to this server until I removed the dynamic port entry and restarted the instance. The other half of my servers worked just fine.
Does anyone have any idea why?
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 9, 2011 at 10:08 am
Nice. Thanks Kenneth! I've had the issue before and worked around it differently (with poorer solutions), but this seems like a great solution. Next time I have the problem, I'm coming right back here to follow your steps. Thanks!
December 9, 2011 at 11:21 am
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
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 9, 2011 at 11:42 am
Recently came across a bug when you need to do a double-hop over different nodes in the same Windows 2008 cluster.
If you have two instances of SQL on the same Windows 2008 cluster, and do something that requires double-hop authentication (in my case SQL Agent initiated an SSIS package from instance A that initiates a bulk insert on Instance B of a file on Server X) there is a bug where Windows will initiate an NTLM authentication even though both instances are on different nodes of the cluster. This happens because Windows does not properly release the DNS entries when it performs a fail over, therefore Kerberos fails initially so it fails over to NTLM authentication.
So when the instances are running on the same node, it works great (no double-hop). But when they are on different nodes, it requires double-hop, and therefore fails with an access denied on the bulk-insert statement. The access denied becomes a red-herring that it is a file permissions issue, and it is not.
Also, it is not "something you probably configured wrong" as one person in my organization tried to insinuate. It is a known issue with MS and addressed in KB 982549.
I hope I just saved somebody several days and half a head of hair.
December 9, 2011 at 11:56 am
Jeff Moden (12/9/2011)
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>
Yes.
Also, see my post above where I address a known issue with MS when the double-hop occurs over same nodes in a windows cluster. Because windows wasn't releasing DNS settings properly after a fail over, kerberos authentication doesn't happen, and it uses NTLM. This works fine for most operations until it performs something like the bulk-insert and then fails with access-denied.
What is frustrating is that the double-hop actually would work if I performed the same type of operation (in SSMS I performed a bulk insert to replicate the failure) from another server on the network, or on the same node. Except it failed when I was on the opposite node of the cluster. I then saw that when connected from non-clustered machine, the session was kerberos. However on the other node in the cluster it was NTLM (when it should have been kerberos).
December 9, 2011 at 12:14 pm
Wow, lots of misleading information in this article. This is the wrong way to go about setting up kerberos. (I'd equate it to recommending switching to simple recovery mode to shrink the log file. It's bad advice!)
1. Make sure that the SQL Server instances, SQLServerA and SQLServerB, each have their own service accounts; say SVCSQLServerA and SVCSQLServerB. Each instance should have its own service account.
2. You must grant Read and Write ServicePrincipalName1 for both instances. This is what allows SQL Server to create & remove SPNs.
3.Assign the service accounts as the startup accounts for the SQL Server instances. SVCSQLServerA for SQLServerA and SVCSQLServerB for SQLServerB.
4. Restart the SQL Server Instances. This causes a SPN to be created (Service Principal Name) for each instance.
5. Once this is done a "Delegation" tab will be visible in AD for each of the service accounts. Grant both of your service accounts "Trust this user for delegation to any service (Kerberos only)". 1
6. Now if your security people balk at the "any service" part, like mine did, they can grant the trust just to the other service account.
i.e. For SVCSQLServerA grant "Trust this user for delegation to specified services only"/"Use Kerberos only"/SVCSQLServerB and vice versa. 2
1. -- Not a requirement for kerberos. It could be considered a best practice, but it's not a requirement
2. -- NO! Just have a domain admin create the SPN. If you're worried about dynamic ports changing, then assign it a static port.
3. -- ok.
4. -- manual creation of SPN's is a better option.
5. -- SVCSQLServerB isn't delegating credentials, so it doesn't need to be trusted for delegation. only SVCSQLServerA needs this set
6. -- "Trust this user for delegation to specified services only" (constrained delegation) is more secure than "any service" (unconstrained delegation). Kerberos delegation will not let you delegate from constrained to unconstrained (weakening security).
1. To connect to a service using Kerberos, you need a ticket from the ticket granting service (Domain Controller). The ticket is encrypted with your user account private key and the target service account's public key (so, only the target service account's private key can decrypt it, and it can verify your identity by decrypting using your public key.)
1a) the DC needs to know which user account is going to decrypt the ticket. Your ticket request says 'I want to connect "MSSQLSvc" on machine "SQLServerA" ' This is what the SPN is used for. No SPN, No ticket. Wrong account in the SPN, and the target account can't decrypt the ticket (KRB_ERR_MODIFIED)
1b) Kerberos only works with A records in DNS, not CNAME records for SPN's.
1c) the NetworkService account is the computer account (mydomain\SQLServerA$) So if you're running as network service, register the SPN under the computer account and Kerberos will work.
2. If the Service needs to pass your credentials on to another service (Delegate), then the service account that's delegating needs to be trusted for delegation.
2a) Unconstrained -> constrained (tightening security) is allowed. Constrained -> Unconstrained (loosening) is not allowed
2b) Sensitive accounts can be flagged to not allow delegation.
Example:
User (internet explorer)
-> Sharepoint (HTTP on server WEB1 running as SVCSharepoint)
-> SSRS (HTTP on server RS1 running as SVCReports)
-> SQL1 (MSSQLSvc on server SQL1 running as SVCSqlServer1)
-> (linked server) SQL2\INSTANCENAME (MSSQLSvc on server SQL2 running as SVCSqlServer2)
WEB1,RS1,SQL1 and SQL2 all need "A" records in DNS
SVCSharepoint, SVCReports and SVCSqlServer1 need to be "trusted for delegation"
spn's should be created with/without FQDN's including ports (or instance names):
setspn.exe -U -A http/WEB1.myDomain.local myDomain\SVCSharepoint
setspn.exe -U -A http/RS1.myDomain.local myDomain\SVCReports
setspn.exe -U -A MSSQLSvc/SQL1.myDomain.local:1433 myDomain\SVCSqlServer1
setspn.exe -U -A MSSQLSvc/SQL1:1433 myDomain\SVCSqlServer1
setspn.exe -U -A MSSQLSvc/SQL2.myDomain.local:INSTANCENAME myDomain\SVCSqlServer2
setspn.exe -U -A MSSQLSvc/SQL2.myDomain.local:56542 myDomain\SVCSqlServer2
setspn.exe -U -A MSSQLSvc/SQL2:INSTANCENAME myDomain\SVCSqlServer2
setspn.exe -U -A MSSQLSvc/SQL2:56542 myDomain\SVCSqlServer2
No duplicate SPN's (two different accounts registered for the same SERVICE\HOST combo) It is ok for the same account to be used in different places.
To query for duplicates:
setspn.exe -X
No additional config is required for SqlServer. For other services (SSRS, IIS, Sharepoint, WCF services) you have to explicitly enable kerberos authentication. check msdn for instructions.
December 9, 2011 at 1:05 pm
I recently requested "account is sensitive and cannot be delegated" for my SQL service accounts. Did I shoot myself in the foot and unintentionally break account delegation for the accounts?
December 9, 2011 at 1:17 pm
You haven't broken it for the average user.
If you logged into windows as the sql service account, then double hops won't work.
December 9, 2011 at 1:29 pm
This caught my eye, right away. I've worked in the Microsoft Dynamics world, and mostly with MSCRM.
We have been dealing with these issues from years now and some scenarios are very difficult.
One instance was a Win 2003 full forest level AD that handled Kerberos just fine. But a Windows 2000 forest in Europe has a forest trust to the Win2003 forest. Sadly, there are no Kerberos packets allowed with a Windows 2000 trust. Thus , SSRS reports could not be rendered from that Forest. SPNs could not be resolved from any clients on that side of the trust.
The story is dramatized here:
http://www.windowsitpro.com/article/reporting-services/twelve-angry-techs
December 9, 2011 at 3:00 pm
SpringTownDBA (12/9/2011)
Wow, lots of misleading information in this article. This is the wrong way to go about setting up kerberos. (I'd equate it to recommending switching to simple recovery mode to shrink the log file. It's bad advice!)
I'm sorry you feel that way. This was primarily aimed at those people who are having a hard time with this problem and would rather have a kerberos solution than doing something else. Switching to SQL logins for example.
1. -- Not a requirement for kerberos. It could be considered a best practice, but it's not a requirement
Agreed. As stated earlier in the discussion this is a best practice not a requirement. I've never tried it but at least one person has stated they have and it works.
2. -- NO! Just have a domain admin create the SPN. If you're worried about dynamic ports changing, then assign it a static port.
4. -- manual creation of SPN's is a better option.
Why do you feel manual is better than automatic? To use a similar analogy to what you have above, it would be like manually creating and executing backup scripts rather than using a maintenance plan and a job. Do you have a specific reason for this or is it just an opinion? If I remember correctly the method I gave is the one recommended by Microsoft. I'm always willing to be proven wrong though, and certainly enjoy learning more.
5. -- SVCSQLServerB isn't delegating credentials, so it doesn't need to be trusted for delegation. only SVCSQLServerA needs this set
If you want to go in both directions (which was my example) then you have to set up both trusts.
6. -- "Trust this user for delegation to specified services only" (constrained delegation) is more secure than "any service" (unconstrained delegation). Kerberos delegation will not let you delegate from constrained to unconstrained (weakening security).
Agreed. As I stated in my article I had to use "Trust this user for delegation to specified services only" and it is certainly more secure. I included both options since as I recall trusting all is the method that Microsoft recommended.
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 9, 2011 at 4:10 pm
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!
December 9, 2011 at 4:29 pm
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.
December 9, 2011 at 4:36 pm
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.
December 9, 2011 at 4:39 pm
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?
December 9, 2011 at 4:48 pm
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.
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply