How to find the local tcp port used on SQL Server

18 October,2018 by Jack Vamvas

Question: How can I find the tcp port number used by a session ? I want to use a SQL Query to find this information as I don't have access to the cmdline.

 

Answer: One method to use from within SQL Server is the sys.dm_exec_connections  DMV . This SQL Server DMV returns various details about the current connections to a SQL Server Instance.

This query returns connections information for all existing connections. One of the columns is the "local_tcp_port"  , and when the net_transport is TCP - there will be a value in the "local_tcp_port"

 

SELECT 
suser_sname() as username
,local_tcp_port
,session_id
,connect_time
,net_transport
,protocol_type
,encrypt_option
,auth_scheme
,last_read
,last_write

FROM   sys.dm_exec_connections



 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to find the local tcp port used on SQL Server


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer