When calling 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.

Cryptographic message

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: 0x0000 because we don’t use authentication in this example
  • Plaintext Length: litle endian 16-bit integer. 12 bytes of plaintext will give 0x0C00
  • 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.

References