Connecting to a local SQL Server with a host name set in the Hosts File using Windows Authentication (SSPI) October, 2008
If you have a host name set in your hosts file pointing to your loop back address and you are trying to connect to your local SQL Server instance with this host name using Windows Auth (SSPI) you may receive one of the following errors depending on how you have things configured:
"Login failed for user ''. The user is not associated with a trusted SQL Server connection."
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
There could be a number of things causing this error (here, here, here and here) but it in the specific scenario noted above it's probably that an alias is not set for the SQL Server. I'm not sure exactly why or how this works or why the above scenario would have an issue but creating an alias seems to resolve it. In fact if you specify "localhost" or the loopback address as the server name it works fine (Without the alias). Also when connecting to a remote SQL Server with a host name defined in the hosts file it works fine as well. hmmmm.... I really couldn't find any info on this specific scenario and creating the alias seems to resolve it so I finally gave up. If you have any more info on why this would happen please leave a comment. You can set the alias as follows:
- 1) Open the SQL Server Configuration Manager under Start|Programs| Microsoft SQL Server 20xx| Configuration Tools| SQL Server Configuration Manager.
- 2) Under the SQL Native Client Configuration node select Aliases.
- 3) Right-click and select "New Alias...".
- 4) Enter the following information and press OK:
- Alias Name: sql.somedomain.com
- Port No: 1433
- Protocol: TCP/IP
- Server: localhost
The connection string would be as follows: