I just had the interesting task of finding the port number that one of the instances I deal with is using. Normally this is a trivial task. I log on to the server, open up SQL Server Configuration Manager and check out the TCP/IP properties.

In this case however it’s a server that I don’t have direct access to. So now I have to find another method to get to the port number. I thought I would share the methods that I found.

First and probably the easiest. Look in the log. Open the log file that contains the last time the instance was started up. Filter on the word “Listening”. You will get rows that look like this: “Server is listening on [ 123.123.123.123 <ipv4> 1433].” This means that the IP address 123.123.123.123 is listening on port 1433. I believe the <ipv4> is a reference to the port properties entry (IP1, IP2, IPALL etc) in SSCM (see the pic above). One thing to be careful of is something like this: “Server is listening on [ 123.123.123.123 <ipv4> 2889].” Followed by “Dedicated admin connection support was established for listening remotely on port 2889.” This is an ip/port combination that is specifically for the DAC (dedicated admin connection). You won’t be able to use this port for normal usage. I also found a tip on MSSQLTips here: (http://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/) that suggests basically the same thing but uses the extended stored procedure xp_readerrorlog.

Next I found a blog “Using DMVs to Find the Ports that SQL Server is Listening On.” (http://adventuresinsql.com/2010/11/using-dmvs-to-find-the-ports-that-sql-server-is-listening-on/) It uses the DMV sys.dm_exec_connections and joins it to sys.endpoints. I found I could just run the following:

 select distinct local_net_address, local_tcp_port from sys.dm_exec_connections

However this method does require that there be a TCP/IP connection made to the instance. And even more specifically that at least one of the connections uses the IP/Port combination that you are interested in.

You can also connect SSCM to another machine using the following instructions: (http://technet.microsoft.com/en-us/library/ms190622.aspx)

These instructions are for SQL 2012 but I’ve used them with SQL 2005 before as well.

And last but not least (of the methods I found) you could use xp_regread to read the port from the registry. I know it’s stored there (I‘ve seen it before when researching the TCP/IP properties in SSCM) but I can’t remember exactly where it is and I wouldn’t recommend the registry unless you are desperate anyway.

I could wish that there was a DMV that exposed the information in the same format that it can be found in SSCM but I wasn’t able to find one.

If you know a method that isn’t listed above please add it in the comments.