A Clever Way to Connect to a Hidden Named Instance

We were experiencing problems connecting to a hidden SQL Server 2005 named instance. The only way we could successfully connect to the hidden named instance was through an alias on a client machine.

When I searched the Internet for a solution, I noticed that all the material I read said the connection strings had to be in the format ServerNameInstanceName,PortNumber (e.g., ProdServerReports,3334). This got me thinking. When this format is used, the connection string doesn't work with hidden named instances because the string is identifying the named instance. I wondered what would happen if the named instance wasn't part of the string, so I removed it. When I tried the revised connection string, which followed the format ServerName,PortNumber (e.g., ProdServer,3334), I was able to connect to the SQL Server 2005 named instance.

I've found that the shortened connection string also works with hidden SQL Server2000 named instances, many tools (including Enterprise Manager), and ODBC connectionstrings. You can even apply the concept to Java Database Connectivity (JDBC) connectionstrings. For example, our JDBC driver uses a connection string that follows the formatServerName:InstanceName:PortNumber (e.g., ProdServer:Reports:3334).When I usedonly the server name and port number in the format ServerName:PortNumber (e.g.,ProdServer:3334), I was able to connect to the hidden SQL Server 2005 named instance.

The shortened connection string works on any instance hosted on any server. I even made it a personal best practice to use the port number, even for the default port (1433), so I can better distinguish one instance from another. However, the shortened connection string only partially works with the dedicated administrator connection. So far, I've only been able to connect to an instance with the dedicated administrator connection though SQL Profiler and an ODBC connection string but not through SQL Server Management Studio (SSMS).
—Gilles Despaties, Senior Database Administrator, House of Commons of Canada

See Associated Figure