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.