To end the period when password downgrade is allowed, execute:
sp_passwordpolicy set, "allow password downgrade", 0
Before executing this command, examine login accounts with sp_displaylogin to determine if the login account has been used, and whether the password is stored in SHA-256 encoding. If is not, the account is automatically locked and reset with a generated password. To use the account again, you must unlock the account and give the user a newly generated password.
You may want to save the output from this command because it can contain information about locked login accounts and generated passwords for those accounts.
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 use only 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 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:
Login authentication uses only SHA-256.
Only the new password on-disk structure for the password column is used.
Attempts to use the locked logins fail authentication. To use the locked logins, you must unlock the login with sp_locklogin and the user must use the password generated by sp_passwordpolicy. Alternatively, you 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 Sybase-proprietary encryption is used for this account. This login has not been used before they upgrade to Adaptive Server version 15.0.2, and will be locked, and its password reset if 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 changes 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 you execute sp_passwordpolicy 'set', 'allow password downgrade', 0, only SHA-256 encryption is used, and you see:
Login Password Encryption: SHA-256
Login accounts that show this value are now using the stronger, on-disk encryption algorithm.
When all passwords have been changed to use the new algorithm, reexecuting sp_passwordpolicy 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)
In this example, 990 out of 1000 login accounts have transitioned to the SHA-256 algorithm, but 10 accounts are 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 appear to the administrator executing the procedure. The
output of the command shows all 10 accounts that have not transitioned
are reset (and locked).
