No SQL Server, criei uma chave simétrica e codifiquei uma coluna de acordo com Uma visão geral da criptografia do SQL Server em nível de coluna
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
Eu decodifico com sucesso com:
-- 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
Fiz backup da chave simétrica, fingi que ela foi perdida/descartada e restaurei a chave usando:
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'
Em seguida, tento decodificar, usando:
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
mas não descriptografa corretamente e recebo um erro:
A chave não é criptografada usando o descriptografador especificado.
Como posso usar a chave simétrica restaurada para decodificar minha coluna codificada?
Quando você criou pela primeira vez
SYMMETRIC KEY
você usou a opçãoENCRYPTION BY CERTIFICATE Certificate_test
e a chave foi criptografada usando um certificado, mas o comando RESTORE SYMMETRIC KEY não tem essa mesma opção (limitação estranha) e você teve que usar oENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'
seguinte:Isso significa que agora você deve abrir a chave simétrica usando a senha que você definiu durante a restauração:
Perceber
A falta de uma opção para restaurar a chave
ENCRYPTION BY CERTIFICATE
parece enfraquecer a segurança da chave de acordo com este aviso no documento :Acredito que uma opção seria criar chaves simétricas idênticas em dois servidores
Outra opção é alterá-lo após restaurá-lo: