sys.dm_broker_connections
Returns a row for each Service Broker network connection.
Column name
Data type
Description
connection_id
uniqueidentifier
Identifier of the connection. NULLABLE.
transport_stream_id
uniqueidentifier
Identifier of the SQL Native 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. NULLABLE. Possible values:
0 = Initial
1 = Negotiate
2 = SSPI
3 = PublicKeyLogin
4 = PublicKeyTentative
5 = LoggedIn,
6 = Arbitration
login_state_desc
nvarchar(60)
Current state of login from the remote computer. NULLABLE. Possible values:
- Initial
- Negotiate
- SSPI
- PublicKeyLogin
- PublicKeyTentative
- Arbitration
- LoggedIn
peer_certificate_id
int
The local object ID of the certificate 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 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 which 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 sent by this connection. NULLABLE.
total_bytes_received
bigint
Total number of bytes received by this connection. NULLABLE.
total_fragments_sent
bigint
Total number of Service Broker message fragments sent by this connection. NULLABLE.
total_fragments_received
bigint
Total number of Service Broker message fragments received by this connection. NULLABLE.
total_sends
bigint
Total number of network send requests issued by this connection. NULLABLE.
total_receives
bigint
Total number of network receive requests 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
.gif "Joins for sys.dm_broker_connections")
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
Service Broker Related Dynamic Management Views