What port is my instance listening on?

6

January 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.

SSCM_Port

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.

About these ads

6 thoughts on “What port is my instance listening on?

  1. Simon L says:

    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.

  2. Ljohnson says:

    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

  3. 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.

  4. Jack Vamvas says:

    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

  5. rlvantrease says:

    I put a PowerShell/SMO version on PoshCode. You will find other examples there as well.
    http://poshcode.org/3978

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 301 other followers

%d bloggers like this: