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 LEFT OUTER JOIN sys.dm_exec_connections ec ON ec.endpoint_id = e.endpoint_id 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.
