Microsoft SQL TDE with External key management

Transparent Data Encryption (TDE) is a security feature in Microsoft SQL Server that provides real-time data encryption and decryption at the database's page level. It protects sensitive information stored in database files, log files, and backups from unauthorized access. TDE does not affect the database schema or functionality, and it works completely transparently without requiring any changes to existing applications.

External key management, on the other hand, is a mechanism that enables organizations to store and manage encryption keys outside the SQL Server environment. This adds an extra layer of security by protecting encryption keys from unauthorized access and separation of duties.

To use TDE with an external key management system, you need to implement an Extensible Key Management (EKM) provider. An EKM provider is a software module that enables SQL Server to interface with external key management systems. Microsoft SQL Server provides an EKM interface that supports integration with third-party key management systems.

Here are the steps to set up TDE with external key management:

1. Acquire an EKM provider: Obtain an EKM provider from a third-party vendor compatible with your key management system.
2. Install the EKM provider: Install the EKM provider on each SQL Server instance where you want to enable TDE with external key management. You may need to follow the vendor’s installation instructions.
3. Register the EKM provider: Register the EKM provider in SQL Server by executing the following command:

CREATE CRYPTOGRAPHIC PROVIDER <EKM_Provider_Name>
FROM FILE = '<EKM_Provider_DLL_Path>';

4. Create an asymmetric key: Create an asymmetric key in the SQL Server master database, which will be used to encrypt the database encryption key (DEK). The asymmetric key is stored in the external key management system.

USE master;
CREATE ASYMMETRIC KEY <Key_Name>
WITH ALGORITHM = <Algorithm_Name>,
 PROVIDER = <EKM_Provider_Name>,
 CREATION_DISPOSITION = CREATE_NEW,
 PROVIDER_KEY_NAME = '<External_Key_Name>';

5. Create a login: Create a SQL Server login and grant it the necessary permissions to use the EKM provider and asymmetric key.

CREATE LOGIN <Login_Name>
FROM ASYMMETRIC KEY <Key_Name>;
GRANT CONTROL ON CERTIFICATE::[<Key_Name>] TO <Login_Name>;

6. Enable TDE: Enable TDE on your database by creating a database encryption key (DEK) and setting the encryption algorithm. Next, encrypt the DEK with the asymmetric key from the external key management system.

USE <Your_Database_Name>;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY <Key_Name>;
ALTER DATABASE <Your_Database_Name>
SET ENCRYPTION ON;

7. After completing these steps, your SQL Server database will be encrypted using TDE with an external key management system. It’s important to monitor and maintain the EKM provider and the key management system to ensure the continued.

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =CASE 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'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)' ELSE 'No Status' END, percent_complete,encryptor_thumbprint, encryptor_type FROM sys.dm_database_encryption_keys