SQL Security: Migrating/Restoring a TDE Encrypted Database to a Different Server.
April 30, 2019
Here are a few simple steps to follow when we are migrating/restoring a SQL Database that has TDE encryption enabled on the source serve/Database.
Step 1: We will need to back up the certificate and private key on the source server.
USE master;
GO
BACKUP CERTIFICATE TDECert
TO FILE = ‘C:\Backup\TDE_Certificate.cer’
WITH PRIVATE KEY
(FILE = ‘C:\Backup\TDE_Key.pvk’,
ENCRYPTION BY PASSWORD = ‘Tren#1000ton’)
Make the password complex and please note it down. We will need the password when we are restoring the certificate and private key
on the destination server.
Step 2: Copy the above certificate and private key to the destination server.
Example: to this location C:\backups\ on the destination server.
Step 3: Create a Master Key on the destination server if it does not already have one.
The first order of the setup is to check to see if a Master Key and Certificate already exist within the Instance on the destination server.
This little code provides the names of master Key and certificates.
USE master
GO
SELECT * FROM sys.certificates.
If a Master key does not already exist, then we create one.
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘erTas#2647!!’
–Any password complex enough.
The above will create the Master Key.
Step 4: After a master key has been created, create a certificate by importing
the certificate we created Step 1.
Here the ‘Decryption
By Password’ parameter is same as that provided to export the certificate to a
file.
CREATE CERTIFICATE TDECert2
FROM FILE = ‘C:\backup\TDE_certificate.cer’
WITH PRIVATE KEY (FILE = ‘C:\backup\TDE_Key.pvk’,
DECRYPTION BY PASSWORD = ‘Tren#100ton’)
That’s it. You are now ready to restore the TDE encrypted SQL backup on the Destination server.
Prakash.