If you have configured Adaptive Server to use encrypted columns, you must drop or decrypt the data in the encrypted columns before you reload it on Adaptive Server version 15.0 or Adaptive Server release 15.0 ESD #1. If you do not, processing encrypted data in the earlier version of Adaptive Server results in errors or data corruption.
You cannot load any dumps of Adaptive Server 15.0.2
databases or transactions with encrypted columns into an earlier
version of Adaptive Server 15.0 until you have performed the following
steps.
To decrypt the data on all affected tables and prepare the server to be downgraded:
Start Adaptive Server in single-user mode to ensure that no other user can process encrypted columns while you remove encryption from the database.
Verify that sp_configure "enable encrypted columns” shows a run value of 1.
If you have configured restricted decrypt permission, turn it off:
sp_configure "restricted decrypt permission", 0
Generate a list of all encryption keys in all databases. You must have the sso_role to run this command:
sp_encryption helpkey, NULL, all_dbs
Generate a list of all columns encrypted by each key. You must have the sso_role to run this command, and you must run the command from the database where the key was created:
sp_encryption helpkey, key_name, display_cols
The result set includes the key name, key owner, database name, table owner, table name, and column name for each column encrypted by key_name.
Alternatively, you can run sp_help table_name on each table in each database. The column description indicates which columns are encrypted.
Either drop the tables that have encrypted columns, or run alter table to decrypt the data. You must be the table owner or have sa_role to drop the table. You must be the table owner or have the sso_role to decrypt the data.
For example, to decrypt the column cc_no in the customer table, enter:
alter table customer modify cc_no decrypt
Drop all encryption keys to ensure that you have removed all encryption from the database. Adaptive Server returns an error message if you attempt to drop a key that is associated with an existing encrypted column.
For example, to drop an encryption key user_key1, as key owner or as a user with the sso_role, enter:
drop encryption key user_key1
To determine which privileges have been granted to users with the keycustodian_role, run the following command in each database:
sp_helprotect keycustodian_role
Remove the privileges listed:
revoke <permission> from keycustodian_role
In each database, for each user, run:
sp_displayroles <username>
Revoke keycustodian_role from any users to whom it was granted:
revoke role keycustodian_role from <user1>, <user2>, ...
Drop the system encryption password in every database where it is set. Enter:
sp_encryption system_encr_passwd, "old password", null
Drop sp_encryption and the auxiliary stored procedures (sp_aux_encrkey_lookup, sp_aux_encrkey_info, and sp_aux_encr_lookup).
Disable encrypted columns:
sp_configure "enable encrypted columns", 0
Execute sp_downgrade, which verifies that you have cleared your databases of all encrypted columns and prepares the server for downgrade. See “Running sp_downgrade on encrypted columns” for more information.
