SQL Server 2008 resolves localhost to computer name
We are migrating from Windows Server 2003 to Windows Server 2008 and we faced the problem:
In the old environment, some developers used sql server 2005 and others used sql server 2008. No problem. Our connection strings were pointing to: localhost / sqlserver. Now, in the new Server 2008 environment, Sql Server 2008 sometimes allows "localhost" which will immediately cause the thing to throw an exception.
In sys.servers, we 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 common offending SQL queries look like this (I know this is a deprecated form) (note: they are not the only violators, but the most common ones):
[localhost\sqlserver].[database].[table].exec sp_executesql blahblah; exec sp_another_sp
The error I am getting from this:
Server buckaroo-bonzai\sqlserver not found in sys.servers
switching my sys.servers login 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 it just isn't an option.
For the record, this hasn't been on our Windows Server 2003 servers before, but only in the new 2008 server environments; any ideas?
Possible workarounds I thought of:
- remove app.config and web.config files from version control (yech)
- difficult to maintain different connection strings in version controlled files (double yech)
- version manages files, but somehow has a unified way to access sql server on localhost (maybe localhost)?) =)
- Actually, figure out why SQL Server Server resolves localhost and stops it. (Yay!)
source to share
This is not a DNS or HOST file: Part 4 object name could not be resolved due to missing sys.servers entry.
You might come across sp_addlinkedserver
EXEC sp_addlinkedserver
'buckaroo-banzai\sqlserver',
N'SQL Server',
@datasrc = 'localhost\sqlserver'
This may now fail (I cannot verify) because the "data_source" in sys.servers will be duplicated with "localhost \ sqlserver". However, you can change this to "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 may be related to Behavior change in SQL 2008 for loop-related servers . Sorry, I didn't know about this.
sp_addserver is only useful for changing the name of the local server (it is @@ servername).
source to share
Well, sounds like a DNS problem. Not sure why it is having trouble referencing itself as "localhost", but you can check your system's HOSTS file to make sure it hasn't been modified in some way.
The order of resolution for a DNS query is as follows:
- First, a node determines whether the specified name is its own name. It must accept its actual name as well as localhost for it to be valid. If it doesn't, it may not be the way you think it is.
- The Hosts file located on each host is requested. This file will override any external DNS.
- DNS servers are queried. If he cannot find any information locally, he will ask someone, and this answer may not be correct, depending on how your DNS is configured.
- As a final result, the host can use NetBIOS to find the host. This is bound to cause problems. It is not good to depend because often things seem to just work, but then unexpected behavior occurs and is difficult to diagnose.
source to share