sys.dm_broker_connections (Transact-SQL)

Returns a row for each Service Broker network connection. The following table provides more information:

Column name	Data type	Description
connection_id	uniqueidentifier	Identifier of the connection. NULLABLE.
transport_stream_id	uniqueidentifier	Identifier of the SQL Server Network Interface (SNI) connection used by this connection for TCP/IP communications. NULLABLE.
state	smallint	Current state of the connection. NULLABLE. Possible values: 1 = NEW 2 = CONNECTING 3 = CONNECTED 4 = LOGGED_IN 5 = CLOSED
state_desc	nvarchar(60)	Current state of the connection. NULLABLE. Possible values: NEW CONNECTING CONNECTED LOGGED_IN CLOSED
connect_time	datetime	Date and time at which the connection was opened. NULLABLE.
login_time	datetime	Date and time at which login for the connection succeeded. NULLABLE.
authentication_method	nvarchar(128)	Name of the Windows Authentication method, such as NTLM or KERBEROS. The value comes from Windows. NULLABLE.
principal_name	nvarchar(128)	Name of the login that was validated for connection permissions. For Windows Authentication, this value is the remote user name. For certificate authentication, this value is the certificate owner. NULLABLE.
remote_user_name	nvarchar(128)	Name of the peer user from the other database that is used by Windows Authentication. NULLABLE.
last_activity_time	datetime	Date and time at which the connection was last used to send or receive information. NULLABLE.
is_accept	bit	Indicates whether the connection originated on the remote side. NULLABLE. 1 = The connection is a request accepted from the remote instance. 0 = The connection was started by the local instance.
login_state	smallint	State of the login process for this connection. Possible values: 0 = INITIAL 1 = WAIT LOGIN NEGOTIATE 2 = ONE ISC 3 = ONE ASC 4 = TWO ISC 5 = TWO ASC 6 = WAIT ISC Confirm 7 = WAIT ASC Confirm 8 = WAIT REJECT 9 = WAIT PRE-MASTER SECRET 10 = WAIT VALIDATION 11 = WAIT ARBITRATION 12 = ONLINE 13 = ERROR
login_state_desc	nvarchar(60)	Current state of login from the remote computer. Possible values: Connection handshake is initializing. Connection handshake is waiting for Login Negotiate message. Connection handshake has initialized and sent security context for authentication. Connection handshake has received and accepted security context for authentication. Connection handshake has initialized and sent security context for authentication. There is an optional mechanism available for authenticating the peers. Connection handshake has received and sent accepted security context for authentication. There is an optional mechanism available for authenticating the peers. Connection handshake is waiting for Initialize Security Context Confirmation message. Connection handshake is waiting for Accept Security Context Confirmation message. Connection handshake is waiting for SSPI rejection message for failed authentication. Connection handshake is waiting for Pre-Master Secret message. Connection handshake is waiting for Validation message. Connection handshake is waiting for Arbitration message. Connection handshake is complete and is online (ready) for message exchange. Connection is in error.
peer_certificate_id	int	The local object ID of the certificate that is used by the remote instance for authentication. The owner of this certificate must have CONNECT permissions to the Service Broker endpoint. NULLABLE.
encryption_algorithm	smallint	Encryption algorithm that is used for this connection. NULLABLE. Possible values: Value Description Corresponding DDL option 0NONE Disabled 1RC4 {Required | Required algorithm RC4} 2AES Required algorithm AES 3NONE, RC4{Supported | Supported algorithm RC4} 4NONE, AESSupported algorithm RC4 5RC4, AES Required algorithm RC4 AES 6AES, RC4 Required Algorithm AES RC4 7NONE, RC4, AES Supported Algorithm RC4 AES 8NONE, AES, RC4 Supported algorithm AES RC4
encryption_algorithm_desc	nvarchar(60)	Textual representation of the encryption algorithm. NULLABLE. Possible Values: Description Corresponding DDL option NONE Disabled RC4 {Required | Required Algorithm RC4} AES Required Algorithm AES NONE, RC4{Supported | Supported Algorithm RC4} NONE, AESSupported Algorithm RC4 RC4, AES Required Algorithm RC4 AES AES, RC4 Required Algorithm AES RC4 NONE, RC4, AES Supported Algorithm RC4 AES NONE, AES, RC4 Supported Algorithm AES RC4
receives_posted	smallint	Number of asynchronous network receives that have not yet completed for this connection. NULLABLE.
is_receive_flow_controlled	bit	Whether network receives have been postponed due to flow control because the network is busy. NULLABLE. 1 = True
sends_posted	smallint	The number of asynchronous network sends that have not yet completed for this connection. NULLABLE.
is_send_flow_controlled	bit	Whether network sends have been postponed due to network flow control because the network is busy. NULLABLE. 1 = True
total_bytes_sent	bigint	Total number of bytes that were sent by this connection. NULLABLE.
total_bytes_received	bigint	Total number of bytes that were received by this connection. NULLABLE.
total_fragments_sent	bigint	Total number of Service Broker message fragments that were sent by this connection. NULLABLE.
total_fragments_received	bigint	Total number of Service Broker message fragments that were received by this connection. NULLABLE.
total_sends	bigint	Total number of network send requests that were issued by this connection. NULLABLE.
total_receives	bigint	Total number of network receive requests that were issued by this connection. NULLABLE.
peer_arbitration_id	uniqueidentifier	Internal identifier for the endpoint. NULLABLE.

Permissions

Requires VIEW SERVER STATE permission on the server.

Physical Joins

Relationship Cardinalities

From	To	Relationship
dm_broker_connections.connection_id	dm_exec_connections.connection_id	One-to-one

See Also

Reference

Dynamic Management Views and Functions (Transact-SQL)

Service Broker Related Dynamic Management Views (Transact-SQL)

Change History

Updated content
Added missing values to the login_state and login_state_desc descriptions.