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
See Associated Figure
End of Article
