Since the introduction of SQL Server 2008 extensible key management  (EKM), new opportunities may arise to handle data encryption on the client while still making the plaintext data accessible to authorized users in SQL Server. One issue between SQL Server and third party clients has been already discussed in the SQL Server Security forum in the past: describing the cryptographic message elements used by the resulting ciphertext generated by EncryptByKey builtin.

  While we are in the process of finalizing our documentation regarding this subject matter, I would like to take this opportunity to bring you an early draft view of the information and to get your feedback.. Below I will explain each one of the parts of the message along with an example. Notice that in all cases we are using little-endian byte ordering.

CipherTextMessage := KeyGUID + EncryptionHeader + EncryptedMessage

KeyGUID := {16 bytes} Key_guid. This GUID serves as an identifier for the key and it is stored in metadata (SELECT key_guid FROM sys.symmetric_keys). It is used during decryption for finding the corresponding key in the keyring.

EncryptionHeader := Headerversion + ReservedBytes

Headerversion := {1 byte} SQL Server encryption message version. Current version = 1

ReservedBytes := {3 byte} Reserved. Must be 0.

EncryptedMessage := InitializationVector + _EncryptFunction(SymKey, InitializationVector, InnerMessage)

InitializationVector := {1 block} the length of this field depends on the algorithm being used. All AES family keys will be 16 bytes per block, while the DES family keys are 8 bytes per block. Initialization vectors are used to initialize the block algorithm. It is not intended to be a secret, but must be unique for every call to the encryption function in order to avoid revealing patterns. For simplicity we will refer to the Initialization Vector as IV.

_EncryptFunction(IV, key, plaintext) := {variable length} function used to encrypt the plaintext data using the key (the algorithm is specified by the key itself) and the IV. In the case of SQL Server, this function is the CryptEncrypt, but since the supported encryption algorithms are standard, this function should be considered generic.

  Interoperability note: We use the block cipher in Cipher Block Chaining (CBC) mode. (default mode for most algorithms for Microsoft cryptographic providers, see AES provider Algorithms in BOL for further details).

InnerMessage := InnerMessageHeader + IntegrityBytes + Plaintext

InnerMessageHeader := MagicNumber + IntegrityBytesLength + PlaintextLength

 

MagicNumber := {4 bytes} As the name suggests, it is only an arbitrary value used to identify the message format. The current expected value is 3131961357 (decimal, unsigned). The main goal of this value is to detect if the message format is the expected one (if the value is incorrect, the message will be discarded). Extra points for anyone who identified the hexadecimal representation for this value which is (DWORD) 0xBAADF00D. For anyone curious about magic numbers, here is an interesting article about hexspeak.

IntegrityBytesLength := {2 bytes} The length of the IntegrityBytes field. May be either 0 or 20 (decimal).

IntegrityBytes := {IntegrityBytesLength bytes} This field is used when the @authenticator parameter is used when calling EncryptByKey. When this parameter is used, the Integrity field will be the cryptographic hash (SHA1) of the @ClearText (Plaintext) concatenated with the @authenticator parameter.

PlaintextLength := {2 bytes} The length of the Plaintext field.

Plaintext := {PlaintextLength bytes} The content of the @ClearText parameter (binary representation) when calling EncryptByKey.

Let’s use concrete examples and analyze each part of the message. For example, a call to SELECT ENCRYPTBYKEY( key_guid('key1'), 'Hello World!') resulted in the following ciphertext:

0x0096F42B8789694F87002E54D30FA0210100000013BDD2DD73F4392654565D3D156A073D4E8B16E0E11D0984F8E564E986268BF7D5C21158F1A511347F0177C5B1B18D24

Detailed analysis of each field:

Field

Value (hex)

Notes

KeyGUID

0x0096F42B8789694F87002E54D30FA021

2BF49600-8987-4F69-8700-2E54D30FA021

This value should match the GUID for our key.

EncryptionHeader

0x01000000

Version 1

Reserved bytes = 0

IV

13BDD2DD73F4392654565D3D156A073D

Randomly generated

 

Since the rest of the message is encrypted, it would be of little value to try to analyze it in the current form, but the following analysis will describe the inner message before being encrypted:

0x0DF0ADBA00000C0048656C6C6F20576F726C6421

 

Field

Value (hex)

Notes

MagicNumber

0x0DF0ADBA

3131961357

IntegrityBytesLength

0x0000

0 bytes since we didn’t use the @authenticator parameter

PlainTextLength

0x0C00

@ClearText length = 12 bytes

IntegrityBytes

Empty

Field not used

Plaintext

0x48656C6C6F20576F726C6421

ASCII representation for “Hello World!”

 

If we would have called the EncryptByKey builtin using the @authenticator parameter, the inner message would have looked slightly different; for example when calling SELECT ENCRYPTBYKEY( key_guid('key1'), 'Hello World!' ,1, 'SQL Server 2008 demo'):

0x0DF0ADBA14000C00429DF3A3B39257F69C3359CA7F94B3F26AF3BE2B48656C6C6F20576F726C6421

 

Field

Value (hex)

Notes

MagicNumber

0x0DF0ADBA

3131961357

IntegrityBytesLength

0x1400

20 bytes since we used the @authenticator

PlainTextLength

0x0C00

@ClearText length = 12 bytes

IntegrityBytes

0x429DF3A3B39257F69C3359CA7F94B3F26AF3BE2B

SHA-1 hash of “Hello World!SQL Server 2008 demo”

Plaintext

0x48656C6C6F20576F726C6421

ASCII representation for “Hello World!”

 

 -Raul Garcia
  SDE/T
  SQL Server Engine