How to enable TDE Encryption on a database in an Availability Group
-----------------------------------------------------------------------------------------
Scenario
To follow the procedures outlined in this article you need:
An AlwaysOn Availability Group with at least one Primary and one Secondary replica defined.
At least one database in the Availability Group.
A Database Master Key on all replica servers (primary and secondary servers)
A Server Certificate installed on all replica instances (primary and all secondary replicas).
Primary replica -- sql95
Secondary replica -- sql94
Database Name -- TDE_DB
Step 1:Verify each replica instance has a Database Master Key (DMK) in Master – if not, create one.
use master
go
select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##'
go
Create MASTER key encryption by password = '#-9b6#WR'
Execute below command to ensure database master key has been created.
select * from sys.symmetric_keys
where name = '##MS_DatabaseMasterKey##'
go
-----------------------------------------------------------------------------------------
Scenario
To follow the procedures outlined in this article you need:
An AlwaysOn Availability Group with at least one Primary and one Secondary replica defined.
At least one database in the Availability Group.
A Database Master Key on all replica servers (primary and secondary servers)
A Server Certificate installed on all replica instances (primary and all secondary replicas).
Primary replica -- sql95
Secondary replica -- sql94
Database Name -- TDE_DB
Step 1:Verify each replica instance has a Database Master Key (DMK) in Master – if not, create one.
use master
go
select * from sys.symmetric_keys where name = '##MS_DatabaseMasterKey##'
go
Create MASTER key encryption by password = '#-9b6#WR'
Execute below command to ensure database master key has been created.
select * from sys.symmetric_keys
where name = '##MS_DatabaseMasterKey##'
go
Step-2.Create a Server Certificate on the primary replica instance
--------------------------------------------------------------------------------
use master
go
Create certificate TDE_DB_EncryptionCert
with subject='TDE Certificate for the ADV_DB database',
EXPIRY_DATE = '20301031';
Go
-- To validate that the certificate was created, you can issue the following query:
select name,expiry_date,pvt_key_encryption_type_desc,thumbprint from sys.certificates
on sql95 & sql94 - Create folder named CertificateFolder on D:\drive and backup the certificate file on sql95
use master
go
backup certificate TDE_DB_EncryptionCert
to FILE = 'd:\CertificateFolder\EncryptionCert'
with private key (FILE = 'd:\CertificateFolder\TDE_DB_PrivateFile',
ENCRYPTION by password = '#-9b6#WR')
After executing above backup certificate command 2 files will get created in d:\CertificateFolder
copy both files [EncryptionCert & TDE_DB_PrivateFile] on \\sql94\d$\CertificateFolder.
Ensure File has been copied successfully.
execute below command on secondary replica in my case it is sql94
use master
go
select * from sys.symmetric_keys where name = '##MS_DatabaseMasterkey##'
go
Create MASTER key encryption by password = '#-9b6#WR'
go
use master
go
Create Certificate TDE_DB_EncryptionCert
from file = 'd:\CertificateFolder\EncryptionCert'
with private key
(
File ='d:\CertificateFolder\TDE_DB_PrivateFile',
Decryption by password ='#-9b6#WR'
)
Verify certificate created on secondary replica.
please note without creating certificate, do not proceed with below step, otherwise database on secondary will come in suspect state.
----------------------------
select name,expiry_date,pvt_key_encryption_type_desc,thumbprint from sys.certificates
okay, now go back to sql95, its primary replica and execute below command this will create the Database Encryption Key on the Primary Replica Instance
use [ADV_DB]
go
create database encryption key
with ALGORITHM = AES_256
ENCRYPTION BY SERVER Certificate TDE_DB_EncryptionCert
go
Turn on Database Encryption on the Primary Replica Instance
Alter database [ADV_DB] set encryption ON
go
you will get a message command completed successfully.
however using below command you have to check its encryption state
1 = Unencrypted
2 = Encryption in progress
3 = Encrypted
select db_name(database_id),encryption_state,
encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys
it will take some time, but database will remain online.
Backout:-
In case customer says to remove the TDE on ADV_DB.
On primary replica:-
Use Master
Go
Alter database ADV_DB Set encryption off
go
This command will complete immediately, however we have to run below command to check the encryption_state
select db_name(database_id),encryption_state,
encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys
Check the encryption_state, the encryption state will change from 3 to 1 and it will take time, I observed for 1 GB Database it took 1 minute.
As soon as encryption state change from 3 to 1, we have to execute below command to remove encryption key from database.
use ADV_DB
go
drop database encryption key
go
on secondary replica
----------------------
use master
go
select * from sys.certificates;
--retrieve certificate name
DROP Certificate TDE_DB_EncryptionCert;
drop master key
on primary replica
--------------------------
use master
go
select * from sys.certificates;
--retrieve certificate name
Use master
go
DROP Certificate TDE_DB_EncryptionCert;
go
drop master key
Thanks for reading. for more details refer below link:-
https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/28/how-to-enable-tde-encryption-on-a-database-in-an-availability-group/
We have one more scenario here
How to add a TDE encrypted database to an Availability Group-- for this you refer this below link.
https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/07/how-to-add-a-tde-encrypted-database-to-an-availability-group/
