AG + TDE
--1.Create a master key.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
go
/*
USE master;
DROP MASTER KEY;
GO
select * from sys.symmetric_keys
*/
--2.Create or obtain a certificate protected by the master key.
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
/*
use MASTER
GO
select * from sys.certificates
GO
drop certificate MyServerCert
*/
--3.Create a database encryption key and protect it by using the certificate
USE Test2;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
--4. Backup the certificate and private key on the primary replica
use master
go
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\temp\TDECert'
WITH PRIVATE KEY (File='C:\temp\TDECert_Private',
ENCRYPTION BY PASSWORD = 'P@ssw0rd')
GO
--5. Create a database master key on the secondary replica
--This step is similar to step 1. You should create a database master key on all secondary replicas if it does not exist
--select * from sys.symmetric_keys
--6. Create a certificate on the secondary replicas from the primary replica certificate
CREATE CERTIFICATE certname FROM FILE = 'C:\temp\TDECert'
WITH PRIVATE KEY (File='C:\temp\TDECert_Private',
DECRYPTION BY PASSWORD = 'P@ssw0rd')
GO
--7.On Primary / Set the database to use encryption
ALTER DATABASE Test2 SET ENCRYPTION ON;
GO
--8. Monitor the transparent data encryption in SQL Server Always On Availability Group
SELECT 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,encryptor_thumbprint, encryptor_type
FROM sys.dm_database_encryption_keys
where database_id >4
--9. Perform a failover test after TDE implementation is recommended
