Transparent Data Encryption

With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the opportunity for full database-level encryption. TDE is the optimal choice for bulk encryption to meet regulatory compliance or corporate data security standards. TDE works at the file level, encrypting data directly on the hard drive. TDE does not replace column-level encryption. It is just another way of transparently encrypting your database data. 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
GO

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

USE master;
GO

CREATE ASYMMETRIC KEY tdekey
FROM PROVIDER utimaco
WITH ALGORITHM = RSA_2048, PROVIDER_KEY_NAME = 'tdekey',
CREATION_DISPOSITION=CREATE_NEW;
GO

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

CREATE LOGIN tdelogin FROM ASYMMETRIC KEY tdekey

4. Link your SQL Server credentials to your new 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 encrypting the database as a background thread. Depending on the size of the database, it can take a while for the encryption to be completed.

ALTER DATABASE demo SET ENCRYPTION ON;

8. To see the current state of the encryption, use the following 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;