Encrypting SQL Server 2012: Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the ‘data at rest’. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files to another server. TDE requires planning but can be implemented without changing the database. Robert Sheldon explains how to implement TDE.

Below is a quick visualization of the hierarchy

This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. It does this by using either Advanced Encryption Standard (AES), or Triple DES, encrypting the file pages and then decrypted as the information goes into memory. This inhibits limitations from querying the data in an encrypted database. This is essentially real time I/O encryption and decryption and does not increase the size of said database.

Also note, that as a result of TDE, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords.

Also, the TempDB database will be automatically encrypted. Since the tempdb is used by all user databases (processing/storing temporary objects). You shouldn’t notice much of a difference in how TDE operates, but this is good to know and often overlooked. What good is an encrypted database if the data placed in TempDB isn’t encrypted?

TDE Hierarchy

Now let’s have a quick overview of the TDE architecture and hierarchy. First we have the Windows Operating System Level Data Protection API, which decrypts the Service Master Key found in the SQL Server instance level. The Server Master Key is created at the time of the initial SQL Server instance setup. From there we go the database level. The Service Master Key encrypts the database Master Key for the master database. The database master key creates a certificate in the master database. Keep in mind that you must create a backup of this certificate. Not only for environmental refreshes but disaster recovery purposes. Once TDE is enabled on the database you won’t be able to restore or move it another server unless this same certificate has been installed. Keep good (and secure records) of the certificate and password.

The certificate is then used to enable encryption at the database level, thus creating the database encryption key.

To help visualize this process, please refer to the following diagram:

 

 

Implementation:

Create Master Key

We must first create the master key. It must be created in the master database, so as a precautionary measure I like to begin this statement with the USE MASTER command.

Create Database Encryption Key

Now, we must utilize our USE command to switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

Enable Encryption

Finally, we can enable encryption on our database by using the ALTER DATABASE command.

Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

 

— THIS QUERY WILL TELL WHAT DATABASE IS ENCRYPTED
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

Screen shot below will show you the database name and is_encrypted=1

 

Meaning of the Encryption State

Indicates whether the database is encrypted or not encrypted.

0 = No database encryption key present, no encryption

1 = Unencrypted

2 = Encryption in progress

3 = Encrypted

4 = Key change in progress

5 = Decryption in progress

6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-database-encryption-keys-transact-sql

Restoring a Certificate

In order to restore the certificate, you will once again have to create a service master key on the secondary server.

Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

Be mindful of the paths used in this example. You must specify the path that you have stored the certificate and private key. Also keep good and secure records of the encryption passwords.

Once the certificate is restored to the secondary server you may restore a copy of the encrypted database.

Some things to note before applying TDE. There are some drawbacks. Remember that TDE encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption. If that is the type of encryption you are looking for, you should investigate column level encryption.