How to configure and remove Transparent Data Encryption in SQL SERVER.

Transparent Data Encryption (TDE) is concept of encrypting data and log files of a database. This encryption is transparent to user, as data gets stored in encrypted format on disks and when user retrieves the data it gets decrypted and shown. More information related to TDE can be found in these links 1, 2.

In this post I will be providing step by step commands to use for enabling TDE in a database/instance and step by step commands for removal of TDE from the database/instance.

Please read about TDE fully before using this feature as improper maintenance of CERTIFICATES or KEYS can lead to data loss or database un-availability.

How to enable Transparent Data Encryption for a database?

Step:1 CREATION OF MASTER KEY

While enabling/configuring TDE first we need to create a MASTER KEY in master database of the server using below command.

USE master

Go

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'pa$$w0rd';

Go

Step:2 CREATION OF CERTIFICATE

We need to create a CERTIFICATE  in master database using below command.

USE master

Go

CREATE CERTIFICATE TestCert

WITH SUBJECT = 'TDE Test Certificate'

Step:3 BACKUP CERTIFICATE with PRIVATE KEY

Once we create certificate the immediate step should be to take backup of the certificate as recommended by Microsoft.

Note: If this CERTIFICATE OR KEY are lost then the database cannot be restored to any other server.

USE master

GO

BACKUP CERTIFICATE TestCert

TO FILE = 'C:\Temp\ TestCert.cer'

WITH PRIVATE KEY (FILE = 'C:\Temp\ TestCert.pvk' ,

ENCRYPTION BY PASSWORD = 'pa$$w0rd' )

Once above command completes successfully we can see 2 files created in the location ‘C:\Temp’. Whenever we want to restore the database in other instance/server we need to copy these 2 files must, if not restore fails.

Step:4   CREATE DATABASE ENCRYPTION KEY

Next create a database encryption key based on the server certificate we created in previous step, use below command

USE [Test]

Go

  CREATE DATABASE ENCRYPTION KEY

    WITH ALGORITHM = AES_128

    ENCRYPTION BY SERVER CERTIFICATE TestCert;

GO

Step:5 ENABLE ENCRYPTION at database level

Next we need to turn on encryption at database level using below command.

USE [Test]

Go

ALTER DATABASE [Test] SET ENCRYPTION ON

We can use below 2 system tables to verify encryption status of each database and the certificates available in the instance.

                  sys.dm_database_encryption_keys

                       sys.certificates

Once we enable TDE on any of the database of instance automatically tempdb gets encrypted.

How to remove Transparent Data Encryption completely.

Step:1 DISABLE ENCRYPTION at database level.

First step is to disable database encryption using below command.

USE [Test]

Go

ALTER DATABASE [Test] SET ENCRYPTION ON

GO

Step:2 DROP DATABASE ENCRYPTION KEY

Now drop the database encryption key using below command.

USE [Test]

Go

DROP DATABASE ENCRYPTION KEY

Step:3 DROP CERTIFICATE from master database

Now drop the server level certificate using below command.

USE master

Go

DROP CERTIFICATE TestCert;

Go

Step:4  DROP MASTER KEY from master database

Now drop the server level master key using below command.

USE master

Go

DROP MASTER KEY;

In order to remove TDE completely we need to restart the instance after performing all the above 4 steps.

To verify the status of database encryption on each database we can use below system tables:

                   sys.dm_database_encryption_keys

                   sys.certificates

Thanks!!