We are migrating an SQL Server 2000 database on one server to SQL Server 2008 R2 on another server. The client application use a User DSN to connect directly to the SQL Server on the internet.
I have backed up the database on the old server, and restored in on the new server, and am able to log in using SQL Management Studio, run queries, and so on.
The SQL Server in the new server is not the default instance, but I used SQL Configuration Manager to change the default port of that instance to 1433. SQL Management Studio can connect to the correct instance by just specifying the server IP or domain name (so no firewall issues, or so I would think).
So far, so good.
The problem comes when I try to connect to the server with my client application. I get a Connection Error/Invalid instance error. The client app run in around 100 computers in 50 different locations, so reconfiguring each cannot be done in a day, thus causing some downtime.
I tried creating a DSN in my computer to test the connection. If I specify the IP address with a port number (123.123.123.123,1433) it works, but if I only use the IP address (123.123.123.123) I get the same error as above.
Connection failed:
SQLState: '01000'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Invalid Instance()).
Connection failed:
SQLState: '08001'
SQL Server Error: 14
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Invalid connection.
The only difference I can think of between the new server and the old, apart from the SQL Server version, is that in the old it is the default instance, while in the new one it is a named instance.
Do you have any ideas of what I could try next?
EDIT:
A few other things I have tried:
- I am using the SQL Server ODBC driver. If I use the SQL Server Native Client driver, everything works as I expect.
- If I create the DSN connection in the same server, using the public IP address of the server, the same behavior is observed.
- If I stop the nondefault instance, and run the default instance on the 1433 port, it Works as expected (without specifying the port). If I set the nondefault instance to listen on port 1433, I need to explicitly specify the port to connect.
END EDIT
Thanks!
Luis Alonso Ramos
Best Answer
It seems that what you want to achieve is not possible using that client.
The issue is a result of older SQL clients (specifically using MDAC sqlsvr32.dll driver) perform an "InstanceValidity" check when connecting to SQL Server. The driver passes "MSSQLServer" as the instance to verify for the InstanceValidity check. In this case, since the instance name listening on the Default port (1433) is named "Instance01" and "Instance02", it fails because the Instance names do not match the InstanceValidity check.
According to what is most convinient for you in this case you might need to specify the port on your client, change your client, or change your named instance to be your default instance.
Source: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7f353b59-7e7f-4ec3-adcb-e69ca2629b21/named-sql-2008-r2-server-listening-on-default-port-1433-with-dedicated-ip-address-requires-port?forum=sqldataaccess