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.
