If you upgrade to an Adaptive Server 15.0.2 or later, then downgrade to an earlier version, use sp_downgrade to retain and use the password encryption functionality from the 15.0.2 and later server. By default, Adaptive Server lets you downgrade passwords after an upgrade, until you end the password downgrade period.
Running sp_downgrade, shutting
down the server, then restarting the same version of Adaptive Server
from which you downgraded removes the changes made by sp_downgrade.
You must re-run sp_downgrade to redo
the changes. See the Installation Guide for
information about running sp_downgrade.
Adaptive Server requires additional space in the master database, and transaction log. Use alter database to add additional space to the master database, and transaction log.
Encryption algorithms and password policies:
Increase the space required for syslogins by about 30%.
Increase the maximum row length by 135 bytes per login account.
Decrease the ratio of rows per page from about 16 rows per 2K page to 12 rows per 2K page between Adaptive Server versions 15.0.1 and 15.0.2. There is a period of time during the downgrade 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 Adaptive Server 15.0.1 has 1,000 login accounts, and the data fits into 59 pages, the same number of login accounts may require approximately 19 additional pages in Adaptive Server 15.0.2 on a new master database, or 33 additional pages if you upgraded from 15.0.1 (with allow password downgrade set to 1).
The transaction log requires additional space for the updated password column. When users first log in, Adaptive Server requires about 829 2K pages per 1,000 logins, and about 343 pages per 1,000 logins for password changes users make during the upgrade and downgrade. To ensure there is sufficient log space, verify that there is approximately one 2K page of free log space per login before starting the password upgrade or downgrade, and when users first login to Adaptive Server version 15.0.2 and later.
Adaptive Server supports downgrading from version 15.0.2 or later to version 15.0 or 15.0.1. If you are downgrading to an earlier version of Adaptive Server, you may need to perform additional actions.
If allow password downgrade is 0 or NULL, or if a password has been stored in syslogins with only the SHA-256 algorithm, use sp_displaylogin on login accounts to determine which algorithm is used, or sp_downgrade "prepare" to determine which accounts are reset.
The prepare option reports whether the server is ready to be downgraded. If the prepare option fails, it reports errors that must be fixed. If a downgrade is performed on the server before the errors are fixed, the downgrade fails. For login passwords, prepare reports which passwords are reset during the downgrade.
Run sp_downgrade "prepare" to verify whether you should run sp_downgrade:
sp_downgrade 'prepare','15.0.1',1
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)
sp_droplogin 'probe'
If the login has user entries in databases, from the master database, drop users from databases, and then drop the login:
use master sp_dropuser 'probe'
The probe login is re-created when you run installmaster on the downgraded server.
Before executing sp_downgrade, Sybase recommends that you drop statistics for syslogins, and syssrvroles. Doing this avoids invalid column information, such as the length of password column, in sysstatistics from being recorded during the downgrade.
To drop statistics for syslogins, and syssrvroles, enter:
delete statistics master..syslogins delete statistics master..syssrvroles
In this example, the execution of sp_downgrade locks, and resets the login password for user103. The random password generated by Adaptive Server is shown only to the client who executes sp_downgrade. The administrator can redirect this output to a file so that these passwords are retained, or the administrator can manually reset them once the downgrade is complete, and the server is restarted.
sp_downgrade 'downgrade','15.0.1',1
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 is 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)
Additional messages appear in the error log to identify steps that occurred during sp_downgrade:
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.
sp_downgrade makes catalog changes, and modifies password data. The server must be in single user mode to successfully execute sp_downgrade. To start the server in single user mode, and to allow only the System Administrator to log in, use the -m command line option to start the server.
After running sp_downgrade, shut down the 15.0.2 server to avoid new logins or other actions that may modify data or system catalogs. If you restart Adaptive Server at version 15.0.2 after running sp_downgrade, the earlier version shuts down and you are again upgraded to the version 15.0.2 or later level.
