Wednesday, August 19, 2015

What port is SQL Server listening on?

I was going through some of my older posts today, and I found a response to one that I had never seen before.  You'll see it at the bottom of this page:
    http://www.sqlfingers.com/2011/05/tcp-port-ip-address.html

My post is just a quick tip for finding the port that SQL Server is listening on, using xp_regread. As my reader says, xp_regread is undocumented and unsupported... I have used the xp's many times over the years, but the point is valid. They're unsupported and could even be gone in the next build.  If you can work around them, you probably should.

And I have! One of my favorite DMVs is sys.dm_exec_connections. You can use it to monitor your active connections and all kinds of fun stuff, as I've sampled hereIn this particular case, we can use sys.dm_exec_connections to return the port number that SQL Server is listening on. 

       SELECT local_tcp_port
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

Your output will resemble this:
















Super easy, documented... and no longer unsupported.  :-) 

Take a look at both of these for more details regarding the DMVs:
    https://msdn.microsoft.com/en-us/library/ms181509(v=sql.110).aspx
    https://msdn.microsoft.com/en-us/library/ms188754.aspx

No comments:

Post a Comment