Search sqlserver-dba.com
Follow sqlserver-dba.com
*Use the Comments section for questions
SQLServer-DBA.com Links
Recent Posts
- The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
- How to find privilege changes with SQL Server Default Trace
- sys.dm_server_services - How to check if SQL Server service is running or not?
- Msg 3023, Level 16, State 2 Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.
- Can we restore a database to an older version in SQL Server?
- How do I save all SQL Agent Jobs
- How to view SQL Server mount point space without access to server
- DBCC SHRINKFILE: page could not be moved because it is a work table page.
- SQL Agent Last Modified explanation
- Sql certificate chain was issued by an authority that is not trusted
How to find the local tcp port used on SQL Server
18 October,2018 by Tom Collins
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: Tom Collins (http://www.sqlserver-dba.com)
Share:
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |