December 8, 2011 at 10:03 pm
Comments posted to this topic are about the item A brief explanation and solution for the Double Hop problem
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 1:39 am
This was removed by the editor as SPAM
December 9, 2011 at 1:40 am
This was removed by the editor as SPAM
December 9, 2011 at 1:45 am
One comment - 'Dynamic Ports being the default for named instances'. The port is only truly dynamic for the installation of SQL Server - once it's installed, the port number is static and does not change.
So SPNs and Kerberos will work fine with named instances and dynamic ports. You just need to identify the correct port number after installation is complete.
December 9, 2011 at 2:55 am
Kenneth, an excellent effort at covering a challenging a topic. Good stuff!
One question I imagine other readers might have, "how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".
December 9, 2011 at 3:45 am
Kenneth, thanks for the article. couple of points: are you sure the SQL restart is necessary to get this working, and the error you will often see returned with the double hop scenario is 'cannot generate SSPI context'
Heres another good source on kerberos, linked servers and double hop
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx.
Has anyone got this working when the first hop is to SQL server and the second to AD itself (i.e. an ADSI linked server set up)
---------------------------------------------------------------------
December 9, 2011 at 3:55 am
Kenneth
Very good article. A succinct way to get this to go.
For those that want a bit more depth, Brian Kelly wrote a good article at http://www.sqlservercentral.com/articles/Security/65169/
HTH
Dave J
December 9, 2011 at 4:09 am
Thanks for this brief overview !
Franky
Franky L.
December 9, 2011 at 5:00 am
stevehindmarsh (12/9/2011)
One comment - 'Dynamic Ports being the default for named instances'. The port is only truly dynamic for the installation of SQL Server - once it's installed, the port number is static and does not change.So SPNs and Kerberos will work fine with named instances and dynamic ports. You just need to identify the correct port number after installation is complete.
Not true. What you are referring to is that SS will try to reuse the current dynamic port, it never becomes 'static'. If it's available it will use it again. If it has been grabbed by another application, it will renegotiate, FTP-style, a new port. It is especially true if one restarts the SS service, or takes the server off-line.
December 9, 2011 at 5:35 am
John.Sansom (12/9/2011)
Kenneth, an excellent effort at covering a challenging a topic. Good stuff!One question I imagine other readers might have, "how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".
While I have to admit I have never tried itmy understanding is that each instance of SQL Server must have a different service account for this to work.
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 5:42 am
george sibbald (12/9/2011)
Kenneth, thanks for the article. couple of points: are you sure the SQL restart is necessary to get this working, and the error you will often see returned with the double hop scenario is 'cannot generate SSPI context'Heres another good source on kerberos, linked servers and double hop
http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx.
Has anyone got this working when the first hop is to SQL server and the second to AD itself (i.e. an ADSI linked server set up)
The SQL restart is necessary in order for SQL to create the SPNs. Without those you won't be able to see the Delegation tab and create the "trust" between the two servers.
I've seen 'cannot generate SSPI context' before and I believe its a DNS problem. I get it every now and again even on servers that have been set up correctly. You will also frequently see it right after you have completed all of the steps but the information hasn't propagated around the network completely. On my network I have to wait about 15 minutes before testing.
Here is a good KB article on the subject though:
http://support.microsoft.com/kb/811889
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 5:47 am
Note that Domain Administrators are usually needed to create SPN's.
And servers and service accouts must be allowed to delegate.
Usually by default this is not the case.
There is a white paper for setting up Kerberos for Sharepoint 2010 which might be a good reference.
Although very long, it covers some new services (Claims to Windows), along with IIS and SSAS.
There are also some tools like KerbBuddy that prove useful too.
3 keys I like to set in the registries to help troubleshoot - are ones for logging, forcing kerberos to use TCPIP, and max packet size.
Good job giving a short overview. Many give up trying to set this up, and resort to workarounds.
December 9, 2011 at 5:57 am
Apologies my mistake, I mis-read the article to imply that SQL always got a new port with dynamic allocation on restart.
December 9, 2011 at 6:28 am
Greg, I assume are talking about this whitepaper for Sharepoint 2010?
Haven't heard of the KerbBuddy tool.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
December 9, 2011 at 6:31 am
One question I imagine other readers might have, "how would the implementation steps differ, if at all, were both ServerA and ServerB using the same SQL Server Service Account?".
They will not differ. Although our servers now run under different service accounts it was not always the case.
Russell Fields
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply