Encrypt Database with Transparent Data Encryption (TDE)

If you want to protect your valuable data from unauthorised access you normally use some technique like password protection or by key protection etc. Encryption is the process of protecting data with key or password from unauthorised access so that if data is stolen or hacked then also it is safe and secure. Today we will see how we can protect our valuable data and database with Transparent Data Encryption (TDE) feature available with SQL Server database.

As a database administrator you take database backups regularly and keep the backup copies on storage drive or tape but if those files are stolen or somebody tries to misuse data by restoring it then how will you protect it from cracking or decrypting, to handle such situation we use Transparent Data Encryption before our data is misused.

Basically in Transparent Data Encryption (TDE), it uses Database Encryption Key (DEK) which is secured by a certificate and kept in master database. Database Encryption Key is either protected by certificate or an asymmetric key secured by Extensible Key Management (EKM) handler with the help of Microsoft Cryptographic API (MSCAPI). In TDE data is encrypted using Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) encryption algorithms.

Transparent Data Encryption technique is used at data page level, database encrypts it before writing and it is decrypted at the time of reading. Hence you can observe I/O encryption and decryption of data file and log file, also you may face performance degrade because if you are using any database encryption on database instance with TDE then tempdb database will be encrypted automatically.

When you backup a database without encryption, you will be able to read it’s table content row by row and our main motto is to encrypt those readable contents inside backup and log files.

Now we will see step by step process of Transparent Data Encryption. We have ‘ClientInfo’ database in SQL Server 2012 Enterprise Edition for demonstration purpose.

Step 1:

First of all we have to create a Master Key with some strong password in database as shown below.

--Create Master Key
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'T3stD@8@Pw6';
GO

Step 2:

We have to create a certificate with certificate name, here we have ‘ClientInfo_Cert’ as certificate.

--Create Certificate
CREATE CERTIFICATE ClientInfo_Cert WITH SUBJECT = 'Client Info Certificate';
GO

Step 3:

We need to create database encryption key (DEK) with AES_128 algorithm with respective certificate.

--Create Encryption Key, Encrypted by Server Certificate.
USE ClientInfo;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ClientInfo_Cert;
GO

Once you will execute above query, you will get warning message as given below which is specifying to keep a backup of certificate and private key which we have created.

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.

Step 4:

So, It is very important to have a backup of Certificate and Private Key otherwise you won’t be able to restore your database. As per warning given, we will first take backup by below query and will keep both certificate and key at safe place to use in future.

--Backup Certificate 
USE master 
GO 
BACKUP CERTIFICATE ClientInfo_Cert 
TO FILE = 'C:\Backup\ClientInfo_Cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\Backup\ClientInfo_Cert_Key.pvk', ENCRYPTION BY PASSWORD = 'T3stD@8@Pw6' ) 
GO

Step 5:

Now we will enable encryption on the database ‘ClientInfo’.

--Enable Encryption
ALTER DATABASE ClientInfo
SET ENCRYPTION ON;
GO

Step 6:

You can verify certificate details and encryption key details with sys.certificates and sys.dm_database_encryption_keys as given below.

--Check Certificate Details 
USE master 
GO 
SELECT *FROM sys.certificates WHERE pvt_key_encryption_type <> 'NA' 
GO

--Check Encryption Key Details
USE master
GO
SELECT encryptor_type, key_length, key_algorithm, encryption_state, create_date 
FROM sys.dm_database_encryption_keys
GO

Step 7:

Take full backup of ‘ClientInfo’ database. This database backup will be in encrypted format and secured.

--Full Database Backup with Recovery.
USE master 
GO 
BACKUP DATABASE [ClientInfo] TO DISK = N'C:\Backup\ClientInfo.bak' 
WITH NOFORMAT, NOINIT, NAME = N'ClientInfo-Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Step 8:

Try to restore this database backup file on other instance (MANZOOR\SQL2) and observe the message. You will get error message indicating to provide server certificate because our database backup is encrypted with TDE and to decrypt it you need server certificate and key.

USE master 
GO
RESTORE DATABASE [ClientInfo]
FROM DISK = N'C:\Backup\ClientInfo.bak'

Error:
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x8295323BE77B6C77D0C93D17A0DFE3ECC5B827D2'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Step 9:

To restore database on other instance we have to create master key with new password and new certificate with decryption by original password and after that we will again try to restore database. Kindly refer below.

--Create Master Key on other Instance
USE master 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'B@dP@$$w0r6' 
GO

--Create new certificate with reference to earlier private key and decrypt it by same password.
CREATE CERTIFICATE ClientInfo_Cert2
FROM FILE = 'C:\Backup\ClientInfo_Cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\Backup\ClientInfo_Cert_Key.pvk', DECRYPTION BY PASSWORD = 'T3stD@8@Pw6') ; 
GO

Step 10:

Now try to restore ‘ClientInfo’ database on other instance (MANZOOR\SQL2) and you will be able to restore database successfully as shown below.

USE master 
GO
RESTORE DATABASE [ClientInfo]
FROM DISK = N'C:\Backup\ClientInfo.bak'

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

You may also like

• 

  SQL Server Data Components – How Data is Stored
• 

  Database Level Auditing with SQL Server 2012
• 

  SQL Server Recovery Models Overview – Simple, Full, Bulk-logged
• 

  Identify when Statistics were last Updated in SQL Server Database
• 

  SQL CHECK Constraint
• 

  Different Types of SQL Server Locks