Transparent Data Encryption

With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the opportunity of full database-level encryption by using TDE. TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level which encrypts data directly on the hard drive. TDE does not replace the column-level encryption. It is just another way of encrypting data of your database transparently. The next steps will guide you on how to enable TDE with the SecurityServer EKM provider.

1. First of all, a credential for TDE has to be created.

CREATE CREDENTIAL tde WITH IDENTITY = 'tde', SECRET = 'utimaco' FOR CRYPTOGRAPHIC PROVIDER utimaco

2. Create an asymmetric key used as TDE KEK (Key Encryption Key) in the master database.

USE master;

CREATE ASYMMETRIC KEY tdekey

FROM PROVIDER utimaco

WITH ALGORITHM = RSA_2048, PROVIDER_KEY_NAME = 'tdekey', CREATION_DISPOSITION=CREATE_NEW;

3. Create a SQL Server login account from this asymmetric key:

CREATE LOGIN tdelogin FROM ASYMMETRIC KEY tdekey

4. Link your SQL Server credential to your just created user account with the next statement:

ALTER LOGIN tdelogin ADD CREDENTIAL tde

5. Switch to your database to be encrypted with TDE. In our example we create a database named demo first:

CREATE DATABASE demo GO

USE demo

6. Create a database encryption key, in this example based on an AES algorithm.

CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER ASYMMETRIC KEY tdekey

7. Enable the transparent data encryption and start encryption of the database as a background thread. Depending on the size of the database it can take a while until the encryption has been completed.

ALTER DATABASE demo SET ENCRYPTION ON;

8. To see the current state of the encryption, use the next SQL statement.

SELECT

DB_NAME(e.database_id) AS DatabaseName, e.database_id, e.encryption_state,

CASE e.encryption_state

WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted'

WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted'

WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' END AS encryption_state_desc, c.name, e.percent_complete

FROM sys.dm_database_encryption_keys AS e LEFT JOIN master.sys.asymmetric_keys AS c ON e.encryptor_thumbprint = c.thumbprint;