Ranga Babu
enabling Transparent Data Encryption (TDE) using SSMS

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

September 11, 2019 by

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.

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.

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.

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.

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

enabling Transparent Data Encryption (TDE) using SSMS

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.

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.

TDE status

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”.

Restoring Transparent Data Encryption (TDE) enabled database

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.

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.

service master key

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

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.

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.

restore database

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.

Ranga Babu

Ranga Babu

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

Latest posts by Ranga Babu (see all)

425 Views