Transparent Data Encryption is a encryption method for encrypting database files at rest. This method is native to the DB engine and is one of the most best methods to encrypt your data files to ensure performance doesn’t take a hit. SQL Server and Oracle both support TDE natively on its database engine.

Now let us go ahead, create a database and encrypt it….

1. Create a Test Database

CREATE DATABASE [SIMPLESQLDBA]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N’SIMPLESQLDBA’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SIMPLESQLDBA\MSSQL\DATA\SIMPLESQLDBA.mdf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N’SIMPLESQLDBA_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL13.SIMPLESQLDBA\MSSQL\DATA\SIMPLESQLDBA_log.ldf’ , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

 

2. Create Master Key

Create a master key in the master database.

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD=’Abc1234$#’;
GO

3. Create Certificate protected by master key

Create the certificate which is protected by the master key

CREATE CERTIFICATE SIMPLESQLDBA_TDE_Cert
WITH
SUBJECT=’SQL_Server_TDE_DB_Encryption’;
GO

The certificate’s name is SIMPLESQLDBA_TDE_Cert

 

4. Create Database Encryption Key

Now we will create the actual encryption key for the database we need to encrypt

USE SIMPLESQLDBA
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE SIMPLESQLDBA_TDE_Cert ;
GO

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.

5. Encrypt the Database

ALTER DATABASE SIMPLESQLDBA
SET ENCRYPTION ON;
GO

 

6. Backup Certificate

It is aleways good practise to backup your encryption certificates and store it in a safe location, in case the server crashes and you have to restore
your database to another server. Create a new folder called ‘certificates’ in c:\ directory and give read-write permissions to SQL Server service account
This way you will avoid below error:
“Msg 15240, Level 16, State 1, Line 34”

 

USE master
GO
BACKUP CERTIFICATE SIMPLESQLDBA_TDE_Cert
TO FILE = ‘C:\certificates\SIMPLESQLDBA_TDE_Cert’
WITH PRIVATE KEY (file=’C:\certificates\SIMPLESQLDBA_TDE_Cert.pvk’,
ENCRYPTION BY PASSWORD=’Abc1234$#’)
GO

 

7. Restore Certificate

Copy the certificates onto the new server and create folder c:\certificates

Create the master key from SSMS

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD=’Abc1234$#’;
GO

Restore the certificate (make sure the password is same as the one when you backed up the certificate)

USE MASTER
GO
CREATE CERTIFICATE SIMPLESQLDBA_TDE_Cert
FROM FILE = ‘C:\certificates\SIMPLESQLDBA_TDE_Cert’
WITH PRIVATE KEY (FILE = ‘C:\certificates\SIMPLESQLDBA_TDE_Cert.pvk’,
DECRYPTION BY PASSWORD = ‘Abc1234$#’ );

The above method of doing TDE is almost same from SQL Server 2008 till SQL Server 2016

Link to the SQL file with all commands : https://drive.google.com/file/d/11gGiBU9Eln_gp64zZgOV9xvMTzoF10TU/view?usp=sharing