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 CryptoServer SQLEKM provider.

First of all, a credential for TDE has to be created (see 2021-0004 Setting up Credentials).

SQL Statement

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

Create an asymmetric key used as TDE KEK (Key Encryption Key) in the master database (see 2021-0004 Creating Keys).

SQL Statement

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

Create a SQL Server login account from this asymmetric key:

SQL Statement

CREATE LOGIN tdelogin FROM ASYMMETRIC KEY tdekey

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

SQL Statement

ALTER LOGIN tdelogin ADD CREDENTIAL tde

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

SQL Statement

CREATE DATABASE demo
GO
 
 
USE demo

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

SQL Statement

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY tdekey

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.

SQL Statement

ALTER DATABASE demo SET ENCRYPTION ON;

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

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;
Page