3

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.

2

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
  • Thanks, Michael. After running your script, I have verified that the key_guid for the key does not match the first 16 bytes of the encrypted data entries for the column. I have tested my initial DECRYPTBYKEYAUTOCERT call on previous backed-up data with a matching key_guid and they decrypted fine. Guess somewhere along the lines the symmetric key or certificate was replaced with a different one. – user3504079 Jun 30 '16 at 19:39
  • If you have all of the parameters used in the original create symmetric key command, which are key_source, identity_value and algorithm, then you can create an identical key with the same key_guid. – Michael Keleher Jul 8 '16 at 13:33

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.