TDE(Transparent Data Encryption) On SQL Server

TDE is used to encrypt the database. With TDE, Data and Log files are encrypted at the page level in real time and there is no interruption in applications.

I guess the most curious question about TDE is this: “Can a DBA or another user who is authorized on instace can see this database when the database is encrypted by using TDE?”.

Yes, he can. The purpose of TDE is to prevent third parties who are authorized on the database server to open your database on another instance by using the methods of copying database files to another server or retrieving the database’s backup and copying them to another server.

In a TDE-enabled database, the data is encrypted before being written to the disk and the passwords are resolved and transferred to the memory when the data needs to be read. If you don’t want DBAs to see the data, you need to use an application-level encryption method. As an important information, if the database has fulltext index, it will be encrypted after TDE.

In order to create the TDE, let’s perform the following steps step by step;

1. Create Master Key
2. Creating a Certificate Protected by Master Key
3. Create a Database Encryption Key protected by the certificate
4. Set the database to use encryption

Creating Master Key: We create the master key with the help of the following script.

Creating a Certificate Protected by Master Key: We create our certificate with the help of the following script.

Create a Database Encryption Key protected by the Certificate:

Create DEK with the help of the following script.

When we create DEK, it gives us a warning like below.

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.

For this reason, you should definitely take the backups of the certificate and private key.

You can take backups of your certificate and private key with the help of the following script.

Set the database to use encryption: Finally, we activate the encryption with the following script on our database.

After completing these steps, we get the error as follows when we want to take the backup of our AdventureWorks2014 database and restore it to another instance.

Msg 33111, Level 16, State 3, Line 2

Cannot find server certificate with thumbprint ‘0x03F47278CEE248166E63B0E6736EA13CF8ECD9F3’

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

This error is normal. Because we did not create our certificate in this new instance. First we have to create our master key as follows.

Then, using the certificate and private key backup, we create our certificate on the new instance with the help of the following script.

When I ran the script, I got an error like the following.

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

You can find the solution to this error in the article “The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it“.

After the certificate has been created, you will see that the process is completed successfully when you try to restore the database.