Guidelines on Auditing Login failures in SQL Server.

Article translations Article translations
Close Close
Article ID: 2187161 - View products that this article applies to.
Expand all | Collapse all

Summary

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>]

More Information

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:

 

Rule software

Rule title

Rule description

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 2008 R2

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 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2187161 - Last Review: April 3, 2012 - Revision: 2.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 Standard
Keywords: 
KB2187161

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com