Ensuring that the Login Auditing option is set to failed logins

This setting records failed authentication attempts for the SQL Server logins to the SQL Server Error log. The failed logins is the default setting for the SQL Server.

This setting is available in all the versions and editions of the SQL Server. Prior to the availability of the SQL Server Audit, this setting was the only provided mechanism for capturing the logins (successful or failed).

To set the value of the Login Auditing option to failed logins, you must:

  1. Run EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2.
  2. Restart the SQL Server instance.