You can upgrade to Adaptive Server release 15.0.2 and later to use new password encryption and retain the ability to downgrade to an earlier release. This password downgrade period is introduced only for upgrade in the event that problems arise after upgrade and the administrator chooses to downgrade back to Adaptive Server 15.0.1, for example.The ability to downgrade passwords is enabled by default upon upgrade and remains in effect until an administrator chooses to end this by disabling it.While enabled, only the new functionality is lost by downgrade to Adaptive Server 15.0.1, the password changes made by users are not lost. All changes take place in master database; no user databases are affected.
If you run sp_downgrade then
shutdown the server and for some reason reboot the 15.0.2 server
all the downgrade changes are undone. In this case you must run sp_downgrade again.
The master database contains the syslogins and syssrvroles tables. When the new encryption algorithms and password policies are enabled on Adaptive Server, additional disk space in master database and transaction log is needed.The database administrator should use the alter database command to add sufficient space to the master database and transaction log to handle the additional disk requirements for the user population of the site.
The space for syslogins in master database is estimated to increase by about 30% for the same number of users. The maximum row length is increased 135 bytes per login account (row in syslogins). The ratio of rows per page has decreased from about 16 rows per 2k page to 12 rows per 2k page between Adaptive Server versions 15.0.1 and 15.0.2. During the period when the value for “allow password downgrade” is 1, when both old and new password encryption algorithms are used, the ratio further decreases to about 10 rows per 2K page.
For example, if a server has 1,000 login accounts in Adaptive Server 15.0.1 and data pages fit into 59 pages, it may require about 19 additional pages in Adaptive Server 15.0.2 on a new master database or 33 pages additional pages if upgraded from 15.0.1 and the value of “allow password downgrade” is 1.
Additional space in transaction log is required for the updated password column at first login (829 2K pages per 1,000 logins), for password changes made by users and during upgrade and downgrade actions (about 343 pages per 1,000 logins). To ensure that sufficient log space is available, verify that there is about one page (2K page) of free log space per login available before password upgrade or downgrade actions are taken, and while users make their first login to Adaptive Server 15.0.2.
On bootstrap of a new master database, the value for “allow password downgrade” is NULL.
This value is different for a bootstrap of master databases
than for an upgraded master database.
The following actions are taken during an upgrade from an earlier Adaptive Server release (not in a new master database):
Schema changes to syslogins and syssrvroles.
Add a new row to sysattributes for “allow password downgrade,” set with value 1 (default). The ability to downgrade passwords is kept on upgraded master databases until the stored procedure “sp_passwordpolicy set, 'allow password downgrade', 0” is called to disable it. The system attribute “allow password downgrade” is enabled by default upon upgrade.
Add a new row to sysattributes for “enable last login updates,” set with value 1 (default).The update of syslogins column lastlogindate is controlled by this attribute value.The administrator can disable this behavior by calling stored procedure “sp_passwordpolicy set, 'enable last login updates', 0.”
Downgrade to Adaptive Server release 15.0 or 15.0.1 is supported, and can happen without the administrator taking action for each login account.
If “allow password downgrade” is 0 or NULL, or a password has otherwise been stored in syslogins with only the new SHA-256 algorithm, then the password is reset and the account is locked during downgrade. This will require administrator action before the account may be used again. Use sp_displaylogin on login accounts to determine what algorithm is used or sp_downgrade "prepare" to determine what accounts will be reset.
To know what actions will be taken and to verify that sp_downgrade may run successfully, you can perform a dry run using the “prepare” option, such as:
1> sp_downgrade 'prepare','15.0.1',1 2> go
Checking databases for downgrade readiness. There are no errors which involve encrypted columns. Allow password downgrade is set to 0. Login passwords may be reset, if old encryption version of password is not present. Warning: New password encryption algorithm found for login name user103, suid 103 . Password will be reset during the downgrade phase. sp_downgrade 'prepare' completed. (return status = 0)
In the above example, the login “user103” was found to have only the new password format that is not used on previous releases. If downgrade occurs, the password will be reset to a random password and the account locked.
This procedure runs through actions and report readiness for downgrade to occur. The downgrade action does not occur if prepare does not succeed. For login passwords, it will report which passwords will be reset.
Before executing sp_downgrade, Sybase recommends removing the login 'probe' from syslogins. To remove the login, connect to Adaptive Server as a system security officer or system administrator, and execute:
sp_droplogin 'probe' go
If the login has user entries in databases, use the following command to drop users from databases and then drop the login.
use master go sp_dropuser 'probe' go
The 'probe' login will be re-created when the installmaster script is run on the downgraded server.
Before executing sp_downgrade, Sybase recommends that you drop statistics for syslogins and syssrvroles. This avoids invalid column information, such as the length of password column, in sysstatistics after you perform a downgrade.
To drop statistics for syslogins and syssrvroles, enter:
1> delete statistics master..syslogins 2> delete statistics master..syssrvroles 3> go
The actions to downgrade password occur when stored procedure sp_downgrade is executed. For example:
1> sp_downgrade 'downgrade','15.0.1',1 2> go
Checking databases for downgrade readiness. There are no errors which involve encrypted columns. Allow password downgrade is set to 0. Login passwords may be reset, if old encryption version of password is not present. Warning: New password encryption algorithm found for login name user103, suid 103 . Password will be reset during the downgrade phase. Executing downgrade step 1 [sp_passwordpolicy 'downgrade'] for : - Database: master (dbid: 1) New password encryption algorithm found for login name user103, suid 103. Resetting password to 'ZdSuFpNkBxAbW9'. Total number of passwords reset during downgrade = 1 [ ... output from other downgrade steps ..] (return status = 0)
In the above example, the execution of sp_downgrade resulted in the locking and password reset of login user103. The random password generated by Adaptive Server is shown only to the client executing sp_downgrade. The administrator can redirect this output to a file so that these passwords are retained, or the administrator can reset them manually after rebooting on the downgraded master.
Additional messages appear in the errorlog to identify steps that occurred during sp_downgrade and any system errors that may occur. Example errorlog output for the example downgrade procedure follows:
00:00000:00006:2007/05/21 05:34:07.81 server Preparing ASE downgrade from 1502 to 1501. 00:00000:00006:2007/05/21 05:35:59.09 server Preparing ASE downgrade from 1502 to 1501. 00:00000:00006:2007/05/21 05:35:59.19 server Starting downgrading ASE. 00:00000:00006:2007/05/21 05:35:59.20 server Downgrade : Downgrading login passwords. 00:00000:00006:2007/05/21 05:35:59.22 server Downgrade : Starting password downgrade. 00:00000:00006:2007/05/21 05:35:59.23 server Downgrade : Removed sysattributes rows. 00:00000:00006:2007/05/21 05:35:59.23 server Downgrade : Updated 1 passwords. 00:00000:00006:2007/05/21 05:35:59.24 server Downgrade : Removed columns in syslogins - lastlogindate, crdate, locksuid, lockreason, lockdate are removed. 00:00000:00006:2007/05/21 05:35:59.26 server Downgrade : Truncated password lengths. 00:00000:00006:2007/05/21 05:35:59.28 server Downgrade : Successfully completed password downgrade. 00:00000:00006:2007/05/21 05:35:59.28 server Downgrade : Marking stored procedures to be recreated from text. 00:00000:00006:2007/05/21 05:36:03.69 server Downgrade : Dropping Sysoptions system table. 00:00000:00006:2007/05/21 05:36:03.81 server Downgrade : Setting master database minor upgrade version. 00:00000:00006:2007/05/21 05:36:03.83 server Downgrade : Setting user databases minor upgrade version. 00:00000:00006:2007/05/21 05:36:03.90 server ASE downgrade completed.
This procedure makes the catalog changes and modifies password data to allow reverting to Adaptive Server version 15.0.1. The server must be in single-user mode in order to successfully execute sp_downgrade. A dataserver started with “-m” command line option starts the server in single-user mode and allows only the sa to login.
After sp_downgrade is run, the only safe thing to do is shut down the server to avoid new logins or other actions that may modify data or system catalogs. If restarting Adaptive Server at version 15.0.2 after sp_downgrade has successfully executed and server is shutdown, internal upgrade actions are performed again and the changes to system catalogs are upgraded to version 15.0.2 level. If Adaptive Server 15.0.2 was restarted before booting the 15.0.x release to which you are reverting it is necessary to rerun sp_downgrade.
See details on other aspects of downgrade in the the “Downgrade” section of the Adaptive Server release bulletin.
