Many things in life have opposites. There are stop and go, beginning and end and of course in and out. SQL Server is not any different, you have BEGIN TRAN…COMMIT TRAN, successful login and failed login as well as a few others. Using Extended Events there is just a login event, not a successful or failed login event. If you look at the description of the login event this is what you will see:
Occurs when a successful connection is made to the Server. This event is fired for new connection or when connections are reused from a connection pool
Notice is states successful connections. What if I want to use Extended Events to track failed logins. Logic would say that if there is an event for successful logins, there would be an event for failed logins. In this case, logic would be wrong.
If you do a search for the work “fail” in the events a number of events will be returned. Many of the are related to column store and none of them are related to failed logins, most are related to column store.
Although, when I think of Extended Events, I usually think about how I can use it to capture performance related information. However, there are somethings, like failed logins, that could be helpful as well. Well how can I capture failed logins if there is not an event to do so.
To do this, you have to use the error_reported event and use predicates(filters) to return just failed logins.
As you can see in the above image, there are two filters we need to configure.
Severity is set to equal 14, this one is optional but might help eliminate unnecessary rows
error_number is set to equal 18456
state is set to greater than 8, this can be set to 1 as well
These combined allow you to capture failed logins. If you do a Google search on the error number you will find that the Microsoft documentation states this.
“When a connection attempt is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: “Login failed for user ‘<user_name>’. (Microsoft SQL Server, Error: 18456)”.”
This might have you wondering why the other predicates are needed. Well, it is to help eliminate similar rows. If you look at the image below, the two rows that are highlighted are the same failed login event. One has a severity of 1 and the other has a severity of 8. The other difference is the error message. As you can see, there is just a bit more information with severity 8 than with severity 1.
Severity 1 message: Login failed for user ‘testgrant’.
Severity 8 message: “Login failed for user ‘testgrant’. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]”
Just a little side note, to read the event file for Extended Events, simply right click on the target file in SQL Server Management Studio and click “View Target Data”.
This can be very useful information and in my opinion, might be a bit easier to query than reading the log file. It is always nice to have options. This works for an on-prem instance of SQL Server, I have not been able to get it to work on an Azure SQL Database yet. This could be due to the fact that in an Azure SQL Database, Extended Events are set up in the database. I am still working on that so look for a future blog post on that.
Thanks for visiting my blog.