Certificate and Symmetric Key Restore is not decrypting the data originally encrypted

Question

I am working in a test environment with SQL Server 2017 to verify that encrypting data with a symmetrical key will meet our needs. I have successfully encrypted and decrypted data using the symmetrical key below, but when I test the restore of the key on the same server, it will not decrypt the data that was originally encrypted. I am sure I am just missing a piece of the puzzle but I cannot see what it is.

I created the certificate and key with the following:

CREATE CERTIFICATE TestCert   
   ENCRYPTION BY PASSWORD = 'QGTkj3E$NvySXU4x7ens'  
   WITH SUBJECT = 'Testing encryption by Certificate',   
   EXPIRY_DATE = '20251231';  

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

I then backed up the certificate using the following:

BACKUP CERTIFICATE TestCert
 TO FILE = N'c:\Backup\TestCert.cer'
 WITH PRIVATE KEY
  ( FILE = N'c:\Backup\TestCert.pvk'
  , ENCRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You'
  , DECRYPTION BY PASSWORD = N'QGTkj3E$NvySXU4x7ens'
  )
;

I then deleted the symmetrical key and then the certificate. I successfully recreated the certificate and key using the following:

CREATE CERTIFICATE TestCert
FROM FILE = N'c:\Backup\TestCert.cer'
WITH PRIVATE KEY
(
     FILE = N'c:\Backup\TestCert.pvk',
     DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You',
     ENCRYPTION BY PASSWORD = 'QGTkj3E$NvySXU4x7ens'  
);

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

I am able to use the same select statement (no errors) but the decrypted data comes back NULL. If I update the encrypted data in the table with the new key, the select statement pulls the decrypted data successfully.

ALTER PROCEDURE [dbo].[spGetData] 
        @uid nvarchar(128),
    @CertKey as varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @sqlOpenCert AS NVARCHAR(MAX);
    SET NOCOUNT ON;
    SET @sqlOpenCert = 'OPEN SYMMETRIC KEY TestSymKey DECRYPTION BY CERTIFICATE TestCert WITH PASSWORD = '''+@CertKey+'''';

    EXEC sp_executesql @sqlOpenCert;

    select [DateEncValue], CONVERT(nvarchar, DecryptByKey([DateEncValue])) as dateDec, TextEncValue, CONVERT(nvarchar, DecryptByKey(TextEncValue)) as textDec
    from tblEncryptTest
    where encryptid = @uid

    CLOSE SYMMETRIC KEY TestSymKey;
END

The table is setup with 4 columns. 2 to store the orignal value (test purposes) and two to store the encryted values.

CREATE TABLE [dbo].[tblEncryptTest](
    [EncryptID] [int] IDENTITY(1,1) NOT NULL,
    [TextValue] [varchar](50) NOT NULL,
    [TextEncValue] [varbinary](8000) NULL,
    [DateValue] [date] NOT NULL,
    [DateEncValue] [varbinary](8000) NULL,
 CONSTRAINT [PK_tblEncryptTest] PRIMARY KEY CLUSTERED 
(
    [EncryptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

An example of the original values is TextValue = 'This is a text Value', DateValue = '12/15/1986'

Thank you

"the same select statement" what select statement? And what's your sample table and input data? Either way, the answer is simple: the certificate and symmetric key are completely separate. You need to BACKUP SYMMETRIC KEY and RESTORE that also (or backup the whole database). What protects the symmetric key (the certificate) is irrelevant if you create a new one. — Charlieface
– Charlieface, Commented Nov 4, 2024 at 17:02
Putting it another way: the cert protects the symkey so you need permissions to the cert to be able to open the symkey. When you restored, all you restored was the cert, you did not restore the symkey, instead you create a new one. What you protect the new one with is up to you: it could be the same cert, a new one, a password, another symkey or an asymkey. Note that a database backup will backup all of those together. — Charlieface
– Charlieface, Commented Nov 4, 2024 at 17:06
It looks like the Backup and Restore Symmetric Key was added in 2022. Based on my research prior version instructions were to restore the cert, then recreate the Key based on the restored cert using the same values as the original create statement. I will test restoring the entire DB. However if something goes wrong with the Key or Cert only, I would have to restore then move data to the restore to synch it up? Thanks — user16421
– user16421, Commented Nov 4, 2024 at 17:24
You wouldn't have to move any data. You can restore the symkey separately from the cert, they are independent when you backup or restore them. It's only once they are on the server that the cert is protecting the symkey. So if the cert was lost, you could restore the cert to get access, or restore the symkey and use a new cert. Either way, your actual question is still not clear, as you haven't provided a script that shows the issue. — Charlieface
– Charlieface, Commented Nov 4, 2024 at 17:27
I have updated the post, per your request. Please let me know if there is anything else that would help clear it up. Thanks. — user16421
– user16421, Commented Nov 4, 2024 at 17:33

Dale K · Accepted Answer · 2024-11-04 18:52:16Z

BACKUP CERTIFICATE TestCert only backs up the certificate, not anything it protects.

I then deleted the symmetrical key

The key is now gone, you cannot recover it, nor can you recover any data you encrypted. Throw it all out and start again.

CREATE SYMMETRIC KEY TestSymKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TestCert;

That just creates a new key, protected by the now restored old certificate. The certificate and key are unrelated, except for the fact that the key is now being encrypted and protected by the certificate.

What you need is to backup the key.

BACKUP SYMMETRIC KEY TestSymKey
 TO FILE = N'c:\Backup\TestCert.cer'
 ENCRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You';

Then restore it

RESTORE SYMMETRIC KEY TestSymKey
 FROM FILE = N'c:\Backup\TestCert.cer'
 DECRYPTION BY PASSWORD = N'AReallyStr0ngK#y4You';

Do note that unfortunately you cannot protect the key backup with the certificate. Instead a password must be used, which encrypts with 3DES, a weak encryption.

An alternative is to recreate the symmetric key using the same parameters. For that, you need to know the source. So change your original CREATE to:

CREATE SYMMETRIC KEY TestSymKey
WITH
  ALGORITHM = AES_256,
  KEY_SOURCE = 'ReallyStrongPasswordHere',
  IDENTITY_VALUE = 'id_phrase'
ENCRYPTION BY CERTIFICATE TestCert;

Now to recreate it, just execute the same statement on the new server. It must be the same version of SQL Server.

One final note: if you backup/restore the database itself then the certificate and key will be backed up also.

Thank you the piece I was missing was the parameters in when creating the key. I am unable to backup the key because of the version of SQL Server I am on. Thanks again

Collectives™ on Stack Overflow

Certificate and Symmetric Key Restore is not decrypting the data originally encrypted

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related