EncryptByKey() in SQL server you will get a bunch of bytes in return. This article from Microsoft describes the structure of these bytes.
If you pay close attention, it is even possible to work with this format outside of SQL server. Both decrypting and encrypting is possible, given that you can get the encryption key available outside of SQL Server. Using a HSM with the EKM integration is one way to do this, as SQL server has no native integration for exporting and importing keys.
Long story short:
- 16 bytes: key GUID
- x bytes: header
- x bytes: IV
- n bytes: ciphertext
Creating the ciphertext
The plaintext must be prepended with the following bytes:
- Magic number:
0x0DF0ADBA. Decryption will fail if the plaintext does not start with this number.
- Integrity bytes length:
0x0000because we don’t use authentication in this example
- Plaintext Length: litle endian 16-bit integer. 12 bytes of plaintext will give
- IntegrityBytes: nothing, we don’t use this in this example
The header will have these bytes:
0x0DF0ADBA00000C00. The plaintext bytes follow immediately after it.
After these bytes, the padded plaintext follows. PKCS#5 padding seems to work just fine. The plaintext with header bytes is then fed into the AES-CBC cipher.