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 ServerName\InstanceName,PortNumber (e.g., ProdServer\Reports,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 Server 2000 named instances, many tools (including Enterprise Manager), and ODBC connection strings. You can even apply the concept to Java Database Connectivity (JDBC) connection strings. For example, our JDBC driver uses a connection string that follows the format ServerName:InstanceName:PortNumber (e.g., ProdServer:Reports:3334).When I used only 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
