How to lock a sql login after N unsuccessful login attempts

Consider I have login called sql_login. Can I lock sql_login login after 5 unsuccessful login attempts.

When we create a login we can see there is option called password policy check the image. But there is nothing mentioned about locking password

Is there a option in Sql Server to lock a login after N unsuccessful login attempts

Yes, you can but you have to set everything up.

SQL Server (SQL) Login

When you enable the Enforce password policy option in the SQL Server Login window, you are basically telling the SQL Server to adhere either to the local security policy or to the policy defined in the domain.


The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN (Transact-SQL) to configure the password policy options of a SQL Server login. The following rules apply to the configuration of password policy enforcement:

• When CHECK_POLICY is changed to ON, the following behaviors occur:

• CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF.

• The password history is initialized with the value of the current password hash.

• Account lockout duration, account lockout threshold, and reset account lockout counter after are also enabled.

and also:


The security policy might be set in Windows, or might be received from the domain. To view the password policy on the computer, use the Local Security Policy MMC snap-in (secpol.msc).

Reference: Password Policy (MSDN / SQL Server 2012)

Setting Policy (locally)

So if you require an account to be locked out after 3 wrong tries, then you either have to define a local policy with secpol.msc or define a domain policy for account lockout.

Example in secpol.msc

SQL Server will then use this policy if the Enforce password policy option is checked.

A technical overview of the account lockout policy can be found here:

Reference: Account Lockout Policy Technical Overview (MSDN)

Reference: Account lockout threshold (MSDN)

Locked out SQL Login

Here is what happens after a SQL Login has been locked out after the set amount of incorrect logins (15 in my case as domain policy). You can see the Login is locked out is set. This can be unset to unblock the account.

StackExchange Database Administrators Q#164240, answer score: 13