Setting password complexity checks

**Table 3-2: Password complexity checks**
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

Examples

Example 1

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

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

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.