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"
To display a brief description of all commands, options, and their values:
sp_passwordpolicy "help"
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.
string or (varchar). Is the option parameter for set, clear, and list, with option_value being the their values:
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.
enable last login updates – enables or disables code in Adaptive Server authentication that records the timestamp when each login occurs.The parameter “set” sets the value of this attribute. “list” displays the current value of the attribute, and "clear" deletes the row from sysattributes. After upgrading or in a 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.
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.
keypair regeneration period – indicates the regenerating period of the RSA key pair. Its option values are { ([keypair regeneration frequency], datetime of first generation) | (keypair regeneration frequency, [datetime of first generation]) }
keypair regeneration frequency – is the frequency of regeneration of an RSA key pair. The valid range of values (in hours) is from 1 to 8,760. The default value is NULL, in which case a key pair is regenerated every 24 hours. It specifies the duration’s format specifier, using:
'T*M' – indicates duration in minutes, replacing the asterisk (*) with a numeric value, such as “T2M” for two minutes.
'H' – indicates duration in hours.
'D' – indicates duration in days. This is the default if you do not specify another format.
'W' – indicates duration in weeks.
'M' – indicates duration in months.
'Y' – indicates duration in years.
datetime of first generation – is the date and time of when the key-pair is first generated. If you specify only the time for the value of datetime of first generation, RSA key pair regeneration is scheduled for that time of day in the next 24-hour period. If you:
Specify datetime of first generation – Adaptive Server regenerates a new RSA key pair immediately if that time has elapsed; otherwise Adaptive Server waits until that specified time.
Do not specify datetime of first generation – Adaptive Server regenerates a new RSA key pair at a time that is obtained by adding keypair regeneration period to the time when the most recent RSA key pair was generated, if this calculated time is not elapsed; otherwise Adaptive Server regenerates a new RSA key pair immediately.
Subsequent generations of key pairs occur based on when the most recent key pair was generated and the value of keypair regeneration period.
You cannot simultaneously set the value of keypair
regeneration frequency and datetime
of first generation to NULL.
keypair error retry [wait | count] – specifies the various configurations you can set for regenerating a key pair after a failed attempt:
wait – specifies the amount of time to wait after a failure before regenerating the keypair.
count – specifies how many times you want Adaptive Server to attempt to regenerate a key pair after a failure.
maximum failed logins – indicates the maximum number of failed logins allowed in a session before the account is locked.
min alpha in password – indicates the minimum number of alphabetic characters in a password.
min digits in password – indicates the minimum number of digits to be allowed in a password.
min lower char in password – indicates the minimum number of lower case characters allowed 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 uppercase characters allowed in a password.
minimum password length – indicates the minimum length of the password.
password exp warn interval – indicates the password expiration warning interval in days.
systemwide password expiration – indicates the system-wide password expiration in days.
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.
Sets the HouseKeeper task to automatically regenerate a key pair every two hours, starting on August 15, 2007 at 12:01 a.m.:
sp_passwordpolicy "set", "keypair regeneration period", "2H", "Aug 15 2007 12:01 AM"
Sets how long Adaptive Server should wait before trying to regenerate the key-pair after a failed attempt:
sp_passwordpolicy 'set', 'keypair error retry wait', '10'
Sets number of times Adaptive Server should attempt to regenerate the key-pair after a failure to 5:
sp_passwordpolicy 'set', 'keypair error retry count', '5'
Displays brief description about all commands, options and their values:
sp_passwordpolicy "help" go
sp_ passwordpolicy Usage: sp_passwordpolicy 'help'
sp_ passwordpolicy Usage: sp_passwordpolicy command [, option1 [, option2 [, option3]]]
sp_passwordpolicy commands:
sp_passwordpolicy 'set',
{'enable last login updates' | 'disallow simple passwords' |
'min digits in password' | 'min alpha in password' |
'min special char in password' | 'min upper char in password' |
'min lower char in password' | 'password exp warn interval' |
'systemwide password expiration' | 'minimum password length' |
'maximum failed logins' | 'expire login' |
'allow password downgrade' | 'keypair error retry wait' |
'keypair error retry count'},
'value'
sp_passwordpolicy 'set', 'keypair regeneration period',
{'regeneration_period' |
null, 'datetime' |
'regeneration_period', 'datetime'}
sp_passwordpolicy 'list',
['enable last login updates' | 'disallow simple passwords' |
'min digits in password' | 'min alpha in password' |
'min special char in password' | 'min upper char in password' |
'min lower char in password' | 'password exp warn interval' |
'systemwide password expiration' | 'minimum password length' |
'maximum failed logins' | 'expire login' |
'allow password downgrade' |
'keypair error retry wait' | 'keypair error retry count' |
'keypair regeneration period']
sp_passwordpolicy 'clear',
{'enable last login updates' | 'disallow simple passwords' |
'min digits in password' | 'min alpha in password' |
'min special char in password' | 'min upper char in password' |
'min lower char in password' | 'password exp warn interval' |
'systemwide password expiration' | 'minimum password length' |
'maximum failed logins' | 'expire login' |
'keypair error retry wait' |
'keypair error retry count' | 'keypair regeneration period'}
sp_passwordpolicy 'expire login passwords'[, '{loginame | wildcard}']
sp_passwordpolicy 'expire role passwords'[, '{rolename | wildcard}']
sp_passwordpolicy 'expire stale login passwords', 'datetime'
sp_passwordpolicy 'expire stale role passwords', 'datetime'
sp_passwordpolicy 'regenerate keypair'[, 'datetime']
sp_passwordpolicy 'validate password options'
(return status = 0)
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
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)
To validate these options, enter:
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 three 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.
Once Adaptive Server has regenerated a new RSA key pair, subsequent generations use a formula of the last time when RSA key pair was generated, combined with the value you specified for keypair regeneration frequency.
The value of keypair regeneration period is stored in master..sysattributes under a new password policy class.
A default value of NULL for the option indicates that this row does not exist in sysattributes and the key pair is generated on when Adaptive Server is restarted, and every 24 hours thereafter.
These two stored procedures do the same thing:
sp_passwordpolicy 'set', 'keypair regeneration period', NULL [, datetime of first generation]
sp_passwordpolicy 'regenerate keypair' [, datetime of first generation]
These global variable use the information from keypair regeneration period:
@@lastkpgendate – reflects the datetime of when the last key pair was generated.
@@nextkpgendate – to reflect when the key pair is next generated.
These login password complexity checks are extended to role passwords:
disallow simple passwords
min digits in password
min alpha in password
min special char in password
min upper char in password
min lower char in password
systemwide password expiration
password exp warn interval
minimum password length
maximum failed logins
expire login
The Adaptive Server high-availability functionality synchronizes these password policy options between primary and secondary servers:
disallow simple passwords
min digits in password
min alpha in password
min special char in password
min upper char in password
min lower char in password
systemwide password expiration
password exp warn interval
minimum password length
maximum failed login
expire login
keypair regeneration period
keypair error retry wait
keypair error retry count
Adaptive Server uses a “password policy” quorum attribute to check the inconsistency of any of those values on both the primary and secondary servers, except keypair regeneration period, keypair error retry wait, and keypair error retry count. A high-availability advisory check succeeds when all those value are the same on both servers, and fail when the values differ. For example:
sp_companion "MONEY1", do_advisory, 'all' go
Attribute Name Attrib Type Local Value Remote Value Advisory -------------- ----------- ----------- ----------- ------ expire login password po 1 0 2 maximum failed password po 3 5 2 min alpha in pa assword po 10 12 2
A value of 2 set in the advisory column of the output indicates that the user cannot proceed with the cluster operation unless the values on both the companions match.
The output of sp_companion do_advisory also indicates the inconsistency in any of the particular password policy checks on both servers.
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'
The “password” audit option also audits the administration of RSA key pair regeneration period that generates the AUD_EVT_PASSWORD_ADMIN(115) auditing event.
