.::: How to configure Transparent Data Encryption (TDE), Monitoring, Remove TDE, remove certificate, test backup & Restore in SQL Server :::.

 

correlation with https://teguhth.blogspot.com/2024/06/how-to-configure-transparent-data.html

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_source
WITH
SUBJECT='Database_Encryption Source';
GO
 Msg 33117, Level 16, State 1, Line 3
Transparent Data Encryption is not available in the edition of this SQL Server instance. See books online for more details on feature support in different SQL Server editions.

 

3. check certificate

SELECT @@servername as ServerName,
    name AS CertificateName,
    pvt_key_encryption_type_desc AS EncryptionType,
    expiry_date,
    start_date,
    issuer_name,
    subject
FROM master.sys.certificates;
WHERE pvt_key_encryption_type_desc <> 'NO_PRIVATE_KEY'

4. create database tde
create database teguhth_enc
go 

 
5. check list tde

6. Create Database Encryption Key

USE teguhth_enc
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_source;
GO

7. Enable Encryption in database

ALTER DATABASE teguhth_enc
SET ENCRYPTION ON;
GO

8. Backup Certificate
USE Master;
GO

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

9. if want to delete encription

--- 1. Disable Encryption
USE master;
GO
ALTER DATABASE teguhth_enc SET ENCRYPTION OFF;
GO

--- 2. Drop Database Encryption Key

USE teguhth_enc;
GO
DROP DATABASE ENCRYPTION KEY;
GO

10. if want drop permanen certificate

drop certificate [TDE_Cert_source];
go

USE master
Go
DROP MASTER KEY;
GO

11. Backup database from source to test

backup database teguhth_enc
to disk = 'c:\tmp\teguhth_enc.bak'
with compression,stats=10;
GO

12. check filelistonly
restore filelistonly from disk = 'c:\tmp\teguhth_enc.bak'

B. monitoring certificates

1 . list TDE SQL Server
SELECT @@servername as ServerName,
    name AS CertificateName,
    pvt_key_encryption_type_desc AS EncryptionType,
    expiry_date,
    start_date,
    issuer_name,
    subject
FROM master.sys.certificates
WHERE pvt_key_encryption_type_desc <> 'NO_PRIVATE_KEY'

2. Monitoring 1
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

3. Monitoring 2
SELECT
    @@SERVERNAME AS 'Server Name',
    db.name AS 'Database Name',
    CASE db.is_encrypted
        WHEN 0 THEN 'Not Encrypted'
        WHEN 1 THEN 'Encrypted'
        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. Try restore to another server

1. Create Master Key
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TDEpassword!'; -- Jika belum ada

2. restore Certificate from master

CREATE CERTIFICATE TDE_Cert_destination
FROM FILE = 'C:\temp\TDE_Cert_source'
WITH PRIVATE KEY
(
    FILE = 'C:\temp\TDE_Cert_source_key.pvk',
    DECRYPTION BY PASSWORD = 'TDEpassword!'
);

3. check
SELECT @@servername as ServerName,
    name AS CertificateName,
    pvt_key_encryption_type_desc AS EncryptionType,
    expiry_date,
    start_date,
    issuer_name,
    subject
FROM master.sys.certificates
WHERE pvt_key_encryption_type_desc <> 'NO_PRIVATE_KEY'

4. restore backup

RESTORE DATABASE teguhth_enc
FROM DISK = 'C:\tmp\teguhth_enc.bak'
WITH MOVE 'teguhth_enc' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQL2022\MSSQL\DATA\teguhth_enc.mdf',
MOVE 'teguhth_enc_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQL2022\MSSQL\DATA\teguhth_enc_log.ldf',
REPLACE, RECOVERY;

5. back to B. monitoring certificates