0

Recently I'm studying on how the replication of encrypted data works from Sybase ASE to Sybase ASE, and I have few queries on below:-

  1. What will be the datatype for columns that contain encrypted data in repdef?
  2. Any grant statements required to grant to replication id?
  3. Any extra configuration needs to be done on Repserver? RepAgent? Target Connection? (My RepAgent and Target Connection are all ready)
  4. Any possibilities to replicate this encrypted data from Sybase ASE to Oracle side?

Appreciate if you could solve my doubt. Thanks.

| improve this question | |
1

Assumptions:

  • you're referring to ASE's column-level encryption which requires a user/role to have decrypt permissions to see the actual data (as opposed to database-level encryption which merely encrypts the data out on disk)

ASE encrypted data is replicated as varbinary; this means that at no time is the data decrypted and/or re-encrypted; of course, it also means you need a copy of the encryption key in the RDS.

NOTE: If using a MSA or Warm Standby setup you can replicate the encryption key(s) to the RDB.

NOTE: If you have the wrong encryption key in the RDS, that varbinary(X) data will not be decryptable ("Duh, Mark!" ?).

If you're creating explicit repdefs for the table then you'll need to designate the encrypted column's datatype as varbinary(X), where X is the value you'll find in the associated (PDB) syscolumns.encrlen column.

The default function string class is already configured with a function/function-string that supports replication of encrypted data; this basically consists of issuing set ciphertext on when the DSI connects to the RDB, thus allowing that varbinary(X) data to be written directly into an encrypted column.

Permissions to write the varbinary(X) data into the RDB table should be the same as for any write operation into the table.

As for replication of encrypted data in a heterogenous environment ... I'm about 98% sure this isn't possible if simply because each RDBMS product, if it supports some sort of column-level encryption, uses different encryption methods and keys. [I'd have to peruse the latest SRS 16.x manuals if this has changed with recent versions of the product.]

| improve this answer | |
  • I actually have created the repdef and subscription on the table which has the encrypted column (with datatype as 'varbinary(113)' in repdef), also I have granted insert permission to my replication ID to be able to write into my target DB. But, when come to testing on inserting data, my data did not replicate to the target as expected, and it is without any error on RS log, any advice? – Tittus Jul 31 '18 at 2:06
  • please update your question with the steps you performed, the repdef, and more details as to what you mean by data did not replicate/as expected ... did any data come through? did the encrypted volume come through but get garbled? have you insured the correct key is available in the replicate? – markp-fuso Jul 31 '18 at 2:33
  • I missed to turn on the replication for the table just now, replication of encrypted column from ASE to ASE is working fine now. Also, I realized that I couldn't view the data being replicated into Target DB after I have granted with decrypt permission, I got the message An internal error occurred during an encryption operation (error code -1). Sybase error code=15413. Here is my grant decrypt permission statement grant decrypt on <table_name> to <role>. Before I run the grant decrypt permission statement, I can still see my data as 'XXXXXXXXXXX'. Any idea? – Tittus Jul 31 '18 at 10:04
  • I'd start by running set ciphertext on and selecting the encrypted column from your row ... do this in both the PDB and RDB and compare the varbinary strnigs to make sure they are identical (if not identical then there's an issue with replication); set ciphertext off (or log out and back) in the RDB, insert a row try to access the (encrypted) column with 2 diff users (1 with decrypt perms, 1 without decrypt perms) ... do you get the expected results (this verifies no issues with encryption in general and a valid enc key is in place); last ... – markp-fuso Jul 31 '18 at 12:55
  • ... you'll need to run your series of sp_encrryption in PDB and RDB to verify the same exact encryption key is available in both the PDB and RDB – markp-fuso Jul 31 '18 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.