SQL Server – How to Lock Login After N Unsuccessful Attempts

loginspasswordsql serversql-server-2012

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

Best Answer

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.

Related Solutions

Sql-server – How to switch to sa account from guest session in SQL server 2012

When signed in under the guest account, you have very few privileges by default. The sa password is displayed as blank because you do not have the necessary rights to view it. Disabling the guest account is a security best practice, where it is not required.

The real sa password is almost certainly not blank. SQL Server does not allow a blank sa password since the 2005 release.

SQL Server does provide a way to impersonate another login or user (assuming you have sufficient rights). See EXECUTE AS (Transact-SQL) for details.

Note that SETUSER is deprecated, provided only for backward compatibility, and has inferior functionality.

SQL Server 2012 Security – How to Know Remaining Expiration of SQL Login Password Date

The information being requested here is not SQL Server information. It is Windows / OS -level information. This information is not available within SQL Server (i.e. there does not appear to be any DMV containing this info).

It should also be noted that the Login properties of is_policy_checked and is_expiration_checked only pertain to SQL Server Logins, not Windows Logins (since Windows Logins already have those enforced as per system / Domain policy).

The only way to get the password policy information is to get it from the Operating System. You can either use a GUI or command-line NET ACCOUNTS as follows:

C:\>NET ACCOUNTS

Force user logoff how long after time expires?:       Never
Minimum password age (days):                          0
Maximum password age (days):                          42
Minimum password length:                              0
Length of password history maintained:                None
Lockout threshold:                                    Never
Lockout duration (minutes):                           30
Lockout observation window (minutes):                 30
Computer role:                                        WORKSTATION

By default, NET ACCOUNTS displays information for the local system. If your computer is attached to a Domain and your login is a Domain account, then you need to add the /DOMAIN switch:

C:\>NET ACCOUNTS /DOMAIN

The request will be processed at a domain controller for domain {computers_domain_name}.

Of course, if you don't have permission to see that information, then you need to contact a Domain Administrator.