SQL Server 2008 resolving localhost to a computer name

Question

We're in the process of switching from Windows Server 2003 to Windows Server 2008, and we've encountered a frustrating problem:

In the old environment, some developers used sql server 2005 and others used sql server 2008. No problems. Our connection strings all pointed to: localhost/sqlserver. Now, on the new Server 2008 environment, Sql Server 2008 is occasionally resolving the "localhost" which immediately causes the thing to throw an exception.

In sys.servers we've changed the entry to point to localhost/sqlserver using:

exec sp_dropserver 'buckaroo-banzai\sqlserver'
exec sp_addserver 'localhost\sqlserver', local
exec sp_serveroption 'localhost\sqlserver', 'data access', TRUE

and the most frequent offending sql statements look like this (I know it's deprecated form) (note: they aren't the only offenders, just the most common ones):

[localhost\sqlserver].[database].[table].exec sp_executesql blahblah; exec sp_another_sp

The error I'm getting from those is:

Server buckaroo-bonzai\sqlserver not found in sys.servers

switching my sys.servers entry back to buckaroo-bonzai\sqlserver gets this error:

Server localhost-bonzai\sqlserver not found in sys.servers

If everything refers to the sql server as buckaroo-bonzai\sqlserver everything works, but for development, this just isn't an option.

For the record, this hasn't happened before on our windows server 2003 environments, just the new server 2008 environments; any ideas?

possible workarounds I've thought of:

1. remove the app.config and web.config files from version control (yech)
2. laboriously keep different connection strings in version controlled files (double yech)
3. version control the files, but somehow have some unified way of referring to an sql server on a local host (localhost maybe? )= )
4. Actually figure out why sql server 2008 is resolving localhost and stop it dead. (yay!)

Answer 1

Check the hosts file for redirection

Answer 2

Well, sounds like a DNS issue. Not sure why it would have problems referring to itself as "localhost", but you might want to check your system HOSTS file, to make sure that hasn't been changed somehow.

The order of resolution for a DNS query is as follows:

1. The host first determines whether the name in question is its own name. It should take its actual name, as well as localhost, to be valid. If this isn't happening, it may not think it is who YOU think it is.
2. A Hosts file, located on each host, is queried. This file will override any external DNS.
3. DNS Servers are queried. If it can't find any information locally, it will ask someone, and that answer may not be correct, depending on how your DNS is configured.
4. As a last result, a host can use NetBIOS to try and find a host. This is bound to cause issues somewhere. Not a good thing to depend on, because often things will seem to just work, but then unexpected behaviour crops up, and it's hard to diagnose.

Answer 3

Does it work any better if you use "." instead of "localhost", as in ".\sqlserver"? A single period refers to the local machine, but there's obviously no way to do a DNS lookup on it, so I doubt SQL Server would try.

Answer 4

It's not DNS or HOST file: the 4 part object name can't resolve because of no sys.servers entry.

You might be able to workaround with sp_addlinkedserver

EXEC sp_addlinkedserver
         'buckaroo-banzai\sqlserver',
          N'SQL Server',
          @datasrc = 'localhost\sqlserver'

Now, this may fail (I can't test) because the "data_source" in sys.servers would be duplicated with "localhost\sqlserver". However, you can change this to something like "127.0.0.1\sqlserver" or "actualservername\sqlserver" to remove the duplicate.

I'm sure I did this some time ago...

Edit, after comment: The error might be related to Behaviour change in SQL 2008 for loopback linked servers. Sorry, i did't know about this.

sp_addserver is only useful for changing local server name (that is @@servername).