Setting up Transparent Data Encryption

 In a SQL Server instance you can choose to selectively configure TDE (Transparent Data Encryption) on target databases.  So, it doesn't become necessary to have all the databases configured with TDE.

(Similarly, in Oracle you can selectively configure target tablespace(s).)

This is a quick walkthrough.

Step 1 : Create a Master Key, using a password that should be protected

Step 2 :  Create a Certificate (it is automatically protected by the Master Key)

Step 3 : Create a Database Encryption Key for the Target Database

Note : You are immediate presented with a warning that the Certificate has not been backed up.

We shall backup the certificate soon.

Step 4: Turn on Database Encryption

Step 5 : List the Certificates that have been created

The first 7 Certificates are pre-populated by the Instance.  The Certificate that I have created "My_TDE_Certificate" is listed as certificate_id=258 and encrypted by the Master Key

Step 6 : List the Encrypted Databases

This is the explanation of the EncryptionState :

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.)

You would see EncryptionState=3 only after some time for a large database as the "SET ENCRYPTION ON" takes time to run through the whole database.

Step 7 : Backup the Certificate

These two backup files (and the Password) must be protected -- e.g. by running backup to another safe device.

If you ever need to restore the database "MY_HR_DB" to another Server, you will need to configure the Master Key and create the Certificate on that server before you can restore the database :

USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My_123_Secret'
GO


CREATE CERTIFICATE My_TDE_Certificate
FROM FILE = 'D:\SS_Important_Backups\My_TDE_Certificate.cert'
WITH PRIVATE KEY (FILE = 'D:\SS_Important_Backups\My_TDE.key',
DECRYPTION BY PASSWORD 'My_123_Secret');

The database cannot be restored and read on another server unless the Master Key and Certificate are configured on the new server.

To disable (remove) Encryption you can  run :

ALTER DATABASE MY_HR_DB SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a value of 1 in the query below. */

SELECT encryption_state FROM sys.dm_database_encryption_keys;
GO

USE MY_HR_DB;
GO

DROP DATABASE ENCRYPTION KEY;
GO

Again, as with setting up Encryption, disabling it can take time to execute.  Note that older backups of the Database and Transaction Logs would still remain in an Encrypted State.

This is the listing of all the commands in this post :

USE MASTER
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My_123_Secret';

CREATE CERTIFICATE My_TDE_Certificate WITH SUBJECT= ' TDE_Configuration' ;

USE MY_HR_DB
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE My_TDE_Certificate;
GO


USE MASTER
GO

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;



SELECT
DB_NAME(database_id) AS DatabaseName
,Encryption_State AS EncryptionState
,key_algorithm AS Algorithm
,key_length AS KeyLength
FROM sys.dm_database_encryption_keys
GO


BACKUP CERTIFICATE My_TDE_Certificate 
TO FILE = 'D:\SS_Important_Backups\My_TDE_Certificate.cert'
WITH PRIVATE KEY (file='D:\SS_Important_Backups\My_TDE.key',
ENCRYPTION BY PASSWORD = 'My_123_Secret')



USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My_123_Secret'
GO


CREATE CERTIFICATE My_TDE_Certificate
FROM FILE = 'D:\SS_Important_Backups\My_TDE_Certificate.cert'
WITH PRIVATE KEY (FILE = 'D:\SS_Important_Backups\My_TDE.key',
DECRYPTION BY PASSWORD 'My_123_Secret');




ALTER DATABASE MY_HR_DB SET ENCRYPTION OFF;
GO
/* Wait for decryption operation to complete, look for a value of 1 in the query below. */

SELECT encryption_state FROM sys.dm_database_encryption_keys;
GO

USE MY_HR_DB;
GO

DROP DATABASE ENCRYPTION KEY;
GO

As noted earlier, an Instance can have a mix of Encrypted (TDE) and Non-Encrypted Databases. TDE does "encryption at rest" -- meaning that the  files on the OS layer (Database, Transaction Log and Backups of both) are encrypted.