CODEWITHSUNDEEP
mysqlsql-servert-sqlencryptionaes
user3297594
user3297594

Reputation: 11

Decrypt MS SQL Server 2008 R2 data in MySQL 5.5 using AES encryption/decryption

I have data in MS SQL 2008 R2 which I want to encrypt and then transport to a MySQL 5.5 database. The reason for the encryption is that I do not completely trust the transport layer, and also that this data is rather sensitive so it would be best to store it in encrypted format. The data is created and updated in MS SQL Server 2008 but only needs to be used for "select" in MySQL.

The challenge that I have run into is that AES algorithm in MS SQL Server 2008 R2 is evidently not the same as MySQL 5. It adds "padding" to the content, changes the values depending on whether the original data is encoded as UTF-8 or UNICODE, and evidently has some 16 bit random vector that is added into the hash.

Can you advise on possibly other encryption/decryption algorithms that would be identical in MS SQL Server 2008 R2 and MySQL 5.5?

Upvotes: 1

Views: 911

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294487

For transfer of data use a protocol that is specially designed for data transfer. Neither ENCRYPTBYKEY nor AES_ENCRYPT are suitable protocols, they are only cryptographic functions.

A good example for a protocol designed for data transfer is Transport Layer security. It takes care of a lot of the little 'details' you have no idea they need to be covered. Use it. Do not attempt to reinvent the wheel and release on the world Yet Another Broken Encryption Scheme.

To strictly answer the question: no, there is no way to decrypt in MySQL data encrypted using the SQL Server cryptographic functions. The SQL Server functions output contains the key ID and the IV used in a format that is proprietary and not documented.

Upvotes: 1

Related Questions