Locking out accounts with unsuccessful login attempts

How can I lock out all accounts that are unsuccessful three or more times when attempting to log in to SQL Server?

Account lock out is a core security tenet for DBAs and security administrators alike who are responsible for protecting organizational data. When users access SQL Server databases via a Windows account i.e. DomainUser, then the Windows principal of "three strikes and you're out" is applicable to any users who try to authenticate to SQL Server via a Windows account. If your user community is authenticating via SQL Server logins with the SQL Server 6.5, 7.0 and 2000 versions, the current functionality with these platforms is unfortunately very limited with the native functionality from Microsoft.

To verify the account lock out configuration, review the security properties for the Windows operating system in the Local Security Settings as outlined in Figure 1. By default with Windows server class of operating systems (Windows 2003 Server, Windows 2000 Server, Windows NT 4.0, etc.), the "three strikes and you're out principal" is enforced by default. With the desktop versions of Windows (2000 Pro, XP, etc.) then the account lock out property is not set to three attempts, but rather it is possible to continuously submit passwords. Keep this in mind for scenarios where SQL Server is installed on one of these machines.

Figure 1 – Local Security Settings – Account Lockout Policy

One option to address your need is to configure SQL Server to record the failed login attempts in the SQL Server Error Log. This can be achieved by right clicking on the server name in Enterprise Manager and select the "Properties" option. Then click on "Security" and review the "Audit Level" portion of the interface. By default the "Audit Level" is set to none and to achieve your desired level of auditing, see Figure 2. Then to achieve the notification, write an application to review the entries in the SQL Server Error Log for as an action item for a DBA or security administrator to address immediately.

Figure 2 – Enterprise Manager - Server Security Tab

Another option is Entegra from Lumigent Technologies that incorporates capturing and alerting based on predefined database activity. This is achieved with real time alerts to a pager or email address for any failed logins among other functionality as shown in the bottom row of Figure 3. This data and more can also be captured and stored in a centralized repository for reporting and forensics purposes.

Figure 3 – Lumigent Entegra - Alerting Capabilities

Briefly looking to the future, this account lockout functionality for both Windows and SQL Server logins was rumored to be available in Yukon, at the SQL PASS 2003 Community summit. If you can wait, stay tuned for the upcoming SQL Server release. Nevertheless, account lockout is a key tenet for proper security management and should be a core component to a comprehensive security plan relying on continuous improvements to protect your organization's biggest asset, your data.

Dig Deeper on Microsoft SQL Server Installation