How to connect an ODBC DSN to nondefault SQL Server instance on default port?

Question

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

Solution 1

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

Solution 2

First verify that your instance of SQL Server has the TCP/IP protocol enabled. Then examine which TCP/IP port your instance of SQL Server is "listening on".

Then If there is a firewall on this machine check If there is an exception in the rules (inbound) for the SQL Server Management Studio program If so, then it would be able to pass through the firewall, but ODBC would not.

About the server reference, you may try Reference your server as "servername\instancename" or using the port number, try either "servername, 1433" or "servername\instancename, 1433" as your server address.

Your server also needs to know which port to reply on, when the TCP port is blank is not mean to use the default port. Depending on what you want to achieve you may need to do or verify if one of these thing are done correctly:

How port allocation is set in your server https://dba.stackexchange.com/questions/47651/when-is-a-dynamic-port-dynamic

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager) http://msdn.microsoft.com/en-us/library/ms177440.aspx

Assign a static port to a SQL Server named instance - and avoid a common pitfall http://blogs.msdn.com/b/arvindsh/archive/2012/09/08/how-to-assign-a-static-port-to-a-sql-server-named-instance-and-avoid-a-common-pitfall.aspx

To connect to remote SQL Servers you have two options one is to use IP and Port (Which is safer) or explicit specify the named instance and opening UDP port 1434 and enabling SQL Server Browser.

The reason is:Only named instances of SQL Server can use the dynamic port allocation process. In the dynamic port allocation process, when you start the instance of SQL Server for the first time, the port is set to zero (0). Therefore, SQL Server requests a free port number from the operating system. As soon as a port number is allocated to SQL Server, SQL Server starts listening on the allocated port.

When an instance of SQL Server uses dynamic port allocation, the connection string that is built at the SQL Server client does not specify the destination TCP/IP port unless the user or the programmer explicitly specifies the port. Therefore, the SQL Server client library queries the server on UDP port 1434 to collect the information about the destination instance of SQL Server. When SQL Server returns the information, the SQL Server client library sends the data to the appropriate instance of SQL Server.

If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may be unable to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2008.

Furthermore depending on what you want you may want to use a System ODBC, rather than a User ODBC. The difference is that the User ODBC is tied to one user account on the machine.

Comments

[Smbk]

Thanks for your detailed reply. TCP/IP is enabled, and the firewall is correctly configured. SQL Management Studio can connect, and so does ODBC. My problem is that in ODBC I need to specify "servername,1433" in order to connect, while "servername" (no port) does not work. SQL Management Studio works with or without port number. There are many clients already configured with a User DNS pointing to "servername" (no port) and it works on the current server, but won't work on my new server unless I explicitly specify the port number.

[Smbk]

Thanks, Edson. I guess I will need to move that database to the default instance.

[Brandon Barkley]

In my case aliasname,1433 was sufficient to get around this issue. Thanks for this post. Our DBA was not even aware of this restriction.