CODEWITHSUNDEEP
sql-server-2008-r2
Patrick
Patrick

Reputation: 2577

Why are these resulting symmetric encryption values different?

I'm using something like this: 

OPEN SYMMETRIC KEY SSNKey
    DECRYPTION BY CERTIFICATE SSNCert;
UPDATE 
    Customers
SET 
    SSNEncrypted = EncryptByKey(Key_GUID('SSNKey'), 'DecryptedSSN')

Where SSNEncrypted is a varbinary column. I noticed the values come out different each time. Why is this? And what can I do to get consistent encrypted values, so I can compare them in different tables?

Upvotes: 0

Views: 294

Answers (1)

MatSnow
MatSnow

Reputation: 7527

This is "by design".

The function EncryptByKey is nondeterministic.
But if you decrypt the different values you always get the original decrypted value.

Have a look at this blog on MSDN.

Upvotes: 1

Related Questions