You can downgrade passwords when user password updates are retained in both the old and new encoding. Adaptive Server retains the old encoding to use after downgrade to an earlier release (for example, 15.0.1).
To end the period when password downgrade is allowed, execute:
sp_passwordpolicy set, "allow password downgrade", 0
Before executing this command, administrators should examine login accounts with sp_displaylogin to determine if the login account has been used, and whether the password is stored in the new SHA-256 encoding. If the password is not encoded with the new encoding, the account is locked by this action, and the password is reset with a generated password. To use the account again, administrator intervention is required to unlock the account and give the user a newly generated password.
The administrator may want to save the output from this command because it can contain information about locked login accounts and generated passwords for those accounts.
Adaptive Server takes the following actions when the password downgrade period ends:
The datetime when the password downgrade period ended is recorded in master.dbo.sysattributes.
The value of each password column in syslogins is rewritten to only use the new password on-disk structure.
The logins that have not transitioned to the new algorithm have the password reset to a new server-generated password in the new SHA-256 format, and the login is locked. The generated password is displayed only to the administrator executing the sp_passwordpolicy procedure above. The lock reason is set to 3 (“Login or role not transitioned to SHA-256”).
After the sp_passwordpolicy procedure completes, the following behavior is expected:
The authentication of logins is done using the new algorithm only.
Only the new password on disk structure for the password column is used.
Attempts to use the locked logins will fail authentication. In order to use the logins that were locked, the administrator must unlock the login with sp_locklogin and you must use the password generated by sp_passwordpolicy. Alternatively, the administrator may prefer to assign a new password instead of the generated password for locked login accounts.
This example prepares an upgraded server to use only SHA-256. Examine login accounts to determine which encryption is used by the account using sp_displaylogin.
1> sp_displaylogin login993 2> go Suid: 70 Loginame: login933 Fullname: Default Database: master Default Language: Auto Login Script: Configured Authorization: Locked: NO Date of Last Password Change: Apr 20 2007 2:55PM Password expiration interval: 0 Password expired: NO Minimum password length: 0 Maximum failed logins: 3 Current failed login attempts: Authenticate with: ANY Login Password Encryption: SYB-PROP Last login date: (return status = 0)
The value “SYB-PROP” from the line Login Password Encryption: SYB-PROP indicates that only the old, Sybase proprietary password encryption is used for this account. This login has not been used since upgrade to Adaptive Server version 15.0.2 and would be locked and password reset if the procedure “sp_passwordpolicy 'set', 'allow password downgrade', 0” is executed.
After the first login to the account after upgrading to Adaptive Server 15.0.2, the line would changed to show value “SYB-PROP, SHA-256” to show that both old and new encryption is used:
Login Password Encryption: SYB-PROP,SHA-256
This is the desired state for all active login accounts, so that executing “sp_passwordpolicy 'set', 'allow password downgrade', 0” does not lock and reset the password for accounts.
After the “sp_passwordpolicy 'set', 'allow password downgrade', 0” procedure is executed, only the new encryption is used, and the following line appears:
Login Password Encryption: SHA-256
Login accounts that show this value are fully transitioned to use the new, stronger, on-disk encryption algorithm.
This example executes “sp_passwordpolicy 'set', 'allow password downgrade', 0” when all accounts have transitioned to new algorithm
When all passwords have been changed to use the new algorithm, executing the command shows no accounts reset or locked.
1> sp_passwordpolicy 'set', 'allow password downgrade', 0 2> go
Old password encryption algorithm usage eliminated from 0 login accounts, changes are committed. (return status = 0)
This example executes “sp_passwordpolicy 'set', 'allow password downgrade', 0” with accounts that have not transitioned to new algorithm
In this example, 990 out of 1000 login accounts have transitioned to using the new SHA-256 algorithm, but 10 accounts were still using SYB-PROP algorithm:
1> sp_passwordpolicy 'set', 'allow password downgrade', 0 2> go
Old password encryption algorithm found for login name login1000, suid 3, ver1 =5, ver2 = 0, resetting password to EcJxKmMvOrDsC4 Old password encryption algorithm found for login name login999, suid 4, ver1 =5, ver2 = 0, resetting password to MdZcUaFpXkFtM1 Old password encryption algorithm found for login name login998, suid 5, ver1 =5, ver2 = 0, resetting password to ZePiZdSeMqBdE6 Old password encryption algorithm found for login name login997, suid 6, ver1 =5, ver2 = 0, resetting password to IfWpXvGlBgDgW7 Old password encryption algorithm found for login name login996, suid 7, ver1 =5, ver2 = 0, resetting password to JhDjYnGcXwObI8 Old password encryption algorithm found for login name login995, suid 8, ver1 =5, ver2 = 0, resetting password to QaXlRuJlCrFaE6 Old password encryption algorithm found for login name login994, suid 9, ver1 =5, ver2 = 0, resetting password to HlHcZdRrYcKyB2 Old password encryption algorithm found for login name login993, suid 10, ver1 =5, ver2 = 0, resetting password to UvMrXoVqKmZvU6 Old password encryption algorithm found for login name login992, suid 11, ver1 =5, ver2 = 0, resetting password to IxIwZqHxEePbX5 Old password encryption algorithm found for login name login991, suid 12, ver1 =5, ver2 = 0, resetting password to HxYrPyQbLzPmJ3 Old password encryption algorithm usage eliminated from 10 login accounts, changes are committed. (return status = 1)
The login name, suid, and generated
password are displayed to the administrator executing the procedure.
The output of the command shows all 10 accounts that have not transitioned
are reset (and locked).
