Cuir in eagar

Comhroinn trí

Password policy

Applies to: SQL Server

SQL Server can use Windows password policy mechanisms. The password policy applies to a login that uses SQL Server authentication, and to a contained database user with password.

SQL Server can apply the same complexity and expiration policies used in Windows to passwords used inside SQL Server. This functionality depends on the NetValidatePasswordPolicy API.

Note

Azure SQL Database enforces password complexity. The password expiration and policy enforcement sections don't apply to Azure SQL Database. For information on password policy for Azure SQL Managed Instance, see our SQL Managed Instance FAQ.

Password complexity

Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced, new passwords must meet the following guidelines:

  • The password doesn't contain the account name of the user.

  • The password is at least eight characters long.

  • The password contains characters from three of the following four categories:

    • Latin uppercase letters (A through Z)
    • Latin lowercase letters (a through z)
    • Base 10 digits (0 through 9)
    • Nonalphanumeric characters such as: exclamation point (!), dollar sign ($), number sign (#), or percent (%).

Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.

Password expiration

Password expiration policies are used to manage the lifespan of a password. When SQL Server enforces password expiration policy, users are reminded to change old passwords, and accounts that have expired passwords are disabled.

Policy enforcement

The enforcement of password policy can be configured separately for each SQL Server login. Use ALTER LOGIN 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's 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.

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

    • CHECK_EXPIRATION is also set to OFF.
    • The password history is cleared.
    • The value of lockout_time is reset.

Some combinations of policy options aren't supported.

  • If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement fails.

  • If CHECK_POLICY is set to OFF, CHECK_EXPIRATION can't be set to ON. An ALTER LOGIN statement that has this combination of options fails.

  • Setting CHECK_POLICY = ON prevents the creation of passwords that are:

    • Null or empty
    • Same as name of computer or login
    • Any of the following: password, admin, administrator, sa, sysadmin

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).

Note

For SQL Server logins that have CHECK_POLICY enabled, if you run ALTER LOGIN and don't include OLD_PASSWORD in the command to change the password, then Enforce Password History is ignored. This is a by-design behavior to allow password resets, despite any previously used passwords. Other checks associated with CHECK_POLICY, including length and complexity are checked regardless of whether OLD_PASSWORD is used.

Check SQL user password policy information

You can review SQL user password policies and expiration dates in SQL Server using the following query. Although the following query also works in Azure SQL Database, only password complexity is enforced in Azure SQL Database.

SELECT name,
       is_policy_checked,
       is_expiration_checked,
       LOGINPROPERTY(name, 'IsMustChange') AS IsMustChange,
       LOGINPROPERTY(name, 'IsLocked') AS IsLocked,
       LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime,
       LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime,
       LOGINPROPERTY(name, 'IsExpired') AS IsExpired,
       LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount,
       LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime,
       LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength,
       modify_date
FROM sys.sql_logins;

Related content

  • Last updated on