January 5, 2011 at 1:35 pm
In an effort to resolve the double hop hop issue on our servers I did the following: (and this is a fairly easy solution for anyone that needs one on 2005 and up servers only)
1) Create domain service accounts for each instance.
A good idea for security purposes anyway.
2) Grant these domain accounts Read/Write SPN (Service Principal Name)
This allows SQL to create/remove SPNs when it is started/stopped.
This must be done by a domain admin.
3) Assign the service accounts to the instance and restart.
Creates the SPNs.
4) Under the delegation tab (only shows up if a SPN has been created) of the service accounts set the Trust this user for delegation to specified services only, Use Kerberos only, then add the service accounts of any instance you want to hop to. Note: Even though you add the service account name it adds the SPN. Because of this your instances have to use a fixed port since the delegation will not change when your instances port # does and will no longer work.
You can use the "Trust this user for delegation to any service (Kerberos only)" setting if your security team will let you. It makes life a lot easier.
5) Wait for everything to propagate through your network (20-30 minutes on mine) and your double hop works.
Ok, now everything here worked great on most of my instances as I said above. I can go from DevServer1 to DevServer2-10, TestServer1-10, even the prod servers I have set up etc. (Yes we really have that many instances even though they aren't actually name that way.)
Where I run into a problem is connecting to DevServers 1 & 2. From SOME servers I can't connect to them. They can get out to any other server, but even though I've followed all of the above steps in some cases I can't get in. Now they are both instances on the same virtual box, both are running SQL 2005 SP2, and the servers that won't connect to one of them will usually connect to the other and vise versa.
I've restarted the server with the 2 instances and several of my other servers and have had no luck.
Anyone have any suggestions?
Thanks
Kenneth
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]
January 5, 2011 at 2:40 pm
Honestly, as complex as your solution and environment sound, I doubt anyone can give specific suggestions on this.
I generally solve authentication double-hop issues by using SQL Server logins instead of AD accounts. Makes the whole thing really, really simple to set up and administer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 5, 2011 at 3:31 pm
We are trying to move away from SQL Authentication for security and maintenance issues. Specifically with 1000+ employees the 5 SQL DBAs don't want to have to manage creating & removing permissions for each employee. We try to grant permission to Active Directory groups and let security manage who is in each group.
Unfortunately the whole double-hop/kerberos problem is such a complicated cross discipline problem that there aren't many people who can get it working at all let alone fix problems. I was just hoping to get lucky 🙂
Kenneth
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]
January 6, 2011 at 8:04 am
Domain access for the direct access, and SQL accounts used only for linked servers, gives a pretty good security model that's easy to administer. You can audit what you need, control what you need, and administration is actually simpler.
There's an option in linked servers that you can provide specific credentials for the link. If you do that, you just need one SQL account, with limitations on what that account can do that are reasonable for your security needs. If you need multiple security contexts, for each department for example, you set up one account per department, and each one uses a different linked server name, even if the links point at the same target. That makes it slightly harder to program against, because you'll need schemas and procs that use different 4-part names, but it's still less complex than 1000+ accounts, and generally less complex than kerberos issues.
Keep in mind, all it takes is a hardware failure on one of your servers, and you might end up having to rebuild a significant portion of your kerberos solution. If you use SQL accounts for linked servers, and AD for everything else, your recovery plans become much simpler, easier to document, and faster to implement.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2011 at 3:23 pm
On the involved servers, check that you are connected using Kerberos by selecting from sys.dm_exec_connections where session_id=@@SPID. Also check for any error messages in the error log.
Are you connecting from server1 to server2 by using a linked server?
Are the two instances run by the same account, or are there different accounts? Are they set up to use dynamic ports or static. Is SQL Browser running? What is the output of setspn -L <service account name>?
January 10, 2011 at 1:36 pm
On the involved servers, check that you are connected using Kerberos by selecting from sys.dm_exec_connections where session_id=@@SPID. Also check for any error messages in the error log.
All of them show Kerberos, no errors except login errors
Are you connecting from server1 to server2 by using a linked server?
Yes
Are the two instances run by the same account, or are there different accounts? Are they set up to use dynamic ports or static.
1 account per instance, all instances are on a static port
Is SQL Browser running?
yes
What is the output of setspn -L <service account name>?
Here are 2 examples
Registered ServicePrincipalNames for CN=SVCDEVSERVER1,OU=ServiceAccounts,OU
=PLN,OU=DMS,DC=us,DC=company,DC=com:
MSSQLSvc/DEVSERVER1.us.company.com:56785
Registered ServicePrincipalNames for CN=SVCTESTSERVER1,OU=ServiceAccounts,OU
=PLN,OU=DMS,DC=us,DC=company,DC=com:
MSSQLSvc/TESTSERVER1.us.company.com:51265
Again I can go from DEVSERVER1 to TESTSERVER1 but not the other way round. The delegation trusts are all in place as best I can tell.
Thanks for your help
Kenneth
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]
January 10, 2011 at 1:45 pm
Then I'm out of ideas.
If you are sure that delegation is set up correctly and that the linked servers are set up identically then I'm afraid I don't know.
The only thing I can suggest is to use a network monitor program to debug the kerberos messages going back and forth between the two instances involved. I've never done it myself, so I can't tell you how to do it, but if I remember correctly there is a good article written about this subject by SQLCAT.
January 10, 2011 at 3:11 pm
Thanks for the attempt. I'll be patching and rebooting the dev server this weekend so we will see if that helps. I had a SPN removed from the server (left over from when it was using NTLM) and havn't rebooted since so you never know.
Thanks again
Kenneth
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]
April 12, 2011 at 9:54 am
In the very unlikely event that someone else has the same problem I thought I would post my solution.
Turns out when I changed my server from dynamic ports to static ports I forgot to take the entry out of the dynamic ports field. While the server was starting up under the correct port SOME servers, and don't ask me why only some, could not find it under that port. So the solution was, remove the dynamic port entry (that shouldn't have been there anyway) and everything now works fine.
Thanks for your help GSquared,
Kenneth
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]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply