Does AES in SQL Server 2008 R2 require an Initialization Vector?

Question

I am testing encryption in Sql Server using AES_128. When I encrypt the same text (4111111111111111) the encrypted binary data is different each time.

Because the encrypted data is different each time do I need to use an Initialization Vector (IV)? My understanding is that an IV is used to protect against duplicate encrypted text to prevent identifying which values are the same.

Does the block-chaining aspect of AES (CBC, CTR) create these different encrypted values?

Here is an example. These are the results of the same value (4111111111111111) encrypted three times:

0x00AC935A664E3C4D97DD1F4A5E316333010000003D6FAE7E02787F52BC5D08BFE9E728E39DFA6CE4FAE2CDCD29D22BB8BEFF3FE9D9A02E7235558BEB11A41C218601B4DE
0x00AC935A664E3C4D97DD1F4A5E31633301000000EDB2D01D6A4DD46241AA616CF74FC41A148EAE6B42E47F3B902F5A04D0021EC34FBE08D634A4C234540565BFB68CD578
0x00AC935A664E3C4D97DD1F4A5E31633301000000D9671BB79C344B0835BB0DCB2FDD58A9AF05095B5ECE0C27077C7A57242674D92D9E1233B1ED1AAA007655F8D53A3DC8

Since the purpose of an IV is to create different ciphertext from the same text string, when that string is encrypted multiple times, my guess is that an IV is already being used here. — Quantum Elf
It appears to me that the cipher text is already in some kind of format, since the first twenty bytes are the same in all three results and the final size does not correspond to what the output size should be (without any additional data, your output size should be 16 bytes). If you can, do some research on how this gets formatted. — strangefreeworld

Duncan Jones Duncan Jones · Accepted Answer · 2012-09-01T21:27:45

SQL Server 2008 adds significant amounts of metadata to the encrypted output, including a randomly generated IV value. Only the final part of the output is the encrypted data.

This should not be a problem for you unless you are attempting to decrypt the data in a non-SQL context. If this is the case, you need to familiarise yourself with the exact format of the output data so that you can understand how to decrypt it correctly.

A previous stackoverflow question on this subject ( How to get compatibility between C# and SQL2k8 AES Encryption? ), suggests a possible output format, however this doesn't appear to quite match your output. Taking the suggested format and applying it to your first output example, we would have:

00AC935A664E3C4D97DD1F4A5E316333 GUID of symmetric key
01000000 Fixed version number
3D6FAE7E02787F52BC5D08BFE9E728E3 Random IV
9DFA6CE4FAE2CDCD29D22BB8BEFF3FE9D9A02E7235558BEB11A41C218601B4DE ?? 32 bytes

The last value doesn't appear to match the format suggested. It is neither 8 bytes of header plus a multiple of 16 bytes of data, nor is it 28 bytes of extended header data plus a multiple of 16 bytes of data. Perhaps your output was truncated?

Does AES in SQL Server 2008 R2 require an Initialization Vector?

1 Answers