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

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

Solution

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.