Transparent Data Encryption

        Transparent Data Encryption (often abbreviated to TDE) is a technology employed by both Microsoft and Oracle to encrypt database content. TDE offers encryption at a column, table, and tablespace level. TDE solves the problem of protecting data at rest, encrypting databases both on the hard drive and consequently on backup media. Enterprises typically employ TDE to solve compliance issues such as

PCI DSS.

WHAT IS Payment Card Industry Data Security Standard (PCI DSS) ?

       The Payment Card Industry Data Security Standard (PCI DSS) is a proprietary information security standard for organizations that handle cardholder information for the major debit, credit, prepaid, e-purse, ATM, and POS cards.
Defined by the Payment Card Industry Security Standards Council, the standard was created to increase controls around cardholder data to reduce credit card fraud via its exposure. Validation of compliance is done annually — by an external Qualified Security Assessor (QSA) that creates a Report on Compliance (ROC) for organizations handling large volumes of transactions, or by Self-Assessment Questionnaire (SAQ) for companies handling smaller volumes.

Microsoft SQL Server 2008 provides real time encryption of data and log files.  Data is encrypted before it is written to disk; data is decrypted when it is read from disk.  

      The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it.  There is absolutely no code that needs to be written to perform the encryption and decryption. 

    There are a couple of steps to be performed to prepare the database for TDE, and then the encryption is turned on at the database level via an ALTER DATBASE command.

    To avoid incidents where backup tapes containing sensitive information have been lost or stolen and sensitive information goes into wrong hand, Using TDE feature the backup files can be also encrypted.  We just need to turn on encryption for database no more additional efforts  are needed to generated encrypted backup, the regular BACKUP command itself generate encrypted backup.

The data in the encrypted backup files is completely useless without having access to the key that was used to encrypt the data.

What is mean by term encryption?

It is the process of transforming information using an algorithm to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key

The security provided by encryption is based on the strength of the algorithm and protection of the key. 

Types of keys:

      1) Symmetric: With a symmetric key, the same value is used to encrypt and decrypt the data.

        2)   Asymmetric:   An asymmetric key has two components - a private key and a public key.  The private key is used to encrypt data and public key must be used to decrypt the data. 

Implementing TDE?

To implement TDE the following four steps need to be followed:

                1)       Create a master key

2)       Create or obtain a certificate protected by the master key

3)       Create a database encryption key and protect it by the certificate

4)       Set the database to use encryption

HOW TO Create a Master Key?

 A master key is a symmetric key that is used to create

     1) Certificates

     2) Asymmetric keys

  Execute the following script to create a master key:

USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word1'; GO

          Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters)

          and you have to backup (use BACKUP MASTER KEY) and store it in a secure location.

HOW TO Create a 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:

USE master; CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate' GO

HOW TO Create a Database Encryption Key?

A database encryption key is required for TDE.  Execute the following script to create a new database and a database encryption key for it:

CREATE DATABASE mssqltips_tde GO USE mssqltips_tde; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert GO

In order to work with TDE the encryption key must be encrypted by a certificate (but not by a password) and the certificate must be located in the master database. 

How TO Enable TDE?

The final step required to implement TDE is to execute the following script:

ALTER DATABASE mssqltips_tde SET ENCRYPTION ON GO SELECT [name], is_encrypted FROM sys.databases GO

You can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.

It is important to emphasize that TDE only encrypts the content of data and log files.  It does not encrypt the data as it is being passed between the client and the database server.