Click on the banner to learn about and purchase my database training on Azure

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

This post is the 20 part of 20 in the series. Security and Audit
Reading Time: 10 minutes

Speak guys!
In this post I'd like to talk about the SQL Server Security part focused on passwords: Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after N attempts.

Video with the Article Summary

If you liked this video and want to delve deeper into the data security part, be sure to check out my course Security in SQL Server

Password Policies (CHECK_POLICY)

Click here to view this content
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.

Important: To learn more about brute force attacks, visit the article SQL Server - How to Avoid Brute Force Attacks on Your Database

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 (%).
When CHECK_POLICY is changed to ON, the following behavior occurs:

  • CHECK_EXPIRATION is also set to ON unless explicitly set to OFF
  • Password history is initialized with the hash value of the current password
  • Account lockout duration, account lockout limit, and account lockout counter after zero are also enabled

When CHECK_POLICY is changed to OFF, the following behavior occurs:

  • CHECK_EXPIRATION will also be set to OFF
  • Password history will be cleared
  • The value of lockout_time is reset

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:

Msg 15116, Level 16, State 1, Line 15
Password validation failed. The password does not meet the operating system policy requirements because it is too short.
Observation: To learn more about these “Password Policy” settings, click this link here.

Password Expiration (CHECK_EXPIRATION)

Click here to view this content
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.

Observation: To learn more about these “Password Policy” settings, click this link here.

Mandatory Password Change (MUST_CHANGE)

Click here to view this content
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:

Important: The MUST_CHANGE option can only be used if the CHECK_EXPIRATION and CHECK_POLICY options are set to ON.

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

Important: It is not possible to activate the MUST_CHANGE option in an existing login without changing the current password to another one in the same ALTER LOGIN command.

Login blocked after N attempts

Click here to view this content
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:

Login failed for user 'test_policy_password'. Reason: Password did not match that for the login provided.

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

Login failed for user 'test_politica_senha'.Reason: The account is currently locked out. The system administrator can unlock it.

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.

Observation: To learn more about these “Account Lockup Policy” settings, click this link here.

Local Security Policy (Without AD) or Group Policy Management (With AD)

If the machine is in a domain (most likely), these password complexity, auto-lock and other settings must be changed by the “Group Policy Management” utility so that these changes are made in Windows Active Directory (Windows AD) and then then they will be replicated to all servers:

If you only change the local Windows settings on the server, the next time the policies are updated, that change you made locally will be overwritten by the domain configuration.

You can use the “Local Security Policy” (secpool.msc) to see which is the current policy being applied on the server, as it will probably be the same policy on the domain.

To force an update of local Active Directory policies to the local computer, you can run the command “gpupdate /force” from the DOS prompt:

If your machine is NOT on a domain, then just change these settings in the “Local Security Policy” utility (secpool.msc), as I showed in the article.

And that's it folks!
A big hug and until next time!