Setting password complexity checks

**Table 14-10: 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 sp_modifylogin
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_logins
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 sp_addlogin or sp_modifylogin.
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 This sets the minimum password length for “johnd” to 6:

sp_addlogin @login_name = 'johnd', 
      @passwd = 'complex_password', 
      @minpwdlen = 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 create a password for login “johnd”:

sp_password @caller_password = 'old_complex_password',
@new_password = 'abcd123', @login_name = 'johnd'

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 new password is successfully created.

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 create a password with only 4 characters:

sp_password @caller_password = 'old_complex_password',
@new_password = 'abcd', @login_name = 'johnd'

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 This example creates a new login with the following password configuration options and sets the minimum password length for login johnd to 4:

sp_addlogin @login_name = 'johnd', @passwd = 'complex_password', @minpwdlen = 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 create the password for login johnd as follows:

sp_password @caller_password = 'old_complex_password',
@ new_password = 'abcde', @login_name = 'johnd'

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 create a new password and prints an error message.

To create a new password, all the checks must pass.