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.

Rate

Share

Share

Rate