I'm seeing that MS just released a new version of SQL Server which supports deterministically encrypting a column, so that you can still query it:


In this example, they deterministically encrypt the SSN column:


But this sounds an awful lot like ECB, which I thought was easily cracked (see the penguin diagram):


Is deterministic encryption any good? Does it offer any real protection, or does it just make you feel better?

  • 2
    You need to specify what you mean by "any good". The documentation clearly states the disadvantages of the system and in which case it should be used – Stephane Jun 5 '15 at 15:26

The currently published documentation on what that deterministic encryption really does is not sufficient to come to any definitive conclusion.

What can be said is that such deterministic encryption may be designed and implemented "properly", in which case it incurs no extra weakness beyond the ones inherent to its functionality: namely, that if two values are identical, they will be encrypted to the same value, and this will be visible to the attacker. This is a big unavoidable problem if the values are in a small enough set (e.g. a boolean true/false).

Existing documentation seems to imply that the underlying encryption algorithm is "AEAD_AES_256_CBC_HMAC_SHA_256", which says both a lot, and not nearly enough to actually know what runs under the hood. A "proper" implementation that matches the documentation would compute the IV for CBC encryption with EK(SHA-256(m)) (symmetric encryption of the hash of the complete data to encrypt). Such an implementation would avoid the ECB/Penguin issue. However, it is not clear whether this is what Microsoft actually implemented.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.