Two years ago, I used the below code to encrypt (TDE) the database in SQL Server 2008
USE MASTER;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password'
GO
USE MASTER;
CREATE CERTIFICATE DB_CER WITH SUBJECT = 'DB Certificate'
GO
USE DB
GO
CREATE DATABASE ENCRYPTION KEY WITH
ALGORITHM = AES_256 ENCRYPTION BY
SERVER CERTIFICATE DB_CER ;
GO
USE DB
ALTER DATABASE DB
SET ENCRYPTION ON ;
GO
USE master;
GO
BACKUP CERTIFICATE DB_CER
TO FILE = 'c:\Backup\certificate_DB_Certificate.cer'
WITH PRIVATE KEY
(FILE = 'c:\Backup\certificate_DB_Key.pvk',
ENCRYPTION BY PASSWORD = 'Strong Password')
I took full backup for DB Database and the certificate_DB_Certificate.cer & certificate_DB_Key.pvk and save it to my hard drive. After two years I used different PC to restore the MASTER KEY & the CERTIFICATE, so I can to restore the Backup file
CREATE CERTIFICATE DB_CER
FROM FILE = 'D:\Backup\certificate_DB_Certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\Backup\certificate_OCV_DB_Key.pvk',
DECRYPTION BY PASSWORD = 'Strong Password')
After I run the code I got this message
Warning: The certificate you created is expired.
So I delete the CERTIFICATE and I change the Pc date to 2015/Jun (the date when The CERTIFICATE was created) then I re-create the certificate again with successfully completed command, but when I restore the backup file I got this message
Restore of database 'DB' failed.
(Microsoft.SqlServer.Management.RelationalEngineTasks) System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint '0xFC01AD2683E08A4C8CD6A0F037DC66A945FBA44D'. (Microsoft.SqlServer.SmoExtended)
Any suggestions?
