Setting old and new password complexity checks

**Table 2-7: Old and new password complexity checks**
Password checks and policies for Adaptive Server authentication	Existing configuration parameters specified using sp_configure	New password complexity options specified using sp_passwordpolicy	Existing 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

You can set the password complexity options at:

The login level using sp_addlogin or sp_modifylogin.
The 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, it is important to know the order of precedence in which the password options will be applied.

When applying password options: Adaptive Server looks first at the existing per-login parameters, then it looks at the new password complexity options, and then it looks at the existing global password options.

Example 1 If you enter:

sp_addlogin @login_name = 'johnd', 
      @passwd = 'complex_password', 
      @minpwdlen = 6

you have set the minimum password length for johnd to 6.

If you then enter the following existing global options for login johnd:

sp_configure 'minimum password length', 8
sp_configure 'check password for digit', 'true'
sp_passwordpolicy 'set', 'min digits in password', 2

you have created two minimum password length requirements for login johnd, and you have also set restrictions about digits in the password.

If you then try to create a password for login johnd as follows:

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 these checks have been performed in the designated sequence, and the new password for login johnd passes these checks, the new password is successfully created.

Example 2 If, for the same login, you enter:

sp_password @caller_password = 'old_complex_password',
@new_password = 'abcd', @login_name = '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. The check fails, and Adaptive Server prints an error message. Once one password complexity check fails, no additional options are checked.

Example 3 If you attempt to create a new login with the following password configuration options:

sp_addlogin @login_name = 'johnd', @passwd = 'complex_password', @minpwdlen = 4

this sets the minimum password length for login johnd to 4. This is a per-login, existing option. If you then add:

sp_passwordpolicy 'set', 'min digits in password', 1

you have created a global requirement that the minimum number of digits for a password must be 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.