How to enable TDE Encryption on a database in an Availability Group

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

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/