Sql-server – Decrypting Previously Encrypted Data Returns NULL

encryptionSecuritysql-server-2008

I'm having trouble decrypting data that has previously been encrypted and stored in a table.

After opening my symmetric key and encrypting the data using EncyptByKey(), I get NULL values when attempting to decrypt using DecryptByKeyAutoCert().

Below is what I use for my setup and attempt at decryption:

Create Keys and Cert

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'abcd'
   WITH SUBJECT = 'Database encryption key', 
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;

Encrypt Data

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'abcd'

(retrieve and store selected data with)
EncryptByKey(Key_GUID('key_dbKeys'), @clean_data);

CLOSE SYMMETRIC KEY key_dbKeys;

Decrypt Data

SELECT DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'abcd', table.data) FROM table

As a result of this, I get a column of nulls.

If possible, please assist in helping me determine as to why I am retrieving NULL values instead of the original data or if there is a better way to carry out this task.

Best Answer

This sounds like a case where the key_guid in the encrypted data does not match the key_guid for the symmetric key used to encrypt it.

When data is encrypted using a symmetric key and is then saved in a varbinary column, the first 16 bytes are the Key_guid of the symmetric key used to encrypt the data, which explains how DECRYPTBYKEYAUTOCERT can automatically locate the correct symmetric key.

Can you run the script below and verify that this is the case? And, did you encrypt and decrypt the data using the same database?

select 
    name,cast(key_guid as varbinary(max))
from sys.symmetric_keys
where
    name ='key_dbKeys'

select distinct table.data from table
where 
    table.data is not null

After attempting to recreate the problem, there could be a problem opening the symmetric key prior to inserting the data or, after reviewing the documentation for DECRYPTBYKEYAUTOCERT, you need to cast the return, which is varbinary, to varchar. I'm also posting the working script that is based on what you have developed. I had to use a different password.

declare  @clean_data varchar(100)

set  @clean_data='Hello World'

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssword!';

CREATE CERTIFICATE cert_dbKeys
   ENCRYPTION BY PASSWORD = 'P@ssword!'
   WITH SUBJECT = 'Database encryption key', 
   EXPIRY_DATE = '20201031';

CREATE SYMMETRIC KEY key_dbKeys 
   WITH ALGORITHM = AES_128
   ENCRYPTION BY CERTIFICATE cert_dbKeys;
create table EncryptedData
( secretmessage varbinary(max))

OPEN SYMMETRIC KEY key_dbKeys
DECRYPTION BY CERTIFICATE cert_dbKeys WITH PASSWORD = 'P@ssword!'

insert into EncryptedData (secretmessage) values(EncryptByKey(Key_GUID('key_dbKeys'), @clean_data))

CLOSE SYMMETRIC KEY key_dbKeys;

SELECT cast(DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'P@ssword!', secretmessage) as varchar(100)) FROM EncryptedData
Related Question