Connect SQL Server Named Instance Using Specific Port Number

Recently I encountered an exception while trying to connect to SQL Server named instance.

We have a main server with SQL Server 2005 installed on as the default instance and another SQL Server 2008 installed as a named instance.

The connection string I used was:
"Data Source=ServerName\InstanceName,1433;network library=dbmssocn;Initial Catalog=MyDataBaseName;Integrated security=SSPI"

When I was trying to execute the SqlConnection.Open() method, SqlException has been thrown. The SQL connection tried to connect the database that was located on the default SQL instance of the specified server (instead of the named instance) and encounter an error.

At the beginning, I was pretty sure that this was because of the "Network library=dbmssocn" setting, but after a little research I've found that the problem that cause the connection to be established to the default instance instead of the desired named instance, was the fact that I've provide that specific port number.

Only the SQL Server default instance listens to the 1433 port. Any other named instance uses a dynamic port number (This is all by default, and absolutely configurable).

When specifying port number among SQL named instance, the named instance will be ignored and the connection will be created against the default instance.
That is why the exception has been thrown, as the database wasn't exist on the default instance, but on the named one I've provided.

Omitting the port number solves this issue and enables you to connect the named instance as well over TCP/IP.

As mentioned above, you can easily configure your SQL Server named instance to use a fixed port number (e.g. for firewalls configuration).

The most important thing you have to ensure when playing the ports configuration is when you're writing the connection string, if you want to specify a port number, the SQL instance you turn to must be listening to that port. Otherwise, an error might be thrown.

Anyway, the port number is a main factor of the connection string. If you're trying to connect the default instance, but you providing one of the named instance ports, you'll be connected to that named instance, and vice versa, if you try to connect a named instance but you specify the default instance port, you'll be connected with the default instance.
The connection will be made to the instance who's listening to that port.
When no one listen that port, you'll get an exception saying:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.)"

If the port is been listened, but belongs to another SQL Server instance which doesn't have the database with the same name or you're not authorized to access it, you'll get another SqlException saying that the database couldn't be open due to login permissions.
If the database exists on the other server and you have permissions, your query will be executed against that (unwanted) server/database who's listening to your port so be careful with that.

Again, you can omit the port number and as long as the SQL browser service is on, you won't have any problem with that as the SQL browser service will find the port for you.

You might consider turning the SQL browser service off, in order to enforce the client to know in advance the port number and specify it at the connection string when connecting the SQL server (e.g. for security reasons).

That was a little bit overhead with SQL server instances and their TCP/IP ports but this is a cool stuff :-)

Hope this helps,
Tamir Shlomi.

Resources and further reading:

1. Setting connection string properties: http://msdn.microsoft.com/en-us/library/ms378988(SQL.90).aspx
2. Network library property: http://support.microsoft.com/kb/238949
3. Configuring SQL Server TCP/IP ports: http://msdn.microsoft.com/en-us/library/ms177440.aspx
4. Configuring SQL Server TCP/IP ports (more detailed, step-by-step guide): http://www.databasejournal.com/features/mssql/article.php/3689846/Using-Non-Standard-Port-for-SQL-Server.htm
5. SQL browser service: http://msdn.microsoft.com/en-us/library/ms181087.aspx