SQL Server - Password Policies, Password Expiration, Mandatory Password Change, and Login Lockout after N attempts

SQL Server's Password Policy (CHECK_POLICY) feature is intended to ensure that login passwords are complex passwords so as to reduce the possibility of brute force attacks. It is worth remembering that this feature only applies to users with SQL authentication.

One of the goals of the password policy is to avoid this type of scenario below, where someone is trying to authenticate to the bank through trial and error, without being blocked, until he finds a combination where he can log into the bank.

In addition to restricting simple or empty passwords, the password policy can automatically block logins after N incorrect username/password attempts (this number is configurable), in addition to allowing passwords to expire after N days and it has to be changed periodically.

To create a user with password policy enabled, you can use this command:

To check SQL logins that do not have the password policy enabled, use the query below:

Result:

You can also view the Login properties to confirm that this option is enabled.

Speaking of password policy, you can check this link here the Windows password policy used by SQL Server for logins with SQL authentication.

When enabling password policy for an SQL login, you must follow the policy below:

• Password cannot contain user account name
• The password must be at least eight (8) characters long
• Passwords can be up to 128 characters long. Use long and complex passwords
• Null or blank passwords are not allowed
• It is not allowed to use the same computer name or login as the password.
• Passwords that are not allowed: “password”, “admin”, “administrator”, “sa”, “sysadmin”
• The password must contain characters from at least three of the four categories below:
  – Latin capital letters (A to Z)
  – Lowercase Latin letters (a to z)
  – Numbers (0 to 9)
  – Non-alphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percentage (%).

To check the Windows settings for some additional information such as the number of login failures to block a login, you can open the “Local Security Policy” screen by typing the command “secpol.msc” in the Start menu.

Or on the Run screen:

Navigate to the “Account Policies” directory and then the “Password Policy” directory

There are some interesting settings there on this screen regarding password complexity:

• Enforce password history: Number of passwords that will be stored to ensure that a previously used password is used again. This value must be between 0 and 24 passwords and the default value is 0 (zero)
• Minimum password length: Pretty obvious here. Defines the minimum number of characters that a password must have to be accepted as a valid password. Acceptable values ​​for this parameter are between 1 and 14. The default value is 0 (zero), which means there is no minimum size
• Password must meet complexity requirements: This parameter defines whether password policies should be used and passwords that do not fit the policy are prevented from being created. Default value is Enabled and if disabled SQL Server will not validate password complexity even if you enable CHECK_POLICY property in SQL Login

When trying to create a password with fewer characters than the one defined on this screen, you will see this error message:

Another interesting feature from a security point of view is the possibility to set an expiration date for SQL login passwords. Once enabled, this option (CHECK_EXPIRATION) will expire the login password after N days and it will only be possible to connect when changing the password.

To create a user whose password expires, you can use the command below:

To check expired or about to expire users, you can use the query below:

Result:

You can also view the Login properties to confirm that this option is enabled.

To change the number of days passwords expire, you can open the “Local Security Policy” screen by typing the command “secpol.msc” in the Start menu.

Or on the Run screen:

Navigate to the “Account Policies” folder and then “Password Policy”

Edit the “Maximum password age” property and set a number from 1 to 999 to define the number of days that a password expires. The default value is 42 days.

If you set the value 0 (zero), it means that the password will not expire. That is, even if you enable this password expiration option in SQL Server, it will not expire because the Windows policy setting is set to not expire.

The MUST_CHANGE property defines that the user must change the password the next time he connects to the database.

To create a login where he must change the password on the first use, you use something like this:

To view the logins that have this MUST_CHANGE property enabled, you can use the query below:

Result:

Unlike the other 2 properties (CHECK_POLICY and CHECK_EXPIRATION), it is not possible to see in the SSMS interface if the MUST_CHANGE property is enabled, only using T-SQL.

When trying to log in with this user in the database with this newly created user (test3), you will see this screen here:

To force a login that already exists to change its password, you can change its password to any one and activate the MUST_CHANGE option

I assume you know this, but SQL Server will only lock a SQL login if the password policy option is enabled for that login and the Windows/AD password policy settings are configured correctly.

If this combination is not configured correctly, you can try to connect as many times as you want, and SQL will only store connection failures in the log (if configured to do so), but will not block the login.

One way to prevent this from happening is to use only logins with Windows authentication (best option), or else you set a limit on the number of failed logins to perform automatic blocking.

To do this, you can open the “Local Security Policy” screen by typing the command “secpol.msc” in the Start menu.

Or on the Run screen:

Navigate to the “Account Policies” directory and then the “Account Lockout Policy” directory

The default configuration is the one in the screenshot, NOT blocking users due to failed connection attempts due to user/password error, which I think is very insecure.

We can even confirm that with the value 0 (zero), the password will NOT be blocked by connection failures

I will change this setting to block after 5 connection failures in a row

And right after confirming this change, a new dialog window appears suggested to also change the “Account lockout period” and “Reset account lockout counter after” settings to 30 minutes (this value can be changed later). This means that after 30 minutes, the account will be automatically unlocked and the connection failure counter will be reset.

From now on, after 5 failures (I set this value) during the login process, this SQL Login will be automatically blocked for 30 minutes (this time is parameterizable too).

If you keep getting the password wrong, the default message for incorrect password will be shown, even if the user is already locked out:

If the correct password is entered and the user is locked out, this error message will be displayed:

To check blocked SQL logins, you can use this query:

Result:

You can also see if this option is active through the SSMS interface by opening the SQL Login properties:

It is worth remembering that there is a configurable time where the user will be automatically unlocked after N minutes.