SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

What port is my instance listening on?

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 [ <ipv4> 1433].” This means that the IP address 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 [ <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.


My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.


Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...