Setting up Transparent Data Encryption (TDE) Part 2 of 3

Category : Articles Published : May 20, 2016 User Rating : 5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

Summary

Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created from the database. Encrypting a database with TDE is a very straightforward process, involving 3 simple steps. This article shows how to do this.

This is the second article in a series of three around TDE.

ENCRYPTING A DATABASE WITH TDE IN 3 STEPS

STEP 1 : Set up an instance level master key and certificate

The first step is to set up a master key by running the following SQL, but make sure you change the password to something more secure. If you already have a master key set up on the instance then you don’t need to run this (you may well already have one as it’s used by other encryption functions).

USE Master;
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyPassword';
Then we need to create a certificate at the instance level by running the following SQL :
USE Master;
CREATE CERTIFICATE MyInstance_ServerCert
WITH SUBJECT='Certificate for MyInstance';

STEP 2 : Set up a database encryption key within the database to be encrypted

To set up a database encryption key run the following SQL in the database to be encrypted (in this example the database is called TDETest). Note that this uses the server certificate created in the previous step (I called it MyInstance_ServerCert) :
USE TDETest;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyInstance_ServerCert;

STEP 3 : Enable TDE for the database

To switch on encryption for a database run the following SQL :
ALTER DATABASE TDETest SET ENCRYPTION ON;
That’s it ! The database files should now be encrypted.

MORE INFORMATION

I’ve briefly described how to set up TDE, but will now discuss how to verify TDE, check on encryption progress and backup certificates and other matters relating to TDE.

Verifying whether a Database is Encrypted

You can check whether a database is encrypted either by looking at the database properties (right click on the database in SQL Server Management Studio, select Properties and then click on the Options page), and looking at the “Encryption Enabled” setting as below : How to verify TDE encryption status

Alternatively a list of all the encrypted databases on an instance can be obtained by running the following query :
SELECT    name,DEK.*
FROM      sys.databases D
JOIN      sys.dm_database_encryption_keys DEK
ON        DEK.database_id = D.database_id
ORDER BY  name;
On my server I have just the one encrypted databases so get the following results from this query (note that as there is at least one encrypted DB then tempdb is encrypted) : TDE Query Result

Note the encryption_state column. The value of 3 indicates that the database is encrypted, a value of 2 indicates that encryption is in progress (a newly encrypted database is encrypted in the background, which may take a while for larger databases).

Backing Up Certificates

It’s important to back up the certificate and keep this safe, as this will be needed if you want to restore the database onto another server. To backup the certificate use the ‘Backup Certificate’ command, for instance I ran :
USE Master;
BACKUP CERTIFICATE MyInstance_ServerCert
TO FILE = 'D:\SQL2012_SQLCert.cer'
WITH PRIVATE KEY (FILE='D:\SQL2012_MasterKey.pvk',
                  ENCRYPTION BY PASSWORD='D8LsiBL62mLPhi0z7CoM');

Removing Encryption from a Database

If encryption is no longer required then it can be removed along with the database encryption key by running the following commands (obviously you’d need to change the database name) :
-- Remove encryption
USE TDETest;
ALTER DATABASE TDETest SET ENCRYPTION OFF;
GO

-- Remove DEK
DROP DATABASE ENCRYPTION KEY;
You may need to leave a gap between running these statements as TDE is removed asynchronously in the background, and needs to be removed before the DEK can be dropped. Also note that tempdb will remain encrypted even if encryption is removed from all databases on the instance. However if the instance is restarted then tempdb will be recreated without encryption.

Restoring Encrypted Backups to another Server

If you are restoring a database backup to the same instance (either overwriting the same database or as a new database) then this can be done in the same way as a non-encrypted database. The restored database will also be encrypted using the same encryption key. However if you try and restore the backup to another instance the chances are that you’ll get a ‘Cannot find server certificate with thumbprint’ error. This is because the new server needs the certificate from the original server. To create the certificate that we backed up in the section ‘Backing Up Certificates’ above we can just run the statement below on the new server :
CREATE CERTIFICATE MyServer_SQL2012_SQLCert
FROM FILE = 'D:\SQL2012_SQLCert.cer'
WITH PRIVATE KEY (FILE='D:\SQL2012_MasterKey.pvk',
                  DECRYPTION BY PASSWORD='D8LsiBL62mLPhi0z7CoM');
Note that the new server will need to have a master key set up in order to encrypt the certificate, however the password does not need to be the same as on the original server. If you don’t have a master key setup then this can be done using the following statement (but best to use a different password) :
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyPassword';
In summary if you are restoring the encrypted database to another instance you will need the certificate and its private key as well as the backup file.

Checking What Certificates Exist

You can check which certificates exist using the following query :
SELECT * FROM sys.certificates;
Link back to this article : https://www.sqlmatters.com/Articles/Setting up Transparent Data Encryption (TDE).aspx

Keywords

SQL 2012, 2008R2, 2014, Security, Encryption, TDE


Comments
Post by Jai on Wed 04 Jul 2018 14:53. Report Inappropriate Post

With respect to the instruction -
"
USE Master;
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyPassword';
"
Assume that I have created the Master Key with a strong password to implement TDE. But after a year, now I would like to change the password. Please note I am talking about the master key created in master DB with above statement.
Q1. How can we change the password of above key
Q2. Will I need to perform encryption of all DBs again?
Q3. What other additional steps I would need to perform after changing the password
Q4. If the activity is seamless, i.e. no additional steps are required, then, is it a resource-intensive operation? Because, the database instance is having many large DBs.
Q5. Will it "re-encrypt" certificate and/or database encryption key of a DB and/or all MDF/LDF files?
Excuses for redundancy in the questions. :)

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered