解密以前加密的數據返回 NULL

我在解密之前已加密並儲存在表中的數據時遇到問題。

打開我的對稱密鑰並使用 加密數據後EncyptByKey()，我在嘗試使用 解密時得到 NULL 值DecryptByKeyAutoCert()。

以下是我用於設置和嘗試解密的內容：

創建密鑰和證書

通過密碼創建主密鑰加密 = 'master';

創建證書 cert_dbKeys
密碼加密 = 'abcd'
WITH SUBJECT = '數據庫加密密鑰',
EXPIRY_DATE = '20201031';

創建對稱密鑰 key_dbKeys
使用算法 = AES_128
證書 cert_dbKeys 加密；

加密數據

打開對稱密鑰 key_dbKeys
通過證書解密 cert_dbKeys WITH PASSWORD = 'abcd'

（檢索和儲存選定的數據）
EncryptByKey(Key_GUID('key_dbKeys'), @clean_data);

關閉對稱密鑰 key_dbKeys;

解密數據

從表中選擇 DecryptByKeyAutoCert(cert_id('cert_dbKeys'), N'abcd', table.data)

結果，我得到一列空值。

如果可能，請幫助我確定我為什麼要檢索 NULL 值而不是原始數據，或者是否有更好的方法來執行此任務。

這聽起來像是加密數據中的 與用於加密它的對稱密鑰key_guid不匹配的情況。key_guid

當使用對稱密鑰加密數據然後保存在varbinary列中時，前 16 個字節是Key_guid用於加密數據的對稱密鑰，這解釋瞭如何DECRYPTBYKEYAUTOCERT自動找到正確的對稱密鑰。

您可以執行下面的腳本並驗證是否是這種情況嗎？而且，您是否使用同一個數據庫加密和解密數據？

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

在嘗試重新創建問題後，在插入數據之前打開對稱密鑰可能會出現問題，或者在查看 DECRYPTBYKEYAUTOCERT 的文件後，您需要將返回值（即 varbinary）轉換為 varchar。我還發布了基於您開發的工作腳本。我不得不使用不同的密碼。

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

引用自：https://dba.stackexchange.com/questions/142389