在 SQL Server 中,我创建了一个对称密钥,并根据列级 SQL Server 加密概述对一列进行了编码
use AdventureWorks2022
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLShack@1';
CREATE CERTIFICATE Certificate_test WITH SUBJECT = 'Protect my data';
CREATE SYMMETRIC KEY SymKey_test WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test;
-- encode using symmetric keys
ALTER TABLE AdventureWorks2022.HumanResources.Employee
ADD BankACCNumber_encrypt varbinary(MAX)
OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;
UPDATE AdventureWorks2022.HumanResources.Employee
SET BankACCNumber_encrypt = EncryptByKey (Key_GUID('SymKey_test'), NationalIDNumber)
FROM AdventureWorks2022.HumanResources.Employee;
CLOSE SYMMETRIC KEY SymKey_test;
select BankACCNumber_encrypt,* from AdventureWorks2022.HumanResources.Employee
我成功解码:
-- decode
OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test;
SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
FROM AdventureWorks2022.HumanResources.Employee
我备份了对称密钥,假装它丢失/丢失了,然后使用以下命令恢复密钥:
OPEN SYMMETRIC KEY SymKey_test
DECRYPTION BY CERTIFICATE Certificate_test
BACKUP SYMMETRIC KEY SymKey_test
TO FILE = 'C:\stuff\Sym_key_backup.cer'
ENCRYPTION BY PASSWORD = 'A4FR^hhjg££fhj'
CLOSE SYMMETRIC KEY SymKey_test;
DROP SYMMETRIC KEY SymKey_test;
RESTORE SYMMETRIC KEY key_name FROM
FILE = 'C:\stuff\Sym_key_backup.cer'
DECRYPTION BY PASSWORD = 'A4FR^hhjg££fhj'
ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'
然后我尝试解码,使用:
OPEN SYMMETRIC KEY key_name
DECRYPTION BY CERTIFICATE Certificate_test;
SELECT nationalIDNumber,BankACCNumber_encrypt AS 'Encrypted data',
CONVERT(nvarchar, DecryptByKey(BankACCNumber_encrypt)) AS 'Decrypted Bank account number'
FROM AdventureWorks2022.HumanResources.Employee
但它不能正确解密,并且我收到错误:
密钥未使用指定的解密器加密。
如何使用恢复的对称密钥来解码我的编码列?
首次创建时,
SYMMETRIC KEY
您使用了选项ENCRYPTION BY CERTIFICATE Certificate_test
,并且密钥使用证书加密,但是RESTORE SYMMETRIC KEY命令没有相同的选项(奇怪的限制),您必须使用ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'
如下方法:这意味着现在您必须使用在恢复期间定义的密码打开对称密钥:
注意
根据文档中的警告,缺少使用恢复密钥的选项
ENCRYPTION BY CERTIFICATE
似乎削弱了密钥的安全性:我认为一个选择是在两台服务器上创建相同的对称密钥
另一种选择是在恢复之后对其进行更改: