sys.dm_exec_connections

Returns server-level information about the connections to SQL Server.

The sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests dynamic management views map to the sys.sysprocesses system table.

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)

The SQL Server authentication scheme for a connection. The mechanism for Windows authentication (NTLM, KERBEROS, DIGEST, BASIC, NEGOTIATE) or SQL for SQL Server authentication. 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(40) (SQL Server 2005)

varchar(48) (SQL Server 2005 Service Pack 1)

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(40) (SQL Server 2005)

varchar(48) (SQL Server 2005 Service Pack 1)

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.

Relationship Cardinalities

From	To	On	Relationship
sys.dm_exec_connections	sys.dm_exec_connections	parent_connection_id = connection_id	One-to-zero or one-to-many
sys.dm_exec_sessions	sys.dm_exec_connections	session_id	One-to-zero or one-to-many
sys.dm_exec_connections	sys.dm_exec_requests	connection_id	One-to-zero or one-to-one
sys.dm_exec_connections	sys.dm_broker_connections	connection_id	One-to-zero or one-to-one

Examples

A. Returning information about the current connection

The following example returns basic information about the current connection: network transport protocol, authentication mechanism, and encryption setting.

SELECT net_transport, auth_scheme, encrypt_option
FROM sys.dm_exec_connections
WHERE session_id=@@SPID;
GO

B. Identifying clients that are connected to server

The following example returns the number of connections to the server and displays the originating IP address and port of each connection.

SELECT session_id, client_net_address, client_tcp_port
FROM sys.dm_exec_connections;
GO

See Also

Reference

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
Dynamic Management Views and Functions
Execution Related Dynamic Management Views and Functions

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added information about mapping to the sys.sysprocesses system table. Added the examples. Changed content: Corrected the description of the auth_scheme column.
14 April 2006	Changed content: The data type of client_net_address and local_net_address has expanded for SQL Server 2005 Service Pack 1.