Today I needed to make a connection to a named instance of a Microsoft SQL Server 2008 R2 database, however I could not use the named instance in the application I was using. Instead, server name and port needed to be used.

Important things to note: * Each named instance is assigned it's own port number. * The table sys.dm_exe_connections has this port information.

The following query will give you the port number if you are already connected to the database:

SELECT DISTINCT LOCAL_TCP_PORT FROM sys.dm_exec_connections WHERE LOCAL_TCP_PORT is not null;

I have heard a rumor that there are some configurations of SQL server that would allow the server to assign random ports on reboot, but I have not been able to verify that. It should just be considered that there is a possibility that if the server is rebooted you may have to rebuild any connections using the port number.