Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using DMVs to Find the Ports that SQL Server is Listening On

The other day I was asked to provide the port number that a SQL Server instance was listening on. As luck would have it, the log file from the last time SQL Server started had rolled off so I was left with either using remote desktop to log onto the server and all the risk that includes or finally figuring out how to query it from the DMVs. I opted for the second option.

I did some digging, opening any of the system views that looked promising. Unfortunately, I was not having much luck. I was about to give up when I remembered that sys.dm_exec_connections will tell what IP address and port. If it had the endpoint_id to join to then I could create my own version of sys.endpoints with the information I need. A quick look at the view showed it had the fields I needed.

Here is the query I came up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT      e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint
FROM        sys.endpoints e
            LEFT OUTER JOIN sys.dm_exec_connections ec
                ON ec.endpoint_id = e.endpoint_id
GROUP BY    e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint

I hope you find this script helpful. Please let me know if you run into any issues with it or know a better way to do the same thing. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Comments

Posted by ThomasLL on 29 November 2010

Thanks, that was a good one. I had to remote to a server just last week to get this for a cognos connection.

Thanks,

Thomas

Posted by Jason Brimhall on 29 November 2010

Good info.

Posted by Dukagjin Maloku on 29 November 2010

Thanks for sharing!

Posted by Robert L Davis on 29 November 2010

I always found it irritating that sys.database_mirroring_endpoints doesn't have the port # in it. I usually join it to sys.tcp_endpoints, but that only contains the port for static ports and not for dynamic ports (like the TSQL port).

Posted by Ninja's_RGR'us on 1 December 2010

Good stuff.

Any reason why you didn't use distinct instead of group by for this query?

Posted by jts_2003 on 1 December 2010

Thanks - I've been looking for a way to obtian this info.

Posted by steve Schneider on 1 December 2010

Very nice!  I also, have wanted this for quite a while.

Thanks.

Posted by aditi.iyer on 1 December 2010

Thanks for sharing!

Posted by andy russell on 1 December 2010

Very nice.  Thanks.

Posted by Robert Streeter Jr on 1 December 2010

Fantastic! I had been querying registry keys to find the ports. I still need to use that for the SQL 2000 instances, but this will help for 2005+. Thanks

Posted by CriticalStatus on 1 December 2010

wish i would have had this last week!  thanks!  very useful!

Posted by Marcelo Bentolila on 1 December 2010

Hi, I run the script, but don't appears local_net_addres and local_tcp_port columns. Cointains NULL values. Thanks !!!

Posted by Mike Caldwell on 1 December 2010

I am unfamiliar with this solution, this is what I would have done, it works with any process using TCP...

lookup the process ID for the SQL server process, go to the command line, type "netstat -a -o", see which listening ports are owned by that process.

This will work for apps other than SQL Server, but may be of limited use in a multi-instance scenario, especially if there are lots of instances.

Posted by Linda Wenglikowski on 1 December 2010

Don't forget to look in the SQL Log, not only does it show the process ID sql is running under it lists the following:

Server is listening on ['any' <ipv4> 1433].

Dedicated admin connection support was established for listening locally on port 1434.

You can filter it and only display messages that contain listen.

If you have mirroring enabled, you might see entries that The Database Mirroring protocol transpos is now listening for connections, which usually follows Server is listening on ['any' <ipv4> 5022]. This is usually the mirror port.

Oh works in 2000 - 2008 R2, so you don't have to alter the script for 2000.

Posted by William Soranno on 2 December 2010

What are the risks of using remote desktop to the server?

I remote in, so I do not have to go 2 building over to the data center to use the console, to at least one of my servers every day to work on them.

Leave a Comment

Please register or log in to leave a comment.