A key part of any data security strategy is the ability to track who has accessed, or attempted to access, your data. This provides the ability to detect unauthorized access attempts or, if necessary, to piece together the actions of malicious insiders who misused their legitimate access. Auditing Login attempts to SQL Server is very important piece of overall Auditing strategy. In SQL Server 2008 and later, auditing failed login attempts is enabled by default. You can also specify to audit all logins. Although auditing all logins increases overhead, you may be able to deduce patterns of multiple failed logins followed by a successful login, and use this information to detect a possible login security breech.
On a failed login attempt, a message similar to the following is written to the SQL Server errorlog. The message provides additional information which can be used to determine the reason behind failed Login Attempt.
Error: 18456, Severity: 14, State: 8.
Login failed for user 'testlogin'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
When auditing failed login attempts is enabled, the database administrator can use the information written to the errolog as a result of failed login attempt to determine the reason for the failed login. For more information refer to Understanding "login failed" (Error 18456) error messages
SQL Server 2008 and later also offers a complete Auditing solution that offers a number of attractive advantages that may help DBAs more easily achieve their goals such as meeting regulatory compliance requirements. These include the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. Perhaps most significantly, SQL Server Audit permits fine-grained auditing whereby an audit can be targeted to specific actions by a principal against a particular object. Fore more information, refer to the Whitepaper Auditing in SQL Server 2008
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)
Auditing Login failures
The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rule to detect when Auditing failed logins is disabled. If you run the BPA tool and encounter warning with the title of Engine - Auditing Login failures, the Auditing login failures is disabled. For more information on configuring login auditing, refer to article How to: Configure Login Auditing
SQL Server 2008
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)
Auditing Login failures
The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rule to detect when Auditing failed logins is disabled. If you run the BPA tool and encounter warning with the title of Engine - Auditing Login failures, the Auditing login failures is disabled. For more information on configuring login auditing, refer to article How to: Configure Login Auditing
SQL Server 2012
Article ID: 2187161 - Last Review: 04/03/2012 00:41:00 - Revision: 2.0