Downgrade procedure

If you have never configured enable encrypted columns in your server, you need not take any action before using an older version of Adaptive Server with 12.5.3a databases. One way to verify that you have never configured encrypted columns is to check that the system table sysencryptkeys does not exist in any database.

All databases should be backed up prior to the downgrade procedure.

Before downgrading a server that has been configured for encrypted columns, you must either drop or modify any tables with encrypted columns to remove encryption. You then run sp_encryption remove_catalog, which verifies that there are no encrypted columns in each database and then removes the system table sysencryptkeys. The new columns in syscolumns added for 12.5.3a are ignored by an older binary and need not be removed

To downgrade from a 12.5.3a server to an earlier version of 12.5.x:

1. If encrypted columns are not currently enabled, the System Security Officer executes:

   sp_configure ‘enable encrypted columns’,1
   

2. Use drop or alter to decrypt all tables with encrypted columns in all databases. The System Security Officer runs the following command in each database where encryption keys were created to list all encryption keys created in that database:

   sp_encryption help
   

   For each key listed, the System Security Officer runs the following to see a list of columns encrypted with a particular key:

   sp_encryption help, <keyname>, 'display_cols'
   

   For each encrypted column, one of the following steps must be performed:

   • alter table to decrypt the encrypted columns

   • alter table to drop the encrypted columns

   • drop the table containing the encrypted column

   • drop the encryption key

3. To guarantee that no other user can access Adaptive Server while a system table is removed, restart the server in single-user mode. See the Adaptive Server Enterprise Utility Guide.

4. A user with sso_role and sa_role must execute the following system stored procedure, which removes the sysencryptkeys catalog from each database:

   sp_encryption remove_catalog
   

   If a database is unavailable, the command prints an error and exits. If columns encrypted by any key in sysencryptkeys exist, the command does not drop sysencryptkeys, but prints an error or warning and continues with the next database.

   If sp_encryption is successful in removing sysencryptkeys, it also removes these rows from sysattributes in each database:

   • The record of the upgrade item that added sysencryptkeys

   • The system encryption password for the database

5. Drop the system stored procedure sp_encryption from the sybsystemprocs database.

6. Shut down the server. You can now use a 12.5.x Adaptive Server binary from a pre-12.5.3a version.

To reenable encrypted columns, when rolling forward from a downgraded 12.5.3a server back to 12.5.3a, configure enable encrypted columns. Upon restarting the 12.5.3a server, the sysencryptkeys system table is installed in each database.

Copyright © 2005. Sybase Inc. All rights reserved.

View this release bulletin as PDF