Take the 2-minute tour ×

I looked at this link that shows how to encrypt a SQL column via a certificate (RSA??) and AES256.

I created a dummy table (script at GitHub) intentionally using identical plain text (Credit Card = 1) and then tries a few simple commands to actually encrypt the data.

I noticed that the resulting cipher text had an identical prefix of bytes. I usually see this in AES ECB but then ECB also results in every block being identical - not just the first. Here the latter portion turns out different despite the same plain test. It might be possible they are using CBC but instead reusing the IV for the entire table?

enter image description here

Edit: The screenshot turned out tiny: Here is the text dump

CardNumber, CardNumber_Encrypted

1, 0x0062513D35F0404A8C2B72A7C64FCDD1010000002887B22A1A1E238BD5658A42F30E0EE34458A4781C4FF7CFAEA91E704E5B9EAE

1, 0x0062513D35F0404A8C2B72A7C64FCDD101000000DFDD0277220EB5CC23D5820FCF763BBE0970072915946ED2E4CAC05D977F7D67

Question: What AES mode does SQL server 2012 use? Can it be changed?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

This blog post (Microsoft has totally embraced the document-by-blog fashion, apparently) describes the encryption format used by SQL Server. That's for SQL Server 2008, but, for backward compatibility reasons, the same format must be supported by SQL Server 2012. Block cipher mode of operation is CBC, there is an IV, and it includes some provisions for integrity check (a SHA-1 hash included in the encrypted data: that's a homemade construction which cryptographers will frown upon, although there is no flagrant weakness in it -- but, in these matters, you never know...). The blog post does not tell what kind of padding is used.

There does not appear to be any documented way to specify any other mode, either during symmetric key generation or when doing the encryption itself.

share|improve this answer
Do you know why the first 20 bytes are identical? I suspected the SHA1 since 160bits=> 20 bytes, but even without the SHA1 hash the 1st 20 bytes are identical (UPDATE dbo.CreditCard SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11'), CardNumber) WHERE CardNumber = '1') –  DeepSpace101 Feb 20 '13 at 0:00
@Sid: this is specified in the blog post: first 16 bytes are the key GUID, so will be the same for all data elements encrypted with the same key. They are followed by 01000000 ("01" for the format version -- only one defined for the moment -- then three reserved bytes of value "00"). 16 + 4 = 20. –  Thomas Pornin Feb 20 '13 at 1:54
add comment

Your Answer


