http://www.sqlservercentral.com/blogs/adventuresinsql/2010/11/29/using-dmvs-to-find-the-ports-that-sql-server-is-listening-on/

Printed 2014/04/23 08:01AM

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

By David Levy, 2010/11/29

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.