Connection To R2 Named Instance

Question

Will preface this by saying I am not at all convinced yet this is not the vendor doing something wrong on their application server. Just looking for new ideas

Instance X is a named instance of SQL Server 2008 R2. It houses numerous databases and takes in connections from numerous application servers. Browser service is running and all connections are made by way of server\instance name.

Moved DB from an old 2K5 instance over to it the other day and vendor claims he cannot connect to the DB. He says he is just putting in the Server\instance name in his connection string and his connection fails. At this time he cannot/has not provided me with any real error message.

Whenever he tries, we see no evidence of success or failure. As of right now there is no evidence he is even hitting the named instance. He can hit a test 2008 R2 default instance though. So, since the named instance appears to be the difference, I am thinking this has something to do with the port number. We have not explicitly tried the port number because we need it to work with just the name and vendor swears it should. However, we can connect with his credentials, via SQLCMD and by setting up a basic ODBC.

Application server is 2003R2 and in my opinion, craped up, but replacing it is not an option. DBNETLIB.DLL version is 2000.86.3959.0 and native client version is 2005.90.3042. You would have to go quite a ways back to not be able to hit a named instance.

Am I missing anything overly obvious? From a customer service perspective would prefer to not just say “Works for everyone else!”.

EDIT 9:44PM Central - Should have noted on original post that the firewall is not on on the application server and we do not filter/block anything internal to the network.

Answer 1

This can be a client error, not a server error. That is, the server responds (as you note) to other clients but not a certain one.

Summary

• remove all client aliases
• ensure tcp is default and top protocol
• ensure servername\instancename only is used

Background

It is possible that there is a "client alias". The client alias will basically "hijack" servername\instancename and overrides port, protocol, instance, server etc. Personally I don't like them.

This can be done explicitly with the "SQL Server Configuration Manager" or by the Control panel ODBC thing. Check and remove it. Alternatively, you can poke around in the registry and remove it under:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo

A second option is that port is specified. If I use servername\instancename,1234 then this is effectively servername,1234. Instancename and resolution of instancename via SQL browser are not performed.

A third option is that the default protocol is named pipes not tcp. This can happen if someone has messed around with "SQL Server Configuration Manager" and is liable to show on server migrations when you drop np or such from the new server. However, you also fix this in the registry too.

There are more exotic option such as hard coded HOSTS file or wrong DNS. But generally is the a fault of the SQL Server client configuration.

Answer 2

Below is the checklist I go through when the connection to a new SQL Server fails from any network application. In your case, points 5 and 6 might apply.

1. Check if the SQL Server has SQL and Windows authentication enabled
2. Check if the SQL Server Browser Service is enabled and running
3. Check if TCP/IP connections are enabled for your SQL Server instance (default is usually disabled)
4. Check if the server firewall allows connections to the SQL Server and SQL Server Browser
5. Check if the database user defined in your database also exists on the new SQL Server with same login credentials
6. Check if the database user has the according access rights on your database if you moved your database from another server to the new one
7. And last but not least, check your application database configuration if it is really doing what you expect (this should probably go first)