What port is my instance listening on?
8January 8, 2013 by Kenneth Fisher
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.
SQL Studies 
i’ve also been using sys.dm_exec_connections trick to check the sql port. but rather than select distinct, i filter it with “Where session_id = @@SPID” and run it from SSMS. This should return the same.
That is a good way to check the ip/port combination that you are using. And most of the time that’s really all that will be there. I did the SELECT DISTINCT just to catch anyone who was coming in off of a different ip/port combination. It does happen occasionally. I have one instance that has 4 or 5 different servername/instancename combinations that will connect to it, and each one has a different ip/port combination.
I also realized you can use setspn to get your port number if your instance is set up for kerberos. It would be a rather restrictive method though. You have to have setspn installed on your machine or one that you can get to and the instance has to have a SPN assigned to it.
if the server is in use, run netstat -a from the command line …
of if the client is connected run netstat -a on the client
sqlping from http://www.sqlsecurity.com will list all the instances on a server, including the IP and Port they are listening on. It uses the SQL Server Browser Service to return results, rather than the registry, Windows logs, or a SQL Server connection.
This is the only method I’ve found that works with _no_ access to the server (or unpriveleged access to the server). All the other methods I’ve seen require some kind of Windows Server or SQL Server admin access.
If you have remote access and Powershell – then combine Powershell with a call to sys.dm_server_registry – http://www.sqlserver-dba.com/2012/10/sql-server-find-sql-server-tcp-port-with-powershell.html
I put a PowerShell/SMO version on PoshCode. You will find other examples there as well.
http://poshcode.org/3978
Kenneth, have you tried select * from sys.dm_tcp_listener_states?
I haven’t. And that’s a great tip! Unfortunately it’s 2012 and up and most of my instances are 2008 R2. I’ll add it to my bag of tricks though 🙂