sys.dm_exec_connections (Transact-SQL)

Returns information about the connections established to this instance of SQL Server and the details of each connection.

Column name	Data type	Description
session_id	int	Identifies the session associated with this connection. Is nullable.
most_recent_session_id	int	Represents the session ID for the most recent request associated with this connection. (SOAP connections can be reused by another session.) Is nullable.
connect_time	datetime	Timestamp when connection was established. Is not nullable.
net_transport	nvarchar(40)	Describes the physical transport protocol that is used by this connection. Is not nullable. Note Always returns Session when a connection has multiple active result sets (MARS) enabled.
protocol_type	nvarchar(40)	Specifies the protocol type of the payload. It currently distinguishes between TDS (TSQL) and SOAP. Is nullable.
protocol_version	int	Version of the data access protocol associated with this connection. Is nullable.
endpoint_id	int	An identifier that describes what type of connection it is. This endpoint_id can be used to query the sys.endpoints view. Is nullable.
encrypt_option	nvarchar(40)	Boolean value to describe whether encryption is enabled for this connection. Is not nullable.
auth_scheme	nvarchar(40)	Specifies SQL Server/Windows Authentication scheme used with this connection. Is not nullable.
node_affinity	smallint	Identifies the memory node to which this connection has affinity. Is not nullable.
num_reads	int	Number of packet reads that have occurred over this connection. Is nullable.
num_writes	int	Number of data packet writes that have occurred over this connection. Is nullable.
last_read	datetime	Timestamp when last read occurred over this connection. Is nullable.
last_write	datetime	Timestamp when last write occurred over this connection. Not Is nullable.
net_packet_size	int	Network packet size used for information and data transfer. Is nullable.
client_net_address	varchar(48)	Host address of the client connecting to this server. Is nullable.
client_tcp_port	int	Port number on the client computer that is associated with this connection. Is nullable.
local_net_address	varchar(48)	Represents the IP address on the server that this connection targeted. Available only for connections using the TCP transport provider. Is nullable.
local_tcp_port	int	Represents the server TCP port that this connection targeted if it were a connection using the TCP transport. Is nullable.
connection_id	uniqueidentifier	Identifies each connection uniquely. Is not nullable.
parent_connection_id	uniqueidentifier	Identifies the primary connection that the MARS session is using. Is nullable.
most_recent_sql_handle	varbinary(64)	The SQL handle of the last request executed on this connection. The most_recent_sql_handle column is always in sync with the most_recent_session_id column. Is nullable.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Relationship Cardinalities

dm_exec_sessions.session_id	dm_exec_connections.session_id	One-to-one
dm_exec_requests.connection_id	dm_exec_connections.connection_id	Many to one
dm_broker_connections.connection_id	dm_exec_connections.connection_id	One to one

Examples

Typical query to gather information about a queries own connection.

SELECT 
    c.session_id, c.net_transport, c.encrypt_option, 
    c.auth_scheme, s.host_name, s.program_name, 
    s.client_interface_name, s.login_name, s.nt_domain, 
    s.nt_user_name, s.original_login_name, c.connect_time, 
    s.login_time 
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Execution Related Dynamic Management Views and Functions (Transact-SQL)

Change History

Updated content
Added the note to the net_transport column about the MARS behavior.