An interface that a user with sso_role can use to configure login and password policy options.
To specify, remove, and list new password complexity options:
sp_passwordpolicy {“set” | “clear” | “list”}, policy_option, option_value
To verify the password complexity options:
sp_passwordpolicy 'validate password options'
To generate asymmetric key pairs for network login password encryption:
sp_passwordpolicy "regenerate keypair"
To expire passwords:
sp_passwordpolicy "expire role passwords", "[rolename | wildcard]"
sp_passwordpolicy "expire login passwords", "[login_name | wildcard]"
sp_passwordpolicy "expire stale role passwords", "datetime"
sp_passwordpolicy "expire stale login passwords", "datetime"
sets a value to an option. When using set, you must specify the policy_option.
deletes the row for the option specified in the master.dbo.sysattributes table. If there is no policy option specified, clear deletes all the option rows in the sysattributes table. When using clear, you must specify the policy_option.
lists the values of the options specified. When using list, you must specify the policy_option.
is the option parameter for set, clear, and list, with option_value being the their values:
Option |
Description |
|---|---|
allow password downgrade |
Ends the password downgrade period. During the password downgrade period, passwords are stored in syslogins in both old and new encodings to allow user passwords to retained if the server is downgraded, for example, to Adaptive Server 15.0.2. |
disallow simple passwords |
Value of 1 turns this option on, and a value of 0 turns it off. |
min digits in password |
Indicates the minimum number of digits to be allowed in a password. |
min alpha in password |
Indicates the minimum number of alphabetic characters in a password. |
min special char in password |
Indicates the minimum number of special characters allowed in a password. |
min upper char in password |
Indicates the minimum number of upper-case characters allowed in a password |
min lower char in password |
Indicates the minimum number of lower case characters allowed in a password. |
systemwide password expiration |
Indicates the system-wide password expiration in days. |
password exp warn interval |
Indicates the password expiration warning interval in days. |
minimum password length |
Sets the minimum length of the password. |
maximum failed logins |
Sets the maximum number of failed logins allowed in a session before the account is locked. |
expire login |
Specifies that a login status changes to expired status when you create or reset your login. You are required to change your password on your first login. |
enables or disables code in Adaptive Server authentication that records the timestamp when each login occurs.The first parameter “set” sets the value of this attribute.The parameter “list” displays the current value of the attribute, and the parameter "clear" deletes the row from sysattributes. On upgrade or new installation, this attribute does not exist in sysattributes. The login timestamp occurs when the attribute row does not exist or has a value of 1. The login timestamp is not maintained if the attribute value is 0.
expires login passwords, all logins or logins matching a wild card pattern. The column status in master database catalog syslogins is updated with a status bit LOGIN_EXPIRED (0x4) to indicate the password is expired.
expires the password of a role, all roles or roles matching a wild-card pattern. The column status in master database catalog syssrvroles is updated with a status bit ROLE_EXPIRED (0x4) to indicate the password is expired:
expires login passwords have not been changed after a datetime specified. The column status in master database catalog syslogins is updated with a status bit LOGIN_EXPIRED (0x0004) to indicate that the password is expired. See “Entering Date and Time Data” in Adaptive Server 15.0 Reference Manual: Building Blocks, Chapter 1, “System and User Defined Datatypes” for an explanation of how datetime values are entered.
expires role passwords have not been changed after a datetime specified. The column status in master database catalog syssrvroles is updated with a status bit ROLE_EXPIRED (0x4) to indicate the password is expired.
generates the asymmetric key pairs to be used for network login password encryption.There is no catalog update for this option; the actions occur only in memory fields.
reports errors or inconsistencies in the password complexity option values set, including length and expiration. The result is reported in a tabular format, with each row representing a validation step, the result of the step, and the validation test performed. The result is one of Pass, Fail, or Not Applicable (NA). If any validation test fails, the return status is set to 1.
Sets a password expiration warning interval to seven days before the password expires:
sp_passwordpolicy 'set', 'password exp warn interval', 7
Lists the option for minimum number of special characters:
sp_passwordpolicy 'list', 'min special char in password'
Resets disallow simple passwords to the default value:
sp_passwordpolicy 'clear', 'disallow simple passwords'
These examples demonstrate using validate password options. These outputs have been reformatted for clarity, and do not resemble the output you see on your screen if you execute this procedure
These password complexity options and their values are stored in the server:
minimum password length: 8 min alpha in password: 2 min digits in password: 2 min upper char in password: 2 min lower char in password: 2
To validate these options, enter:
sp_passwordpolicy 'validate password options' Validation Step Pass/Fail/NA Validation Test --------------- ------------ ----------------------------- min alpha in password Fail 'min alpha in password' > = 'min upper char in password + 'min lower char in password' minimum password length - 1 Pass 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length - 2 Pass 'minimum password length' > = 'min digits in password' + min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length - 1 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' maximum password length – 2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 1)
There is one failure: The sum of min upper char in password + min lower char in password is greater than the value of min alpha in password, so the validation step min alpha in password fails.
Validating the following options stored in Adaptive Server:
minimum password length: 8 min digits in password: 2 min special char in password: 2 min alpha in password: 6 min upper char in password: 3 min lower char in password: 3
sp_passwordpolicy 'validate password options' Validation Step Pass/Fail/NA Validation Test --------------- ------------ ----------------------------- min alpha in password Pass 'min alpha in password' > = 'min upper char in password' + 'min lower char in password' minimum password length-1 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length-2 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length-1 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' maximum password length–2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 1)
There are two failures in step 2 and step 3.The sum of min digits in password, min special char in password and min alpha in password is greater than the value of minimum password length, so the validation step minimum password length -1 fails. The sum of min digits in password, min special char in password, min upper char in password and min lower char in password is greater than the value of minimum password length, so the validation step minimum password length -2 fails.
The following examples illlustrate the option 'validate password options'. The outputs have been reformatted for clarity, and do not resemble the output you see on your screed when you execute this procedure.
These password complexity options and their values are stored in the server:
minimum password length: 8 min alpha in password: 2 min digits in password: 2 min upper char in password: 2 min lower char in password: 2
To validate these options, enter:
sp_passwordpolicy 'validate password options'
Validation Step Pass/Fail/NA Validation Test --------------- ------------ -------------------------- min alpha in password Fail 'min alpha in password' > = 'min upper char in password + 'min lower char in password' minimum password length - 1 Pass 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length - 2 Pass 'minimum password length' > = 'min digits in password' + min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length - 1 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' maximum password length – 2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 1)
There is one failure: the sum of min upper char in password + min lower char in password is greater than the value of min alpha in password, so the validation step min alpha in password fails.
Validating the following options stored in Adaptive Server:
minimum password length: 8 min digits in password: 2 min special char in password: 2 min alpha in password: 6 min upper char in password: 3 min lower char in password: 3
sp_passwordpolicy 'validate password options'
Validation Step Pass/Fail/NA Validation Test --------------- ------------- ------------------------------ min alpha in password Pass 'min alpha in password' > = 'min upper char in password' + 'min lower char in password' minimum password length-1 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length-2 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length-1 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' maximum password length–2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 1)
There are two failures in step 2 and step 3.
The sum of min digits in password, min special char in password and min alpha in password is greater than the value of minimum password length, so the validation step minimum password length -1 fails. The sum of min digits in password, min special char in password, min upper char in password and min lower char in password is greater than the value of minimum password length, so the validation step minimum password length -2 fails.
Validating the following options stored in Adaptive Server:
minimum password length: 8 min digits in password: 11 min special char in password: 11 min alpha in password: 11 min upper char in password: 1 min lower char in password: 1
sp_passwordpolicy 'validate password options'
Validation Step Pass/Fail/NA Validation Test --------------- ------------ ----------------------------- min alpha in password Pass 'min alpha in password' > = 'min upper char in password' + 'min lower char in password' minimum password length-1 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length-2 Fail 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length-1 Fail 'max password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' maximum password length–2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 1)
There are 3 failures, including a serious one, a failure in a test for maximum password length, where the sum of the required password components is greater than the maximum password allowed.
Validating the following options stored in Adaptive Server:
minimum password length: 8 min digits in password: 2 min special char in password: 1 min alpha in password: 4 min upper char in password: 0 min lower char in password: 0
sp_passwordpolicy 'validate password options'
Validation Step Pass/Fail/NA Validation Test --------------- ------------ ------------------------------min alpha in password Pass 'min alpha in password' > = 'min upper char in password' + 'min lower char in password' minimum password length-1 Pass 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min alpha in password' minimum password length-2 Pass 'minimum password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' maximum password length-1 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min 'min alpha in password' maximum password length–2 Pass 'max password length' > = 'min digits in password' + 'min special char in password' + 'min upper char in password' + 'min lower char in password' password exp warn interval NA 'password exp warn interval' < = 'systemwide password expiration' (6 rows affected) (return status = 0)
There are no failures with these settings. This reports all 5 rows returned, and a return status of 0.
sp_passwordpolicy information is stored in the master.dbo.sysattributes table.
The set and clear commands in sp_passwordpolicy are audited through audit event 115, “Password Administration.”
A audit option “password” audits these actions.
sp_passwordpolicy 'set', 'option_name', 'option_value'
sp_passwordpolicy 'clear', 'option_name'
sp_passwordpolicy 'expire login passwords'
sp_passwordpolicy 'expire stale login passwords'
sp_passwordpolicy 'regenerate keypair'
sp_passwordpolicy 'expire role passwords'
sp_passwordpolicy 'expire stale role passwords'
