Restoring Transparent Data Encryption (TDE) enabled databases on a different server

In this article, we will review how to enable Transparent Data Encryption (TDE) on a database in SQL Server and move the Transparent Data Encryption (TDE) enabled databases to a different server by restoring the backup.

Transparent Data encryption (TDE) encrypts data at rest i.e. data and log files. Encryption is done at page level on the database file. When Transparent Data encryption (TDE) is enabled on a database, it reads the page from the data files to buffer pool, encrypts the page and writes back to disk.

Enabling Transparent Data Encryption (TDE) on database

Enabling TDE on a database involves following steps.

Creating a master key
Creating a certificate in the master database
Creating database encryption key (DEK)
Enable encryption on the database

Let us go through these steps one by one.

Creating a master key

We need to create a master key in the master database. creating a master key is performed at the master database level. Execute the following T-SQL script which creates a master key in the master database. Replace it with stong password of yours. This database master key is encrypted by service master key at instance level which is created at the time of SQL Server instance setup.

USE master

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'SQLshackDemo@*';

Creating a certificate in the master database

The second step in enabling Transparent Data Encryption (TDE) is creating a certificate in the master database. Once we create a master key, we must create a certificate which is protected by the database master key created in the above step. Execute the following T-SQL script to create a certificate in the master database.

MyProduct_Cert is the name of the certificate. You can input the name and the subject of your choice.

CREATE CERTIFICATE MyProduct_Cert

WITH SUBJECT = 'Used to encrypt MyProduct Database';

Creating database encryption key (DEK)

Once the certificate is created in the master database, we must create database encryption key (DEK) which is encrypted by the certificate created in the above step. Creation of database encryption key is performed at the user database. Execute the following T-SQL script which creates database encryption key (DEK) in the database called MyProductsDB. Replace the database name with yours.

USE MyProductDB

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE MyProduct_Cert;

Enable encryption on the database

Once the database encryption key (DEK) is created, we must enable transparent data encryption (TDE) on the database. Execute the following T-SQL script by replacing the database name which you are going to encrypt.

ALTER DATABASE MyProductDB

SET ENCRYPTION ON;

We can also enable encryption by setting the value to true for the option “Enabled Encryption” in the database properties.

We can see the status of the encryption using the dynamic management view “sys.dm_database_encryption_keys”. Please use the below script to know the encryption status.

SELECT DB_NAME(database_id)

,encryption_state = CASE

WHEN encryption_state = 1

THEN 'Unencrypted'

WHEN encryption_state = 2

THEN 'Encryption in progress'

WHEN encryption_state = 3

THEN 'Encrypted'

WHEN encryption_state = 4

THEN 'Key change in progress'

WHEN encryption_state = 5

THEN 'Decryption in progress'

WHEN encryption_state = 6

THEN 'Protection change in progress'

WHEN encryption_state = 0

THEN 'No database encryption key present, no encryption'

END

,create_date

,encryptor_type

FROM sys.dm_database_encryption_keys

Please refer to the below image. We can see the database MyProductDB is encrypted along with tempdb. The system database tempdb will be automatically encrypted if enable encryption for at least one database in the instance.

Restoring Transparent Data encryption (TDE) enabled database backup to a different server

When the database is enabled for transparent data encryption, the database backup files are also encrypted. If we try to restore a TDE enabled database backup on a different server it throws error “Cannot find server certificate with thumbprint”.

We need the certificate which was used to encrypt the database to restore the backup on a different server.

Following are the steps involved in restoring Transparent Data encryption (TDE) enabled database.

Backup the certificate on the source server
Copy the backup file and create a certificate from the file
Restore the database backup

We will go through these steps one by one.

Backup the certificate on the source server

First, we must back up the certificate that was used to encrypt the database. Execute the following T-SQL script to create the certificate backup and the private key file in the mentioned path. MyProduct_Cert is the name of the certificate. Replace the name of the certificate with yours.

BACKUP CERTIFICATE MyProduct_Cert

TO FILE = '/var/opt/mssql/data/MyProduct_Cert.cer'

WITH PRIVATE KEY

(

FILE = '/var/opt/mssql/data/MyProduct_Cert.pvk',

ENCRYPTION BY PASSWORD = 'SQLshackDemo@*Backup'

);

Creating the certificate from the file

Copy the backup file and the private key file to the server where you are going to restore the Transparent data encryption (TDE) enabled database backup.

Check if you have a master key on the master database already, create one if you do not have it. In this case, I do not have the master database key on the destination server.

Execute the following script on the destination server to create the master key. replace it with the password of your choice.

USE master

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'SQLshackDemo@*Destination';

Once the master key is created, restore the certificate using backup file and the private key. Execute the following T-SQL script to restore the certificate from the backup file. Please note that the password should be the same which was used to back up the certificate.

USE master;

CREATE CERTIFICATE TDECert

FROM FILE = '/var/opt/mssql/data/MyProduct_Cert.cer'

WITH PRIVATE KEY (

FILE = N'/var/opt/mssql/data/MyProduct_Cert.pvk',

DECRYPTION BY PASSWORD = 'SQLshackDemo@*Backup'

);

Now let us restore the TDE enabled database backup on the destination server. Please refer to the below image that shows the restore backup is successful after restoring the certificate that is used to create the database encryption key.

Conclusion

In this article, we explored how to enable Transparent Data Encryption (TDE) on a database in SQL Server and move the Transparent Data Encryption (TDE) enabled databases to a different server by restoring the backup. In case you have any questions, please feel free to ask in the comment section below.

To continue your learning about Transparent Data Encryption (TDE), please refer to the Transparent Data Encryption category.

Author
Recent Posts

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies