debugsqlMinor
SQL Server 2008R2 Alias not working
Viewed 0 times
sqlworking2008r2servernotalias
Problem
I have an interesting situation here. We're basically trying to migrate some SQL Server infrastructure to new hardware, and one of the boxes (single-instance, 2-node cluster) is running 2008R2. The others are 2012 & 2014, but those don't exhibit this problem.
There's an app that connects to a server named, say, "OLD-SQL"; and let's say that IP is 11.22.33.44. This is the name of the legacy SQL box running the default instance, SQL 2008R2, and Windows Server 2008R2. The app's connection setting/config/string/whatever cannot be changed at this time.
The new SQL box that is set to replace that one, is named, say, "NEW-SQL"; and let's say it's IP is 11.22.33.55. Also running SQL 2008R2 (same SQL build). OS is Windows Server 2012 R2 (newer OS). Both boxes are actually Clustered Instances w/ 2 nodes each (old-fashioned failover clustering, nothing fancy).
So to aid the migration, for now, for testing/QA purposes, we've done the following:
To test it out, I try connect via SSMS; I enter "OLD-SQL" as the server-name to connect to. It fails with the infamous "SSPI context" error (https://support.microsoft.com/en-us/kb/811889). The same thing happens with the application that's being tested. Ping from cmd-line resolves fine -- it knows "OLD-SQL" resolves to new IP 11.22.33.55 based on the Hosts file.
Now, to really throw a wrench into things. I go back onto the server NEW-SQL, and add another Alias, same parameters but named "OLD-SQL2". This name is unique within the domain network. I go back to my box, change my Hosts file to point from that name to the IP (11.22.33.55), and go to SSMS and try to connect again. THIS WORKS!
I verify that I'm on the "right server" by doing a
There's an app that connects to a server named, say, "OLD-SQL"; and let's say that IP is 11.22.33.44. This is the name of the legacy SQL box running the default instance, SQL 2008R2, and Windows Server 2008R2. The app's connection setting/config/string/whatever cannot be changed at this time.
The new SQL box that is set to replace that one, is named, say, "NEW-SQL"; and let's say it's IP is 11.22.33.55. Also running SQL 2008R2 (same SQL build). OS is Windows Server 2012 R2 (newer OS). Both boxes are actually Clustered Instances w/ 2 nodes each (old-fashioned failover clustering, nothing fancy).
So to aid the migration, for now, for testing/QA purposes, we've done the following:
- Set up Hosts file on the client QA'ing machine to redirect the name "OLD-SQL" to 11.22.33.55 (new server).
- Created a SQL Server Alias on the NEW-SQL server (using SQL Config Mgr.), named "OLD-SQL", pointing at itself, port 1433, protocol TCP/IP.
To test it out, I try connect via SSMS; I enter "OLD-SQL" as the server-name to connect to. It fails with the infamous "SSPI context" error (https://support.microsoft.com/en-us/kb/811889). The same thing happens with the application that's being tested. Ping from cmd-line resolves fine -- it knows "OLD-SQL" resolves to new IP 11.22.33.55 based on the Hosts file.
Now, to really throw a wrench into things. I go back onto the server NEW-SQL, and add another Alias, same parameters but named "OLD-SQL2". This name is unique within the domain network. I go back to my box, change my Hosts file to point from that name to the IP (11.22.33.55), and go to SSMS and try to connect again. THIS WORKS!
I verify that I'm on the "right server" by doing a
SELECT @@SERVERNAME, and lo-and-beSolution
Summarizing what we discovered in the chat.
When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.
In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM. Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.
References
Understanding Kerberos Keys
Registering an SPN
When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.
In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM. Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.
References
Understanding Kerberos Keys
Registering an SPN
Context
StackExchange Database Administrators Q#132313, answer score: 6
Revisions (0)
No revisions yet.