sp_passwordpolicy

Allows a user with sso_role to configure login and password policy options.

Syntax

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"

Parameters

set – sets a value to an option. When using set, you must specify the policy_option.
clear – 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.
list – lists the values of the options specified. When using list, you must specify the policy_option.
policy_option, option_value – 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 SAP ASE 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 SAP ASE 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
  - “clear” deletes the row from sysattributes. Although “clear” deletes the row from sysattributes, the last setting is still effective until you restart the SAP ASE server, or when “set” sets the new value.
- expire login – Specifies that when new logins are created or when the SSO changes login passwords, the passwords for those logins are marked as expired, thus forcing those users to change their password when they first log in.
- 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 – the SAP ASE server regenerates a new RSA key pair immediately if that time has elapsed; otherwise the SAP ASE server waits until that specified time.
  - Do not specify datetime of first generation – the SAP ASE 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 the SAP ASE 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.
  
  Note: 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 the SAP ASE 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.
- unique keypair per session – Specifies the configurations you can set for generating a unique key pair for every user:
  - 1 – specifies to generate a new key pair for every user connection.
  - 0 – specifies that all connections share the same RSA key pair.
  Note: If sp_configure "net password encryption reqd" is configured to "3", this password policy option is ignored because a unique keypair per session is not needed to secure the password.
“expire login passwords", "[login_name | wildcard]" – 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.
"expire role passwords", "[rolename | wildcard]" – 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:
"expire stale login passwords", "datetime" – 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 Reference Manual: Building Blocks for an explanation of how datetime values are entered.
"expire stale role passwords", "datetime" – 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.
"regenerate keypair" – 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.
'validate password options' – 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.

Examples

Example 1 – Sets a password expiration warning interval to seven days before the password expires:
```
sp_passwordpolicy 'set', 
      'password exp warn interval', '7'
```
Example 2 – Lists the option for minimum number of special characters:
```
sp_passwordpolicy 'list', 
      'min special char in password'
```
Example 3 – Resets disallow simple passwords to the default value:
```
sp_passwordpolicy 'clear', 'disallow simple passwords'
```

Example 4 – 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.

Example 5 – 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"
```
Example 6 – Sets how long the SAP ASE server should wait before trying to regenerate the key-pair after a failed attempt:
```
sp_passwordpolicy 'set', 'keypair error retry wait', '10'
```
Example 7 – Sets number of times the SAP ASE server should attempt to regenerate the key-pair after a failure to 5:
```
sp_passwordpolicy 'set', 'keypair error retry count', '5'
```

Example 8 – 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)

Example 9 – Validating the following options stored in the SAP ASE 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.

Example 10 – Illlustrates the option 'validate password options'. Output has been reformatted for clarity, and does not resemble the output you see on your screen 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)

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 the SAP ASE 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 the SAP ASE 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 the SAP ASE 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.

Usage

sp_passwordpolicy information is stored in the master.dbo.sysattributes table.

Once the SAP ASE 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 the SAP ASE 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.

Permissions

The permission checks for sp_passwordpolicy differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage security configuration` privilege.
Disabled	With granular permissions disabled, you must be a user with sso_role.

Auditing

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.