Password checks and policies for Adaptive Server authentication |
Configuration parameters specified using sp_configure |
Password complexity options specified using sp_passwordpolicy |
Per-login overrides specified using alter login |
---|---|---|---|
Password expiration |
system-wide password expiration |
system-wide password expiration |
password expiration |
Digits in password |
check password for digit |
min digits in password |
N/A |
Alphabetic characters in password |
N/A |
min alpha in password |
N/A |
Password length |
minimum password length |
minimum password length |
min passwd length |
Failed logins lockout |
maximum failed logins |
maximum failed logins |
max failed attempts |
Disallow simple passwords |
N/A |
disallow simple passwords |
N/A |
Special characters in password |
N/A |
min special char in password |
N/A |
Uppercase letters in password |
N/A |
min upper char in password |
N/A |
Lowercase letters in password |
N/A |
min lower char in password |
N/A |
Password expiration warning interval |
N/A |
password exp warn interval |
N/A |
Resetting your password at first login |
N/A |
expire login |
N/A |
Custom password complexity checks |
N/A |
N/A |
N/A |
Set the password complexity options at the:
Login level using create login or alter login.
Global level using the new sp_passwordpolicy or sp_configure.
Because you can set password configuration options on a global and per-login basis, and using old and new parameters, the order of precedence in which the password options is applied is important.
When applying password options, the order of precedence is:
Existing per-login parameters
Password complexity options
Existing global password options
Example 1 Creates a new login and sets the minimum password length for “johnd” to 6:
create login johnd with password complex_password min password length '6'
These global options for login “johnd” create two minimum password length requirements for login “johnd”, and sets restrictions about digits in the password:
sp_configure 'minimum password length', '8' sp_configure 'check password for digit', 'true' sp_passwordpolicy 'set', 'min digits in password', '2'
If you then try to alter the password for login “johnd”:
alter login johnd with password complex_password modify password 'abcd123'
Adaptive Server checks the password in the following order:
Per-login existing options check: minimum password length must be greater than 6. This is true and the check passes.
New options: minimum digits in password must be greater than 2. This is true and the check passes.
Existing global options: minimum password length specified here is not checked because there is already a per-login check for the login “johnd”.
The check password for digit option is redundant because it is already checked when the minimum number of digits is turned on and set to 2.
Once Adaptive Server checks the designated sequence, and the new password for login “johnd” passes these checks, the password is successfully change.
Example 2 If you enter the following for user “johnd”, Adaptive Server first checks the per-login existing options, and determines the minimum password length is set to 6, but that you have attempted to alter the password to use only 4 characters:
alter login johnd with password complex_password modify password abcd
The check fails, and Adaptive Server prints an error message. Once one password complexity check fails, no additional options are checked.
Example 3 Creates a new login with the following password configuration options
and sets the minimum password length for login johnd
to
4:
create login johnd with password complex_password min password length 4
This is a per-login, existing option. When you add the following, you have created a global requirement that the minimum number of digits for a password must be 1:
sp_passwordpolicy 'set', 'min digits in password', '1'
If you then attempt to alter the password for login johnd
as
follows:
alter login johnd with password complex_password modify password abcde
Adaptive Server performs the checks in the following order:
Per-login existing options check: the minimum password length of a new password is 4. The password “abcde” is greater than 4, so this check passes.
New global requirement check: the minimum digits in a password is set to 1, globally. This check fails.
Adaptive Server does not change the password and prints an error message.
To alter a password, all the checks must pass.