The following walks through:

 

• Setting up Transparent Data Encryption (TDE) for a database
• Restoring a TDE database
• Removing TDE from a database
• Removing TDE created objects

 

Note: This feature is currently only available with SQL Server Enterprise Edition, so you may never get to use it.

 
If this is your first TDE setup on a SQL Server it is best that you set aside 4 strong passwords for the various objects we will interact with through this example e.g.

 

• Service Master Key backup file: br_y2s6tR?3taket?3@uvUk6
• Database Master Key: X2t$Ay&7rapHA!?jE3uW3taJ
• Database Master Key backup file: f3av#vamEVemesaMuWr7sWuP
• Certificate Private Key backup file: te5pepUr*n$PhexE2!a2r@#r

 
Note: The only password above you will always require to restore a TDE database on another instance is the Certificate Private Key backup file password. I’m covering the others as you never know when you may need them.

 
If you already have TDE databases on your instance you will likely not need to create any further passwords.

 

Setting up Transparent Data Encryption (TDE) for a database

 

Firstly it is good to know that TDE typically relies on:

 

• a Database Encryption Key (one key is created in each TDE database) and is associated with;
• a Certificate (only one is created for the whole instance and exists in the master database), this Certificate is associated with;
• a Database Master Key (only one is created for the whole instance and exists in the master database) and this Database Master Key relies on;
• a Service Master Key (also only one for the whole instance and it exists in the master database).

 
Knowing the above and as a best practice, let’s make sure we backup the Service Master Key which I suspect you may never have done before.
Every SQL Server instance has a Service Master Key as shown below:

SELECT * FROM master.sys.symmetric_keys;

To backup the Service Master Key you would use:

BACKUP SERVICE MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_service_master_key'
ENCRYPTION BY PASSWORD = 'br_y2s6tR?3taket?3@uvUk6';

Next we will check if a Database Master Key exists in the master database using the following code and as shown below it does not exist:

SELECT * FROM master.sys.symmetric_keys;

If you have an entry called ##MS_DatabaseMasterKey## you can skip this step.
 

As in this case it does not exist we will create it using the following and we can now see the Database Master Key in the master database:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'X2t$Ay&7rapHA!?jE3uW3taJ';
SELECT * FROM master.sys.symmetric_keys;

 

Now we know it exists and as best practice let’s make a backup of the key:

USE master;
BACKUP MASTER KEY TO FILE = 'C:\SQLBackups\TDE\SQL01D_master_key'
ENCRYPTION BY PASSWORD = 'f3av#vamEVemesaMuWr7sWuP';

 

Now to create the TDE Certificate in the master database which will be associated with the Database Master Key:

USE master;
CREATE CERTIFICATE SQL01D_Certificate WITH SUBJECT = 'SQL01D Certificate', EXPIRY_DATE = '20200131';
SELECT * FROM master.sys.certificates;

 

As this Certificate is typically the only object, besides the TDE database, you will need to recover on another instance, you will need to definitely create a backup of it and store it safely for future use.

 

As shown below this will create 2 files and you will require them both to restore the Certificate:

USE master;
BACKUP CERTIFICATE SQL01D_Certificate TO FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.cer'
WITH PRIVATE KEY ( FILE = 'C:\SQLBackups\TDE\SQL01D_certificate.pvk'
,ENCRYPTION BY PASSWORD = 'te5pepUr*n$PhexE2!a2r@#r');

 

Now to create a Database Encryption Key for our database which will use TDE:

USE AdventureWorks2014;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SQL01D_Certificate;

SELECT DB_NAME(database_id) AS database_name, encryption_state 
FROM sys.dm_database_encryption_keys; -- encryption_state should say 1 (Unencrypted)

 

Finally we enable TDE encryption in the database. As shown below, the tempdb database will also be encrypted automatically:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION ON;

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 

For large databases in may take some time for the database to become fully encrypted. The progress can be checked with:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 

Restoring a TDE database

 

Now you have a TDE enabled database and the time arrives to restore the database onto another instance.

 

You are going to need 4 things to restore your database:

• The database backup file(s) to restore the database
• The Certificate cer file
• The Certificate pvk file
• The password used to encrypt the Certificate Private Key backup file

 

Firstly check that the Database Master Key exists in the master database.

 

As below, only a Service Master Key exists so we will need to create a Database Master Key:

SELECT * FROM master.sys.symmetric_keys;

 

If you have a Database Master Key you can skip this step. The password should be strong and backup the Database Master Key once created as previously demonstrated. The password does not have to be same as the original server.

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'BanetrAje&$ATReMuspA*U2e';
SELECT * FROM master.sys.symmetric_keys;

 

Copy the Certificate cer and pvk files to the location which holds your database mdf files. You could place these files anywhere your SQL Server can access them, but this location should typically always work.

 

Now restore the Certificate:

USE master;
CREATE CERTIFICATE SQL01D_Certificate
FROM FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.cer'
WITH PRIVATE KEY(FILE='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQL01D_certificate.pvk'
,DECRYPTION BY PASSWORD='te5pepUr*n$PhexE2!a2r@#r');

SELECT * FROM master.sys.certificates;

 

Finally the TDE database can be safely restored and TDE will still be in use:

-- encryption_state 1 = Unencrypted, 2 = Encryption in progress, 3 = Encrypted
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

Removing TDE from a database

For whatever reason, you now need to remove TDE from a database.

Firstly, disable encryption on the database:

USE AdventureWorks2014;
ALTER DATABASE AdventureWorks2014 SET ENCRYPTION OFF;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 

This may take some time and you can continue once the database has an encryption state of 1 (Unencrypted).

USE AdventureWorks2014;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 

Once the database is fully decrypted you lastly need to remove the Database Encryption Key using:

USE AdventureWorks2014;
DROP DATABASE ENCRYPTION KEY;

-- encryption_state 1 = Unencrypted, 3 = Encrypted, 5 = Decryption in progress
SELECT DB_NAME(database_id) AS database_name, encryption_state, percent_complete 
FROM sys.dm_database_encryption_keys;

 

Once TDE has been removed from the last database, you will notice that tempdb is still encrypted.The tempdb database will only be decrypted when the SQL Server instance is restarted.

Removing TDE created objects

 

To tidy up a SQL Server instance once TDE is no longer be used, the following steps are used.

 

Note: Ensure you have a secure copy of any created TDE Certificate(s) in case you need to restore a previously TDE encrypted backup. Backups were covered previous.

 

First identify the Certificate to remove using:

SELECT * FROM master.sys.certificates;

 

Remove the Certificate using:

USE master;
DROP CERTIFICATE SQL01D_Certificate;
SELECT * FROM master.sys.certificates;

 

Finally identify and drop the Database Master Key if you know it is no longer used.

Below shows the Database Master Key exists in the master database:

SELECT * FROM master.sys.symmetric_keys;

 

 
The following removes the Database Master Key and leaves the Server Master Key which remains on the instance:

USE master;
DROP MASTER KEY;
SELECT * FROM master.sys.symmetric_keys;