Connection Property : Port, IP .....

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.