Downgrade instructions for Adaptive Server with encrypted columns

This section details the steps to be taken for downgrading a 12.5.4 server to earlier versions with regard to encrypted columns.

Back up all your databases and the $SYBASE release area prior to the 12.5.4 downgrade.

If you are returning to Adaptive Server version 12.5.3a, you do not need to take any steps to prepare your database for downgrade, whether or not you enabled encrypted columns in your 12.5.4 Adaptive Server.

If you are returning to Adaptive Server version 12.5.3 , returning to 12.5.3 ESD #7 is preferable, because 12.5.3 ESD #7 contains fixes for a number of error conditions.

Depending on whether enable encrypted columns has been configured in your server, you must take a different set of actions before using an older version of Adaptive Server with 12.5.4 databases.

One way to verify that you have never configured encrypted columns is to check whether the system table sysencryptkeys exists in any database. If the catalog does not exist, you have never configured encrypted columns in your server.

Use the following procedure when downgrading from a 12.5.4 Adaptive Server that has not been configured for encrypted columns.

1. Start the 12.5.4 server in single-user mode to guarantee that no other user can access Adaptive Server while you are going through the downgrade steps. See the Utility Guide for details on starting the server in single user mode.

2. A user with sso_role and sa_role must execute:

   sp_encryption remove_catalog
   

   This stored procedure removes the new columns added in syscolumns from each database. If sp_encryption is successful in removing the new columns from syscolumns, it also removes the record of the upgrade item from sysattributes in each database. If a database is unavailable, the command prints an error message and exits. You should bring the unavailable database online and execute sp_encryption remove_catalog again.

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

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

5. Copy the RUN_SERVER file to a 12.5.x release area and modify it to use the data server binary from the 12.5.x release area.

6. Restart the server using the modified RUN_SERVER file.

7. Run the 12.5.x version of installmaster to return system stored procedures to their original version.

Use the following procedure to downgrade from a 12.5.4 server to an earlier version of 12.5.x when encrypted columns has been configured.

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:

   1. alter table to decrypt the encrypted column

   2. alter table to drop the encrypted columns

   3. drop the table containing the encrypted column

   After the data encryption has been removed, 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 Utility Guide for details on starting the server in single-user mode.

4. To remove the sysencryptkeys catalog and the new columns in syscolumns from each database, a user with sso_role and sa_role must execute:

   sp_encryption remove_catalog
   

   If a database is unavailable, the command prints an error and exits. You should bring the unavailable database online and execute sp_encryption remove_catalog again.

   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 and the new columns from syscolumns, it also removes these rows from sysattributes in each database:

   • The record of the upgrade item that added sysencryptkeys

   • The record of the upgrade item that added the new columns in syscolumns

   • 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.4 release area.

7. Copy the RUN_SERVER file to a 12.5.x release area and modify it to use the data server binary from the 12.5.x release area.

8. Restart the server using the modified RUN_SERVER file.

9. Run the 12.5.x version of installmaster to return stored procedures to their original version.

To re-enable encrypted columns, when rolling forward from a downgraded 12.5.4 server back to 12.5.4, configure enable encrypted columns. Upon restarting the 12.5.4 server, the sysencryptkeys system table and the new columns in syscolumns are installed in each database.

When downgrading a server that has replication enabled on databases that contain encrypted data, you must do one of the following before you start the downgrade procedure:

1. Ensure that all replicated data in the primary database transaction log has been successfully transferred to the standby or replicate database. The process for doing this is application dependent.

2. Using the following commands truncate the transaction log in the primary database, and zero the RS locator for that database in the Replication Server. In the primary database run:

   sp_stop_rep_agent primary_dbname
   dbcc settrunc ('ltm', 'ignore') 
   dump tran primary_dbname with truncate_only 
   dbcc settruc ('ltm', 'valid')
   

   Shutdown Replication Server. In the RSSD for the Replication Server run:

   rs_zeroltm primary_servername, primary_dbname