Transparent Data Encryption Implementation And Moving TDE Database to another instance

Configuring TDE should go through 4 steps.

Create Master Key -> Create Certificate protected from Master Key -> Create database Encryption Key & protect by Certificate -> Enable Encryption on database.

Step 1: Create Master Key

This is mandatory step. Without this Master Key you won’t be able to create Certificate.

USE Master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEkey123'

GO

Step 2: Create Certificate protected from Master Key.

USE Master

GO

CREATE CERTIFICATE TDE_Demo WITH SUBJECT = 'TDE Certificate'

GO

If you are already having a Certificate, you can check using sys.certificates view.

Step 3: Create database Encryption Key & protect by Certificate

Here we are going to use AES algorithm for encrypting database.

USE TDE_DEMO

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TDE_Demo

GO

Once you execute the above T-SQL, it will ask to back up the encryption key

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Step 4: Enable Encryption on database

ALTER DATABASE TDE_DEMO

SET ENCRYPTION ON

GO

Query to check whether database is encrypted or not.

SELECT name,is_encrypted FROM sys.databases WHERE name='TDE_DEMO'

Backup the certificate as discussed in Step 3.

USE Master

GO

BACKUP CERTIFICATE TDE_Demo TO FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_Certificate_backup'

WITH PRIVATE KEY (FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_key' ,

ENCRYPTION BY PASSWORD = 'TDEdemo123')

GO

  

  

Note: Please make sure to take backup of the certificate, because without that certificate you can’t do anything with the physical files of the database.

Moving TDE enabled database.

In order to move TDE enabled database you need to create certificate in destination instance from the backup of certificate we create while implementing in source instance.

Copy the Certificate and Private key file to destination server.

USE Master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEdemo123'

GO

CREATE CERTIFICATE TDE_Demo

    FROM FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_Certificate_backup'

    WITH PRIVATE KEY (FILE = 'C:\SQL_2K14\S\TDE_Encryption\TDE_DEMO_key',

    DECRYPTION BY PASSWORD = 'TDEdemo123');

GO

All the above paths and passwords are taken from source instance. You can find them in above TDE implementation article.

Now you can move TDE enabled database from source to destination.

You can use normal Backup and restore method or Attach and Detach method. Without creating Encryption key and certificate in destination you can move the TDE database.