sp_password  sp_pciconfig

Chapter 1: System Procedures

sp_passwordpolicy

Description

An interface that a user with sso_role can use 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:

Valid policy_option values

Descriptions

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

  • “clear” deletes the row from sysattributes. Although “clear” deletes the row from sysattributes, the last setting is still effective until you restart Adaptive 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 – 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.

NoteYou 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.

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.

NoteIf sp_configure "net password encryption reqd" is configured to "3", this password policy option will be 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 Adaptive Server 15.0 Reference Manual: Building Blocks, Chapter 1, “System and User Defined Datatypes” 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

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 Adaptive 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 Adaptive 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 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.

Example 10

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.

Usage

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


Regenerating key pairs

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:


Login password complexity checks

These login password complexity checks are extended to role passwords:


High-availability and password policy options

The Adaptive Server high-availability functionality synchronizes these password policy options between primary and secondary servers:

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.

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be a user with manage security configuration privilege.

Granular permissions 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:

The “password” audit option also audits the administration of RSA key pair regeneration period that generates the AUD_EVT_PASSWORD_ADMIN(115) auditing event.




Copyright © 2012. Sybase Inc. All rights reserved. sp_pciconfig

View this document as PDF