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:
1. Set up Hosts file on the client QA'ing machine to redirect the name "OLD-SQL" to 11.22.33.55 (new server).
2. 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-behold, it says "NEW-SQL". But of course this isn't the alias I really want; I want to be able to give it the alias "OLD-SQL" and have my app redirected to "NEW-SQL" via the Hosts entry and the SQL Alias.
So what am I doing wrong with the first alias? Is it just that I can't use the same name as an existing server on the network, with 2008R2?
Similar post on SO: https://stackoverflow.com/questions/6406811/alias-not-working-on-sql-server-2008-r2
(Did not solve the issue)
PS: I say "with 2008R2" specifically, because when I try the same setup (Hosts, SQL Alias) with our 2012-2014 boxes, they work the first way just fine! (I.e. the Alias on the "NEW-SQL2012" box can be "OLD-SQL2012", which is the same as an existing server, and no problems connecting or anything.)
PPS: I did read about these aliases being more of a client-side thing, but that's why I'm using the Hosts file trick. When we get ready for the "real" migration, we'll use DNS and other tricks outside of my knowledge (that's the SysEng's domain) to do the redirection of clients (apps/computers) to the new servers, but they said for now, for testing, the Hosts file thing is a good substitute.
Update: The main difference between the "working" setups and the "non-working" one, besides the SQL Server versions, is the fact that the old 2008R2 instance "SQL-OLD", when I connect using the "pristine" setup (no aliases or hosts-file-redir.), uses Kerberos authentication. When I connect via a unique alias or redirect, such as "OLD-SQL2", it registers as NTLM. And in ALL OTHER working connection methods, it's also NTLM. What the hell is Kerberos doing to me??
Best Answer
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