.::: How to configure Transparent Data Encryption (TDE), Monitoring & Remove TDE in SQL Server :::.

A. Create TDE

1. Create Master Key

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='TDEpassword!';
GO
 
 
2. Create Certificate protected by master key
CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

3. check list tde 

 
use master;
go

select @@servername as ServerName,
    database_name = d.name,
    dek.encryptor_type,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

4. Create Database Encryption Key

USE tdedb
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO
 
-- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

5. Enable Encryption in database

ALTER DATABASE tdedb
SET ENCRYPTION ON;
GO

6. Backup Certificate
USE Master;
GO

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'C:\temp\TDE_Cert'
WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='TDEpassword!')
 

B. How to monitor TDE Progress:

SELECT @@servername as ServerName,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,create_date,encryptor_thumbprint, encryptor_type,key_algorithm,key_length  FROM sys.dm_database_encryption_keys


SELECT
    @@SERVERNAME AS 'Server Name',
    db.name AS 'Database Name',
    CASE db.is_encrypted
        WHEN 0 THEN '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'
        ELSE 'Unknown'
    END AS 'Is Encrypted',
    dm.encryption_state AS 'Encryption State',
    dm.percent_complete AS 'Encryption Progress',
    dm.key_algorithm AS 'Encryption Algorithm',
    dm.key_length AS 'Encryption Key Length'
FROM
    sys.databases db
LEFT JOIN sys.dm_database_encryption_keys dm
    ON db.database_id = dm.database_id;
 

 
C. Turn Off TDE (Transparent Data Encryption) /remove TDE

1. Disable Encryption

USE master;
GO
ALTER DATABASE tdedb SET ENCRYPTION OFF;
GO

2. Drop Database Encryption Key

USE tdedb;
GO
DROP DATABASE ENCRYPTION KEY;
GO

3. Drop TDE certificate from MASTER databases

use master;
go

select @@servername as ServerName,
    database_name = d.name,
    dek.encryptor_type,
    cert_name = c.name
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;

--- drop master
-- drop  encryption all db

drop certificate [TDE_Cert];
go

USE master
Go
DROP MASTER KEY;
GO