How to implement TDE in SQL Server Database
Rem: I have taken those steps from YouTube and added its link.
Rem: The author claims no responsibility for the steps explained in this post. In production execute with your own risk.
SQL Server TDE steps:
1. Create the DMK (Database Master Key)
2. Create the certificate
3. Create the Database Encryption Key (DEK)
4. Enable TDE on the user database
5. Back up the certificate and keys
Note:
After you enable TDE, immediately back up the certificate and its associated private key. If the certificate becomes unavailable, or if you restore or attach the database on another server, you need backups of the certificate and private key. Otherwise, you can't open the database.
Keep the encrypting certificate even if you've disabled TDE on the database. Although the database isn't encrypted, parts of the transaction log might remain protected. You also might need the certificate for some operations until you do a full database backup.
Important Views:
sys.dm_database_encryption_keys
sys.certificates (Important column) expiry_date, start_date,name
SELECT name CertName, certificate_id CertID, pvt_key_encryption_type_desc EncryptType,
issuer_name Issuer, FROM sys.certificates
WHERE issuer_name = 'MY TDE Certificate';
select c.name, c.start_date, c.expiry_date
from sys.services s
join sys.certificates c on s.principal_id = c.principal_id
where c.pvt_key_encryption_type = 'MK'
and c.is_active_for_begin_dialog = 1
and GETUTCDATE() BETWEEN c.start_date AND c.expiry_date
and s.service_id > 2;
use master
SELECT DB_NAME(database_id) DbName,
encryption_state EncryptState,
key_algorithm KeyAlgorithm,
key_length KeyLength,
encryptor_type EncryptType
FROM sys.dm_database_encryption_keys;
--Certificate with Expiry date
CREATE CERTIFICATE MYTDE_Prod_TdeCert WITH SUBJECT = 'MYTDE-PROD TDE Certificate',
START_DATE ='2018-04-10', EXPIRY_DATE='2050-12-31';
--Custom Database Encryption with AES_128 algo
use TESTCompany
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MYTDE_Prod_TdeCert;
--Backup Master Key
BACKUP MASTER KEY
TO FILE = 'MYPath\MYTDE_Prod_MasterKey_23102022xx.key'
ENCRYPTION BY PASSWORD = 'BObAlice2018$$TDE'
Backup Certificates
BACKUP CERTIFICATE MYTDE_Prod_TdeCert
TO FILE = 'MYPath\MYTDE_Prod_22102022.cer'
WITH PRIVATE KEY(
FILE = 'MYPath\MYTDE_Prod_MasterKey_23102022xx.key',
ENCRYPTION BY PASSWORD = 'BObAlice2018$$TDE');
Credit Goes to URL:
https://www.youtube.com/watch?v=fF57RqmlEBQ
https://www.mssqltips.com/sqlservertip/6980/manage-tde-keys-on-premises-with-sql-data-files-stored-in-azure/
https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16
https://learnsqlserverhadr.com/how-to-enable-transparent-data-encryption-in-an-existing-sql-server-availability-group/
https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/
