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/ 

Step by step how to refresh Databases In AlwaysOn if TDE is also enabled

How to refresh Databases In AlwaysOn Availability  if  TDE is also enabled on those databases.

Assume customer comes and say they want to refresh database companytest database from companyprod database.

Prerequsite:-

An existing AlwaysOn Availability Group with at least one Primary[Win2k12-2] and one Secondary replica [Win2k12-3] instance.

A TDE encrypted database on the same instance as the primary replica, online and accessible.

A Database Master Key on all replica servers hosting the availability group (the primary will already have one since it has a TDE encrypted database).

Here:-

Win2k12-2 = Primary replica

Win2k12-3 = Secondary replica

MyTestAg = Availability group Name

\\PDOMAINDC\AlwaysOnBackup = Backup shared folder..

and Task here is to refresh CompanyTest database from CompanyProd Both Databases have TDE already Enabled.

The broad steps which we need to carry out to meet our requirement is as:-

• Remove a Secondary Database from Availability Group
• Remove the specified primary database and the corresponding secondary databases from the availability group.
• Drop database from a secondary replica

• Take full backup of companyprod from primary replica and restore into compantest database on primary replica with norecovery
• Take Transaction log backup of companyprod from primary replica and restore into compantest database on primary replica with recovery.
• On primary replica we specify user database ‘compnaytest’ as we want to add to the availability group.
• Again, take Fullbackup of companytest from primary replica with copy_only option.
• Restore companytest to secondary replica in norecovery mode
• Take T-log backup of companytest from primary replica
• Restore T-log backup of companytest to secondary replica in norecovery mode
• Join the secondary database ‘companytest’ to the local secondary replica.

• Remove a Secondary Database from Availability Group

: connect WIN2k12-3

Use master

Go

ALTER DATABASE [companytest] SET HADR OFF;

GO

• Remove the specified primary database and the corresponding secondary databases from the availability group.

: connect Win2k12-2

USE [master]

GO

ALTER AVAILABILITY GROUP [MyTestAg] REMOVE DATABASE [companytest];

GO

: connect WIN2k12-3

drop database companytest

GO

: Connect WIN2K12-2

BACKUP DATABASE [companyprod] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH  COPY_ONLY, FORMAT, INIT, COMPRESSION,  STATS = 1

GO

RESTORE DATABASE [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companyprod_full.bak' WITH  RECOVERY,replace

, NOUNLOAD,  STATS = 5,

move N'AdventureWorks2014_Data' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest.mdf',

move N'AdventureWorks2014_Log' to N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\companytest_log.ldf'

GO

: Connect WIN2K12-2

USE [master]

GO

ALTER AVAILABILITY GROUP [MyTestAg] ADD DATABASE [companytest];

GO

: Connect WIN2K12-2

BACKUP DATABASE [companytest] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

: Connect WIN2K12-3

RESTORE DATABASE [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_full_Again.bak' WITH  NORECOVERY, NOUNLOAD,  STATS = 5

GO

: Connect WIN2K12-2

BACKUP LOG [companytest] TO  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

GO

: Connect WIN2K12-3

RESTORE LOG [companytest] FROM  DISK = N'\\PDOMAINDC\AlwaysOnBackup\companytest_Again_1.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

GO

-- Wait for the replica to start communicating

: Connect WIN2K12-3

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 -- wait for 5 minutes

if (serverproperty('IsHadrEnabled') = 1)

    and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

    and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N'MyTestAg'

    select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

    while @conn <> 1 and @count > 0

    begin

        set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

        if @conn = 1

        begin

            -- exit loop when the replica is connected, or if the query cannot find the replica status

            break

        end

        waitfor delay '00:00:10'

        set @count = @count - 1

    end

end

end try

begin catch

    -- If the wait loop fails, do not stop the execution of the alter database statement

end catch

Join the secondary database ‘companytest’ to the local secondary replica.

ALTER DATABASE [companytest] SET HADR AVAILABILITY GROUP = [MyTestAg];

GO


Thanks for reading..


and
 How to add a TDE encrypted database to an Availability Group

 refer this hyper link..

Enable TDE for a database and how to restore TDE Enabled Database on other database server

Step by Step Enable TDE for a database and how to restore TDE Enabled Database on other database servers

---------------------------------------------------------------------------------------------------

For Theory refer this link 

-------------------------------

In Server 1:

USE Master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

CREATE CERTIFICATE TestSQLServerCert

WITH SUBJECT = 'Certificate to protect TDE key',
EXPIRY_DATE = '2030-04-23';

GO

-- Create a database to be protected by TDE.

CREATE DATABASE CustRecords;

GO

-- Switch to the new database.

-- Create a database encryption key, that is protected by the server certificate in the master database.

-- Alter the new database to encrypt the database using TDE.

USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

you will receive this warning..

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database

Hence, we will take backup of that certificate in below steps

-- Create a backup of the server certificate in the master database.

-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server

-- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).

Use master
go

BACKUP CERTIFICATE TestSQLServerCert

TO FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO
Then execute above command again to remove those warning..USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

use CustRecords
go

ALTER DATABASE CustRecords SET ENCRYPTION ON;

GO

/* Now Verifying TDE is enabled for which database and what is status.. */
At any point, you can use the sys.dm_database_encryption_keys DMV to determine which databases are currently encrypted (or in progress of being encrypted) on your server.
This DMV also gives you the status of the encryption process of newly database.

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

or


SELECT DB_NAME(database_id) as database_name,
database_id,
CASE encryption_state
  when 0 Then N'No database encryption key present, no encryption'
  When 1 Then N'Unencrypted'
  When 2 Then N'Encryption in progress'
  When 3 Then 'Encrypted'
  When 4 Then 'Key change in progress'
  When 5 Then 'Decryption in Progress'
END as encryption_state,
key_algorithm,
key_length,
Percent_complete
from sys.dm_database_encryption_keys;

/*
select *
from  sys.certificates
inner join sys.key_encryptions
on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
where (sys.certificates.[name] = '<Certificate Name>')
*/

use master;
go

select * from sys.certificates
go

select @@SERVERNAME,database_name = d.name,
cert_name = c.name    --dek.encryptor_type,
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;
      
go                          

SELECT db_name(database_id),encryption_state,
case encryption_state
when 3 then '3=Encrypted'
end 'encryption_state',
encryptor_thumbprint, encryptor_type, percent_complete
FROM sys.dm_database_encryption_keys 


--Output will show -- > encryption_state is 3=Encrypted for <Database>


After successful execution, you will find 2 files in this location (C:\SQLCert)

 Detach the TDE protected database from the source server. (optional) if you want then.. 

USE Master ;

GO
EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';

GO

In Server-2
----------------
when you will try to restore TDE enabled database in 2nd server,will get below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally.

Hence

Create a database master key on the destination instance of SQL Server. For more information, see Using Transact-SQL below.

Recreate the server certificate by using the original server certificate backup file. For more information, see Using Transact-SQL below.

-- Move or copy the database files from the source server to the same location on the destination server.

-- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.

-- Create a database master key on the destination instance of SQL Server.

 USE master;

   GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

--- After creating a Master key when i tried to restore the database again, got below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Msg 33111, Level 16, State 3, Line 8
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 8
RESTORE FILELIST is terminating abnormally.

-- Recreate the server certificate by using the original server certificate backup file.

-- The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TestSQLServerCert

FROM FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO

-- Attach the database that is being moved.

-- The path of the database files must be the location where you have stored the database files.

CREATE DATABASE [CustRecords] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )

FOR ATTACH ;

GO

Thanks for Reading..

other Interesting link on this topic:-

https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

Link for Understanding Encryption Hierarchy is written in this blog link..

http://kushagrarakesh.blogspot.com/2015/09/there-is-already-master-key-in-database.html