replicating varbinary columns

[jayrijnbergen]

SRS supports replicating varbinary columns, but does that mean SRS also deals with different endianess between source and target servers?

Example:

Source system on Linux, little endian.

Target system on AIX, big endian

Data to be replicated is encrypted using Sybase column encryption, that means the column is replicated as ciphertext in varbinary format.

Is this varbinary string automatically byte swapped for endianess on the target server?

The encryption key can be sync'd using dump & load, or it can be replicated.

If dump & load is used, then the load database will take care of the cross platform endianess conversion.

What about replicating the encryption key cross platform with different endianess?

[Mark_A_Parsons · Accepted solution]

At some point early in the ASE 15.x release schedule (sorry, don't recall the specific 15.x version), the method used to encrypt ASE passwords was changed to a machine-independent format.

This meant that you could now do a straight bcp/copy of the syslogins.password column between different endians without a need to perform any sort of byte swapping; net result was that you could now log into both ASEs (little endian, big endian) with the same password.

I'm wondering if ASE 15.x/16.x column-level encryption uses a similar machine-independent format?

NOTE: You don't mention your ASE version so I'm assuming you're running ASE 15.x/16.x.

Obviously (?) it would be easy enough to test this theory of machine-independent data storage of the encrypted/varbinary values:

- create encryption key in PDS (little endian)

- copy encryption key from PDS to RDS (big endian) (use XPDL of encryption key db, ddlgen, etc)

- create new table with an encrypted column (perhaps a few columns of different datatypes, eg, varchar(x), datetime, numeric) in both PDB and RDB

- insert single row of data in PDB table

- set ciphertext on; select * from PDB table

- set ciphertext on; insert * into RDB table (using varbinary values displayed from PDB/select statement) [NOTE: This is what the SRS/DSI connection does in the RDB: set ciphertext on; insert/update varbinary values.]

- set ciphertext off'; select * from RDB table to see if the values are decrypted properly

[jayrijnbergen · Accepted solution]

thanks Mark, indeed I'm just being lazy here

I should have done that test already

Will do the test and post the results here

good point about the passwords, forgot about that.

[former_member182259 · Accepted solution]

Actually, a better test would be to simply try replicating.   Here is the deal.   There are a lot of data structures already in native datatypes that are subject to byte swapping - e.g. even INT....but definitely datetime, numeric, money, float, etc.   Some are not (e.g. varchar/varbinary) - will explain in a minute.   As part of the standard OCS locales/charsets/platform support we can replicate datatypes between platform without issue.   Part of this is because SRS generally translates params into string form for the SQL - but if using an fstring with output RPC - much as if making a client side RPC call (a common situation for fully prepared statements and proc calls), the client translates the string datavalues into the appropriate datatype in binary form.   OCS & ASE together can handle the endianess or otherwise we would have a real mess with PC clients & Unix DBMS servers.

However, a key factor of byte swapping in endianess is based on full bytes vs. swapping bit positions.  In other words, in a two byte sequence such as 0x0A, becomes 0xA0 when swapped vs. swapping the bits within the bytes.   Generally speaking varchar/varbinary are simply strings of bytes - a single byte for each character.   As a result, no byte swapping is required.  Even for some multi-byte, things are the same as UTF-8 using multiple single bytes for each character.   The only one I am not sure about is 16-bit character sets.....however, like I said, I am pretty sure the OCS layer handles this.