Replicating encrypted columns

As of version 15.0, Replication Server supports replication of encrypted columns in Adaptive Server. Replication Server replicates the encrypted columns from the primary Adaptive Server database, in binary format as ciphertext values, rather than clear text values.

The encryption keys for the primary and the replicate databases must be identical. Use replication to create the encryption key at the replicate database, or use a dump and load command to ensure that the encryption keys are identical.

Replication Server in a warm standby and in an MSA environment replicates the create, alter, and drop commands of the encryption keys. It also replicates alter table to encrypt or decrypt a column. To replicate the create, alter, and drop encryption key DDL commands, the system_encr_passwd must be identical for both the primary and the replicate databases.

If the encryption keys are stored in a separate database, ensure that it is synchronized at the same time as the database containing the encrypted columns using those encryption keys.

If data has diverged between the primary and the replicate databases because of earlier encryption keys or because of differences between the initialization vector and the padding, manually sync the data to avoid failures of update and delete statements.

Replicating encrypted columns has these restrictions:

• Text and image columns cannot be encrypted.

• Encrypted columns cannot be used in a where clause because Replication Server receives the value in ciphertext and cannot compare that value to a clear text value. The encrypted columns cannot be searchable columns.

• If an encrypted column is used in a primary key, the encryption key must be defined with INIT_VECTOR NULL and PAD NULL.

  The purpose of an initialization vector and padding is to randomize the ciphertext so that two like values encrypted by the same key result in two differing ciphertext strings. If the ciphertext for encrypted data at the primary and the replicate sites differ, then any attempt by the Replication Server to match the before-image from the primary site with the data at the replicate site fails.

  If no initialization vector is used, the ciphertext at the source and the target databases exactly match. The matching is required because Replication Server issues a where clause on the update/delete statements using the ciphertext of the encrypted columns.

• If a table replication definition is not used to replicate the data in a warm standby or MSA environment for a table, all the encrypted columns in that table must be encrypted with keys defined as INIT_VECTOR NULL and PAD NULL.