How to use restored key to decode an encoded column in SQL Server?

Question

In SQL server, I created a symmetric key and encoded a column as per An overview of the column level SQL Server encryption

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

I decode succeessfully with:

-- 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

I backed up the symmetric key, pretended it was lost/dropped, and restored the key using:

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'

I then try to decode, using:

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

but it does not decrypt correctly, and I get error:

The key is not encrypted using the specified decryptor.

How can I use the restored symmetric key to decode my encoded column?

score 4 · Accepted Answer · edited Jul 25 '24 at 01:00

When you first created the SYMMETRIC KEY you used the option ENCRYPTION BY CERTIFICATE Certificate_test and the key was encrypted using a certificate, but the RESTORE SYMMETRIC KEY command does not have that same option (strange limitation) and you had to use the ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4' as follows:

RESTORE SYMMETRIC KEY key_name FROM 
    FILE = 'C:\stuff\Sym_key_backup.cer'
      DECRYPTION BY PASSWORD = 'A4FR^hhjg££fhj' 
      ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'

It means now you have to open the symmetric key using that password you defined during the restoration:

OPEN SYMMETRIC KEY key_name
        DECRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4'

Notice

The lack of an option to restore the key using ENCRYPTION BY CERTIFICATE seems to weaken the security of the key according to this warning on the doc:

Caution

When a symmetric key is encrypted with a password instead of a certificate (or another key), the TRIPLE DES encryption algorithm is used to encrypt the password. Because of this, keys that are created with a strong encryption algorithm, such as AES, are themselves secured by a weaker algorithm.

I believe an option to that would be Create identical symmetric keys on two servers

Another option is to alter it after restoring it:

ALTER SYMMETRIC KEY key_name ADD ENCRYPTION BY CERTIFICATE Certificate_test;
ALTER SYMMETRIC KEY key_name DROP ENCRYPTION BY PASSWORD = '3dH85Hhk003GHk2597gheij4';

How to use restored key to decode an encoded column in SQL Server?

1 Answers1

Notice