In my previous article we have discussed what is TDE, how it works, its pros and cons. In this article Im going to cover how to implement transparent data encryption to your server. Its very simple to implement TDE, below are the steps that needs to be completed to enable TDE.
- Create a master key
- Create or obtain a certificate protected by the master key
- Create a database encryption key and protect it by the certificate
- Set the database to use encryption
Lets discuss on each of this title to create TDE
Create a master key
In this first step Im going to create a master key with the password TDEtesting123. If you are already having a master key in the master database proceed to the next step. Please note that without this master key you wont be able to create a certificate.
USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEtesting123' GO
Create or obtain a certificate protected by the master key
In this step Im going to create a certificate in master database using the command below. Im going to create a certificate named TDE_testing in master database. Please note that if you are already having a certificate in master database you can skip this step and you can use that certificate name in the consecutive steps.
USE master GO CREATE CERTIFICATE TDE_testing WITH SUBJECT = 'TDE Certificate' GO
If you want to check whether the certificate is created, you can query sys.certificates catalog view. All the certificates in a database can be retrieved using this view, now you should be able to view the created certificate.
Create a database encryption key and protect it by the certificate
In this Im going to create database encryption with the help of certificate created in the previous step. Im going to use AES algorithm for encrypting the database. Im going to use the database TDE_testing
USE TDE_Testing GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE TDE_testing GO
Set the database to use encryption
We have reached to the final step in TDE, If all the steps above is successful then we need to enable the database to use encryption.
ALTER DATABASE TDE_Testing SET ENCRYPTION ON GO
Thats it TDE_Testing database is now encrypted with TDE.
To confirm whether the database is set to encryption you can use the query below, if it returns 1 then the database is encrypted else its not encrypted.
SELECT name,is_encrypted FROM sys.databases WHERE name='TDE_Testing'
To know the encryption status you can use the query below, Ive used a where condition on encryption_state column for 3, Where 3 represents an encrypted state on the database and transaction logs.
USE TDE_Testing GO SELECT DB_NAME(database_id),* FROM sys.dm_database_encryption_keys WHERE encryption_state = 3 GO
As I mentioned in my previous article on TDE that tempdb will also be encrypted if any of the database participates in TDE. You can find from the output above that tempdb is also in encrypted state, however when you query in sys.databases you cant find value 1 on column is_encrypted for tempdb database.
Backup Certificate & Private Encryption Key
Ok now its time to backup the certificate created in master because the certificate is very important because without this certificate you cant restore attach the database to any of the SQL Server 2008 or you cant recover the database in case of failure from backup. So Please take a backup of the certificate created in master database and keep it in a safe location.
In the above step 3 (Create a database encryption key and protect it by the certificate) if you create the database encryption key without backing up the certificate you will be thrown with a warning as shown 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.
So the next most important step is to backup the certificate, lets discuss how to backup the certificate. Im going to backup the certificate TDE_testing created in step 2 and the private key, you can modify the query below to backup your certificate.
USE master GO BACKUP CERTIFICATE TDE_testing TO FILE = 'c:\TDE_testing_cert_backup' WITH PRIVATE KEY ( FILE = 'c:\TDE_testing_key' , ENCRYPTION BY PASSWORD = 'TDEtesting123' ) GO
You can find from the below screenshot that the certificate and its private key are backed up successfully. Copy and keep this backup in a safe location.
TDE is a nice security feature available in SQL 2008 and its very easy to implement too. If you enable TDE for a database then its your responsibility to take backup of the certificate created, because without that certificate you cant do anything with the physical files of the database.