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.
CREATE SERVER AUDIT audit_test
TO FILE (FILEPATH = 'C:\Audit')
Now create audit specification against FAILED_LOGIN_GROUP.
CREATE SERVER AUDIT SPECIFICATION audit_test_spec
FOR SERVER AUDIT [audit_test]
You can see these audits in Object Explorer under Security=>Audits.
Now suppose you logon 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.