Connecting to SQL Server on a non-default Port

Here's a little tid-bit that threw me today. How to connect to SQL Server (using SQL Management Studio), if that SQL Server is not running on the default port (1433)?

I was actually trying to register an instance of SQL Server 2000 (the same would apply to 2005 though.) The reason this instance was not on the default port is because we happened to already have SQL Server 2005 installed, as the default instance, on the default port. So when installing we had to have a named instance of SQL Server, and on a different port. But how to get at it using the Client? I was unable to register the server, which would have been of the type "SQLBOX\instancename" if it were on the default port.

At first I couldn't find a way in SQL Management Studio to configure the port it communicated on (let's say it was 1234). I thought that maybe I'd better revert to the tried-and-trusted SQL 2000 Client. There, I configured the Client Network Utility such that TCP/IP spoke using port 1234 - but I still couldn't register the box on the Client. I was using the syntax "SQLBOX\instancename" as usual. I checked permissions and network connectivity; all were fine. How to do it?

The answer turns out to be simply in the syntax of how to register the server. It doesn't matter wether it is SQL 2005 or 2000 - it's still the same, and the syntax is:

"SQLBOX\instancename, 1234"

Yes, include the comma; I know it looks weird but it works! I was instantly able to talk to our 2000 instance, using the 2005 Client; no problem at all.

This is also how to configure your Database Connection string in your application configuration file (web.config or app.config) - complete with comma; e.g.

<connectionStrings><add name="MyNonStandardPortDb"connectionString="Database=DatabaseName;Server=SQLBOX\instancename,1234; Integrated Security=True;" providerName="System.Data.SqlClient" /></connectionStrings>