Audit Broker Login Event Class

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

SQL Server creates an Audit Broker Login event to report audit messages related to Service Broker transport security.

Audit Broker Login Event Class Data Columns

Data column Type Description Column number Filterable
ApplicationName nvarchar Unused in this event class. 10 Yes
ClientProcessID int Unused in this event class. 9 Yes
DatabaseID int SQL Server Profiler displays the name of the database if the ServerName data column is captured in the trace and the server is available. Determine the value for a database by using the DB_ID function. 3 Yes
EventClass int The type of event class captured. Always 159 for Audit Broker Login. 27 No
EventSequence int Sequence number for this event. 51 No
EventSubClass int The type of event subclass, providing further information about each event class. The table below lists the event subclass values for this event. 21 Yes
FileName nvarchar Remote broker authentication level. Supported authentication method configured on the remote broker endpoint. When more than one method is available, the accepting (target) endpoint determines which method is tried first. Possible values are:

None. No authentication method is configured.

NTLM. Requires NTLM authentication.

KERBEROS. Requires Kerberos authentication.

NEGOTIATE. Windows negotiates the authentication method.

CERTIFICATE. Requires the certificate configured for the endpoint, which is stored in the master database.

NTLM, CERTIFICATE. Accepts NTLM or SSL certificate authentication.

KERBEROS, CERTIFICATE. Accepts Kerberos or the endpoint certificate authentication.

NEGOTIATE, CERTIFICATE. Windows negotiates the authentication method or an endpoint certificate can be used for authentication.

CERTIFICATE, NTLM. Accepts an endpoint certificate or NTLM for authentication.

CERTIFICATE, KERBEROS. Accepts an endpoint certificate or Kerberos for authentication.

CERTIFICATE, NEGOTIATE. Accepts an endpoint certificate for authentication or Windows negotiates the authentication method..
36 No
HostName nvarchar Unused in this event class. 8 Yes
IsSystem int Indicates whether the event occurred on a system process or a user process. 1 = system, 0 = user. 60 No
LoginSid image The security identification number (SID) of the logged-in user. Each SID is unique for each login in the server. 41 Yes
NTDomainName nvarchar The Windows domain to which the user belongs. 7 Yes
NTUserName nvarchar The name of the user that owns the connection that generated this event. 6 Yes
ObjectName nvarchar The connect string used for this connection. 34 No
OwnerName nvarchar Supported authentication method configured on the local broker endpoint. When more than one method is available, the accepting (target) endpoint determines which method is tried first. Possible values are:

None. No authentication method is configured.

NTLM. Requires NTLM authentication.

KERBEROS. Requires Kerberos authentication.

NEGOTIATE. Windows negotiates the authentication method.

CERTIFICATE. Requires the certificate configured for the endpoint, which is stored in the master database.

NTLM, CERTIFICATE. Accepts NTLM or SSL certificate authentication.

KERBEROS, CERTIFICATE. Accepts Kerberos or the endpoint certificate authentication.

NEGOTIATE, CERTIFICATE. Windows negotiates the authentication method or an endpoint certificate can be used for authentication.

CERTIFICATE, NTLM. Accepts an endpoint certificate or for NTLM authentication.

CERTIFICATE, KERBEROS. Accepts an endpoint certificate or Kerberos for authentication.

CERTIFICATE, NEGOTIATE. Accepts an endpoint certificate for authentication or Windows negotiates the authentication method..
37 No
ProviderName nvarchar The authentication method used for this connection 46 No
RoleName nvarchar The role of the connection. This is either initiator or target. 38 No
ServerName nvarchar The name of the instance of SQL Server being traced. 26 No
SPID int The server process ID assigned by SQL Server to the process associated with the client. 12 Yes
StartTime datetime The time at which the event started, when available. 14 Yes
State int Indicates the location within the SQL Server source code that produced the event. Each location that may produce this event has a different state code. A Microsoft support engineer can use this state code to find where the event was produced. 30 No
TargetUserName nvarchar Login state. One of:

INITIAL

WAIT LOGIN NEGOTIATE

ONE ISC

ONE ASC

TWO ISC

TWO ASC

WAIT ISC Confirm

WAIT ASC Confirm

WAIT REJECT

WAIT PRE-MASTER SECRET

WAIT VALIDATION

WAIT ARBITRATION

ONLINE

ERROR



Note: ISC = Initiate Security Context. ASC = Accept Security Context
39 No
TransactionID bigint The system-assigned ID of the transaction. 4 No

The table below lists the subclass values for this event class.

ID Subclass Description
1 Login Success A Login Success event reports that the adjacent broker login process has finished successfully.
2 Login Protocol Error A Login Protocol Error event reports that the broker receives a message that is well-formed but not valid for the current state of the of the login process. The message may have been lost or sent out-of-sequence.
3 Message Format Error A Message Format Error event reports that the broker received a message that does not match the expected format. The message may have been corrupted, or a program other than SQL Server may be sending messages to the port that Service Broker uses.
4 Negotiate Failure A Negotiate Failure event reports that the local broker and the remote broker support mutually exclusive levels of authentication.
5 Authentication Failure An Authentication Failure event reports that Service Broker cannot perform authentication for the connection due to an error. For Windows Authentication, this event reports that Service Broker is unable to use Windows Authentication. For certificate-based authentication, this event reports that Service Broker is unable to access the certificate.
6 Authorization Failure An Authorization Failure event reports that Service Broker denied authorization for the connection. For Windows Authentication, this event reports that the security identifier for the connection does not match a database user. For certificate-based authentication, this event reports that the public key delivered in the message does not correspond to a certificate in the database.

See Also

CREATE ENDPOINT (Transact-SQL)
ALTER ENDPOINT (Transact-SQL)
SQL Server Service Broker