To get locl tcp port and ip address:
select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null
To get all information related to sql connections:
select * from sys.dm_exec_connections
=============================================
Query Connection Properties :
SELECT
CONNECTIONPROPERTY('net_transport') AS net_transport,
CONNECTIONPROPERTY('protocol_type') AS protocol_type,
CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
CONNECTIONPROPERTY('local_net_address') AS local_net_address,
CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
CONNECTIONPROPERTY('client_net_address') AS client_net_address
=============================================
When connecting locally,
Transport
Protocol always comes “SHARED MEMORY” and TCP Port as NULL.
When
connecting remotely,
Transport Protocol always comes
“TCP” and TCP Port as negative number (if the port is set to dynamic port).
To
get the actual port, just do a subtraction from a number “65536” and we get the actual port number.
