TDE(Transparent Data Encryption) On SQL Server

dbtut

6 years ago

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;

Create Master Key
Creating a Certificate Protected by Master Key
Create a Database Encryption Key protected by the certificate
Set the database to use encryption

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

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'
GO

USE [master]

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'

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

USE [master]
GO
CREATE CERTIFICATE Sertifikam WITH SUBJECT = 'MyCertificate'; 
GO

USE [master]

CREATE CERTIFICATE Sertifikam WITH SUBJECT = 'MyCertificate';

Create a Database Encryption Key protected by the Certificate:

Create DEK with the help of the following script.

USE AdventureWorks2014; 
GO 
CREATE DATABASE ENCRYPTION KEY 
WITH ALGORITHM = AES_128 
ENCRYPTION BY SERVER CERTIFICATE MyCertificate; 
GO

USE AdventureWorks2014;

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MyCertificate;

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.

USE master
GO
BACKUP CERTIFICATE MyCertificate TO FILE = 'C:\Backup\MyCertificate' 
    WITH PRIVATE KEY ( FILE = 'C:\Backup\MyPrivateKey' ,  
    ENCRYPTION BY PASSWORD = 'TDE123' ); 
GO

USE master

BACKUP CERTIFICATE MyCertificate TO FILE = 'C:\Backup\MyCertificate'

WITH PRIVATE KEY ( FILE = 'C:\Backup\MyPrivateKey' ,

ENCRYPTION BY PASSWORD = 'TDE123' );

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

ALTER DATABASE AdventureWorks2014
SET ENCRYPTION ON;
GO

ALTER DATABASE AdventureWorks2014

SET ENCRYPTION ON;

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.

USE [master]
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'
GO

USE [master]

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDE123'

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

Use [master]
CREATE CERTIFICATE Sertifikam
    FROM FILE = 'C:\Backup\Sertifikam'
    WITH PRIVATE KEY ( FILE = 'C:\Backup\PrivateKeyim' ,
    DECRYPTION BY PASSWORD = 'TDE123' );
GO

Use [master]

CREATE CERTIFICATE Sertifikam

FROM FILE = 'C:\Backup\Sertifikam'

WITH PRIVATE KEY ( FILE = 'C:\Backup\PrivateKeyim' ,

DECRYPTION BY PASSWORD = 'TDE123' );

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.