In our first article we had discussed security and privacy of data in cloud and how to meet MSSQL server HIPPA(Health Information Portability and Accountability Act) compliance. We had also learn how to encrypt data in transit between MSSQL server and applications. Here you will learn how to encrypt data in rest in MSSQL using Transparent Data Encryption(TDE).
It seems that most of the security products focused their effort on securing data in transit only, using SSL/TLS. But when you build a security system that meet the international security regulations and acts, you need to encrypt data in rest also. The insecure configuration of server, operating system, firewall and network in general, will make it easier for some one to gain access to data at rest.
TDE performs real time encryption and decryption of data while writing and reading data from MSSQL. As the name implies encryption is transparent, that is no need to modify code or architecture of applications when implementing TDE.
Implementation of TDE
1. Create a master key
A master key is a symmetric key that is used to create certificates and asymmetric keys.
[sql]
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘<password>’;
GO
[/sql]
2.Create Certificate
Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly. Execute the following script to create a certificate:
[sql]
CREATE CERTIFICATE TDECert
WITH SUBJECT = ‘TDECertificate’
GO
[/sql]
3.Create a Database Encryption Key and Protect it by the Certificate
1.Click object explorer in the left pane of the MSSQL SERVER Management Studio
2.Right Click on the database which you want to encrypt
3.Click Tasks and Navigate to Manage Database Encryption
4. Select the encrytion algorithm (AES 128/192/256) and select the certificate you have created
5.Then Mark the check Box for Set Database Encryption On
You can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.
[sql]
SELECT [name], is_encrypted FROM sys.databases
GO
[/sql]
Important Back up the certifi cate and private key to a fi le to ensure recoverability as follows
[sql]
BACKUP CERTIFICATE TDECert TO FILE = ‘C:\TDECert.cer’
[/sql]
You are Done!!
