How to enable TDE encryption in AG cluster?
In this video, I am going to show you how to enable TDE encryption in SQL Server AG cluster database.
Chapters
0:00 - Create master key and certificate in primary replica
1:06 - Backup certificate
1:23 - Move certificate to secondary replica
1:55 - Create master key and restore certificate in secondary replica
2:10 - Enable encryption in primary replica
2:41 - Check encryption status
Commands used
USE [master];
GO
-- Create the database master key to encrypt the certificate if you do not have already
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MySecretServerPassw0rd!';
GO
-- Create the certificate we're going to use for TDE in AG. We can query the sys.certificates system object to verify the certificate exists for our SQL instance.
CREATE CERTIFICATE TDECerrtificateForAg
WITH SUBJECT = 'TDE Cert for AG';
GO
-- Back up the certificate and its private key Remember the password!
BACKUP CERTIFICATE TDECerrtificateForAg
TO FILE = N'F:\Certificate\TDECerrtificateForAg.cer'
WITH PRIVATE KEY (
FILE = N'F:\Certificate\TDECerrtificateForAg_key.pvk',
ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rdAG!'
);
-- Go to secondary replica and run the below
USE [master];
GO
-- Create the database master key to encrypt the certificate if you do not have already
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MySecretServerPassw0rd!';
GO
-- Restore the certificate from the backup we took in primary with the same password. Please kindly note that
-- you should give the full permission to "Everyone" in backed up cert properties to copy/paste certicate to secondary.
CREATE CERTIFICATE TDECerrtificateForAg
FROM FILE = 'F:\Certificate\TDECerrtificateForAg.cer'
WITH Private KEY (
FILE = 'F:\Certificate\TDECerrtificateForAg_key.pvk',
DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rdAG!')
-- Go to Primary and enable TDE. Only on PRIMARY needed. Please kindly note that the synchronization might be paused in secondary. Please resume data movement if you face this issue.
USE [AdventureWorks2019];
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECerrtificateForAg;
GO
USE [master];
GO
-- Turn on TDE
ALTER DATABASE [AdventureWorks2019]
SET ENCRYPTION ON;
GO
📚 ►Full course on Encrypting connections to SQL Server- • Full course on Encrypting connections to S...
📷 ►Follow me on Instagram - https://www.instagram.com/arslanov_bo...
🧑💼►Follow me on LinkedIn - / bobirmirzo-arslanov-548960173
Watch all my playlists here:
🚀► Troubleshooting sessions: • Troubleshooting
🚀 ►SQL Server replication session: • SQL Server replication
🚀 ►SQL Server Always On Availability Group: • SQL Server Always On Availability Groups (AG)
🚀 ► SQL Server internals: • SQL Internals
🚀 ► Course in On-memory OLTP: • Course on In-Memory OLTP