TDE.sql

Step 1: Database Master Key (DMK) on the primary replica

first, you can check if your instance already has a database master key

if the result came empty means no master key created

use master 
go
select * from sys.symmetric_keys
where name = '##MS_DatabaseMasterKey##'

since the result came empty we will create master key using the below qurey

USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='P@ssw0rd123';
GO

now we have master key with AES_256 encryption algorathem

You should use complex passwords for creating a master key
If the database master key is already available for your SQL instance, but you do not know the password of it, your SQL service account with the SA permission can decrypt the key
You can create the DML on all AG instances with different passwords

Step 2: Create the Certificate for the AG database on the primary replica

after master key is created we need to create certficate that also we will use in secendray node

USE master;
GO
CREATE CERTIFICATE TDECert WITH SUBJECT = 'Certificate for TDE'
GO

you can use the below command to verfiye the status of certificate .


SELECT name, pvt_key_encryption_type_desc,issuer_name,subject,expiry_date,start_date,key_length
FROM sys.certificates WHERE name = 'TDECert'
GO

Step 3: Create a database encryption key and use the certificate to protect it

In this step, we create a database encryption key (DEK) to enable the TDE. In our case, the DBARepository database is already part of the availability group, and we want to enable TDE on it.

The database master key(DEK) is the actual key for the encryption and decryption of the database. The server certificate protects it.

USE [ahmed]
GO
CREATE DATABASE ENCRYPTION KEY
   WITH Algorithm = AES_128
   ENCRYPTION BY Server Certificate TDECert;

Step 4: Backup the certificate and private key on the primary replica

In this step, we need to take the certificate and its private key backup. This backup should be encrypted by a complex password as well.

Use Master
Go
BACKUP CERTIFICATE TDECert
TO FILE='C:\temp\TDECert'
WITH PRIVATE KEY (FILE='C:\temp\TDECert_private',
ENCRYPTION BY PASSWORD='P@ssw0rd123')
GO

he first file [TDECert] is the certificate and the second file [TDECert_private] is the private key file, and it is protected by the password specified while taking the backup.

Step 5: Create a database master key on the secondary replica

you need to create master key , first verfiye if master key already exsets on secendray

use master 
go
select * from sys.symmetric_keys
where name = '##MS_DatabaseMasterKey##'

you dont have master key then create one

USE master
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='P@ssw0rd123';
GO

Step 6: Create a certificate on the secondary replicas from the primary replica certificate

You must copy the certificate from the primary replica to all secondary replicas. In this step, we create a certificate on the secondary replica from the primary replica certificate.

You must specify the password that we used earlier to encrypt the backup. If you specify a different password, it won’t decrypt the certificate.

CREATE CERTIFICATE SQLAG2_TDE_certificate
   FROM FILE = 'C:\cert\TDECert'
   WITH Private KEY (
       FILE = 'C:\cert\TDECert_private',
       Decryption BY Password = 'Awersdfzxc.1');

after that Verify certificate

SELECT name, pvt_key_encryption_type_desc,issuer_name,subject,expiry_date,start_date,key_length
FROM sys.certificates WHERE name = 'TDECert'
GO

Step 7: Enable TDE for the SQL Server Always On Availability Group database

We are ready to enable the TDE for the database participating in an availability group. Execute the following t-SQL on the primary replica.

ALTER DATABASE DBARepository SET ENCRYPTION ON

Monitor the transparent data encryption in SQL Server Always On Availability Group


USE MASTER;
GO
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,
dm.key_algorithm,dm.key_length
FROM sys.databases db
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

It gives the following column outputs:

Is_encrypted: The value “1” denotes TDE is enabled for the corresponding database
Encryption_state: Initially, once you enable the TDE, it shows the encryption_state 2 along with its percentage completion in the percent_complete column. It might take a few hours to enable TDE on a vast database. You should monitor the status of it after enabling it. Once encryption completes, it changes the encryption_state value to 3 and percent_complete value to zero
Key_algorithm and key_length: It shows the algorithm and key length for encrypting the databases

finally you must do failover test to check if any error occurred

Sql Server – TDE encryption on always on