Backup/Restore a SQL TDE certificate

Database backups are critical to any organization. Similarly, we need to ensure the TDE certificates are backed up in case of a DR scenario. TDE cert is present on any database that has encryption at rest enabled on it.

Below script takes backup of the master key, TDE cert and Private key attached to the cert:

BACKUP MASTER KEY
TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_MK'
ENCRYPTION BY PASSWORD = 'StrongPassword';
GO

USE [master];
GO
BACKUP CERTIFICATE Livongo_Ascend_Cert TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY
(
    FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
    ENCRYPTION BY PASSWORD = 'StrongPassword'
);
GO

Below is the script to use in a DR scenario or when you need to restore an encrypted backup to a new server:

/* This creates the service master key*/ 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'
GO

/* This creates the TDE cert from backup */
Use [master]
GO
CREATE CERTIFICATE TDE_databasename
FROM FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY (
FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
DECRYPTION BY PASSWORD='StrongPassword')
GO

/* This creates the encryption key using the cert we created in the above step*/
Use [databasename]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_databasename
GO

/* This step enables the encrption on the database*/
ALTER DATABASE [databasename]
SET ENCRYPTION ON
GO

Use the following script to view all the encrypted DBs and their Cert name, encryption key used.

SELECT db_name(database_id) AS [Database Name],
    dek.key_length as [Key Length],
    case encryption_state when '0' then 'No database encryption key present, no encryption'
    when '1' then 'Not Encrypted'
    when '2' then 'Encryption in Progress'
    when '3' then 'Encrypted'
    when '4' then 'Key Change in Progress'
    when '5' then 'Decryption in Progress'
    when '6' then 'Protection Change in Progress'
    end as [Encryption Status] ,
    key_algorithm as [Key Algorithm],
    Name as [Cert Name],
    pvt_key_encryption_type_desc as [Pvt Key Desc],
    [subject] as [Subject],
    [expiry_date] as [Expiry Date],
    [start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: