How to track failed login attempts in SQL Server

SQL Server Audit allows you to track and monitor Database/Server level events occurs on database engine.For LOGIN events there is a SQL Server audit action group as “FAILED_LOGIN_GROUP”.You can see list of specifications here.

For auditing login attempts you have first create Server Audit.

USE MASTER

GO

CREATE SERVER AUDIT audit_test                                           

TO FILE (FILEPATH = 'C:\Audit')

GO

Now create audit specification against FAILED_LOGIN_GROUP.

CREATE SERVER AUDIT SPECIFICATION audit_test_spec       

FOR SERVER AUDIT [audit_test]

ADD (FAILED_LOGIN_GROUP)

WITH (STATE=ON)

GO

You can see these audits in Object Explorer under Security=>Audits.

Now suppose you log on to same instance on that above audit is applied with wrong credentials.

Now connect with right credentials. And go to

Object Explorer=> Security=>Audits=> audit_test

Right click on audit_test  and click View Audit Logs

In logs you can see various details of Client who have tried to login Database engine.Also a log file has been created to defined destination.

CodeProject