Are SQL Server encryption functions deterministic?

Question

I am learning about SQL Server encryption and I have a question regarding deterministic behaviour.

Are SQL Server encryption functions deterministic? Does it depend on the version (2005,..., 2017)?

I am not referring to 'Always encrypted' feature, but to functions like encryptedbykey.

Answer 1

First of all, this can be tested very easily. You can just encrypt the same value two times and check if the output is the same. Use the code below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  

CREATE CERTIFICATE [CERT_StackOverflow]
WITH SUBJECT = 'example';

CREATE SYMMETRIC KEY [SK_StackOverflow]   
WITH ALGORITHM = AES_256  
ENCRYPTION BY CERTIFICATE [CERT_StackOverflow];  
GO  


OPEN SYMMETRIC KEY [SK_StackOverflow] DECRYPTION BY CERTIFICATE [CERT_StackOverflow];  

SELECT EncryptByKey(Key_GUID('SK_StackOverflow'), 'stackoverflow');  
SELECT EncryptByKey(Key_GUID('SK_StackOverflow'), 'stackoverflow');  


DROP SYMMETRIC KEY [SK_StackOverflow];
DROP CERTIFICATE [CERT_StackOverflow];
DROP MASTER KEY;

So, the result is that the ENCRYPTBYKEY function is not deterministic.

Why? It will be very easy to brute force if it were not. And if you are interested what is going on behind the scene, you can check the following article. Basically, the output text is calculated like this:

CipherTextMessage := KeyGUID + EncryptionHeader + EncryptedMessage

where:

• 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 key ring. That's why when we use DECRYPTBYKEY we do not use to specify the key used during the encryption

• EncryptedMessage is calculated like:

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

And the key is the InitializationVector which is:

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.

If you are interested in deterministic encryption function, because of creating an index in order to optimize a search for example, and you do not want to use Always Encrypted because of all of its limitations, I can tell you how you can create/simulate deterministic encryption using the SQL Server security hierarchy.