How to configure Always On for a TDE database

Creating Availability group for an Encrypted database: 

In this blog we will walk you through the steps to create availability group for an encrypted database. Though we have various articles, I would like to keep the entire step by step approach in one place.

 

We cannot create an availability group for an encrypted database from the availability group wizard. You will receive the below error

This wizard cannot add a database containing database encryption key to an availability group

We have to add the database using T-SQL statements.

Before you configure Always On for a TDE database make sure the following pre requisites are followed.

1.  Always On endpoint port is opened in all node (5022 is default port, which can be changed).

2.  Startup account of cluster service is added as SQL Server login and granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

{

In Windows Server 2008 Failover Clusters, the cluster service no longer runs in the context of a domain user account. Instead, the cluster service runs in the context of a local system (Nt authority\ system) account that has restricted rights to the cluster node. By default, Kerberos authentication is used. If the application does not support Kerberos authentication, NTLM authentication is used.

http://support.microsoft.com/kb/947049 has the details.

When installing the Database Engine as an Always On Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for Always On Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).

}

 

Let me demonstrate few examples here:

 

Method 1 (Using T-SQL and GUI):

In this example, let us assume that you have a two node windows cluster server A and server B with two SQL standalone 2012 instances. Availability group feature is enabled for both the instances.

On server A which is our primary replica, perform the below steps

1.      Enable TDE for the database test.

 

USE master;

 

 GO

 

 CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Test@123';

 

 go

 

 

CREATE CERTIFICATE TestCert WITH SUBJECT = 'Cert@123'

 

 

 go

 

 

USE Test

 

 GO

 

 CREATE DATABASE ENCRYPTION KEY

 

 WITH ALGORITHM = AES_128

 

ENCRYPTION BY SERVER CERTIFICATE TestCert

 

 

 GO

 

 

 ALTER DATABASE test

 

 

 SET ENCRYPTION ON

 

 

 GO

 

2.     Backup the certificate on the primary server

 

   Use master

 

  BACKUP CERTIFICATE TestCert TO FILE = 'C:\test\TestCert.cer'

 

 

   WITH PRIVATE KEY (FILE = 'C:\test\TestCert.pvk' , 

 

 

  ENCRYPTION BY PASSWORD = 'Pas$w0rd');

 

 

  GO

 

3.     Take full and log backups for database test

4.     Create an availability group named Test_AG  from management studio ( create availability group )

5.     Now add server B as secondary replica, while doing this select Skip initial data synchronization as we need to do it manually.

6.     Now add the encrypted database Test to the availability group by running the below command

ALTER AVAILABILITY GROUP Test_AG ADD DATABASE Test

7.     Verify the availability replicas and database on primary from the management studio

 

 

On server B which is our primary replica, perform the below steps

1.      Create the master key and certificate from the backup taken from primary.

 

 USE master;

 

 GO

 

 CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Test@123';

 

 Go 

 

CREATE CERTIFICATE TestCert

FROM FILE = 'C:\test\TestCert.cer'

WITH PRIVATE KEY

(

    FILE = 'C:\test\TestCert.pvk',

    DECRYPTION BY PASSWORD = 'Pas$w0rd'

);

go

 

2.     Restore the database full and transaction log backups with no recovery

 

use Test

 

OPEN MASTER KEY DECRYPTION BY PASSWORD = ''Test@123';

 

GO

 

Restore database test from disk='c:\test\test.bak'

with norecovery

 

go

 

Restore log test from disk='c:\test\test.trn'

with norecovery

 

3.     Add the database to the availability group by running the below command

 

 use Test

 

 OPEN MASTER KEY DECRYPTION BY PASSWORD = ''Test@123';

 Go

           ALTER DATABASE Test SET HADR AVAILABILITY GROUP = Test_AG;

4.     You can verify the data availability group status and database status from management studio for the secondary replica.

Perform the above steps to add multiple encrypted databases to availability group.

 

 

Method 2 (only T-SQL):

 

: connect primary Server

--create database

Create database TDE_AlwaysOn

go

--ENABLE TDE ON DATABASE

--Create Master key

Create master key encryption by password = 'passw0rd!'

go

--Create certificate

Create certificate Server_Cert with subject  = 'Server Certificate'

go

--Backup certificate

Backup certificate Server_Cert to file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert'

with private key(file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert_Priv_Key',

Encryption by password = 'passw0rd!')

go

--Enable TDE on the datbase

use TDE_AlwaysOn

go

Create database encryption key with algorithm = AES_128

encryption by server certificate Server_Cert

go

--Create hadr endpoint

create endpoint Hadr_Endpoint as TCP(

      Listener_port = 5022) for data_mirroring(Role=all, encryption= REQUIRED ALGORITHM AES)

go

--start the endpoint

alter endpoint Hadr_Endpoint state = started;

go

--grant connect on endpoint to the SQL Service account(s) (SQL Server service accounts on all the nodes in the AG).

use master

go

grant connect on endpoint::[Hadr_Endpoint] to [corp\sqlsvc1]

go

--Take full and tranaction log backups of the TDE database

backup database TDE_AlwaysOn to disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_full.bak'

go

backup log TDE_AlwaysOn to disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_tran.trn'  

go

--Create AlwaysOn Availability Group for TDE database

CREATE Availability GROUP [AG_TDE]

   WITH (Automated_Backup_Preference = Secondary)

   FOR DATABASE [TDE_AlwaysOn]

  Replica ON N'primaryServer'

      WITH (Endpoint_URL = N'TCP://PRIMARYSERVER.corp.contoso.com:5022',

               Failover_Mode = Manual,

               Availability_Mode = Asynchronous_Commit,

               Backup_Priority = 50,

               Secondary_Role(Allow_Connections = ALL)

           ),

       N'SECONDAYSERVER'

      WITH (Endpoint_URL = N'TCP://SECONDAYSERVER.corp.contoso.com:5022',

               Failover_Mode = Manual,

               Availability_Mode = Asynchronous_Commit,

               Backup_Priority = 50,

               Secondary_Role(Allow_Connections = ALL)

           );

GO

: connect secondayServer

--create master key

create master key encryption by password = 'passw0rd!'

go

:connect secondayServer

--backup master key

backup master key to file = '\\PRIMARYSERVER\KeyBackup\secondayServer_master_key'

encryption by password = 'passw0rd!'

go

:connect secondayServer

--create certificate protected by master key

create certificate secondayServer_cert

from file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert'

with private key(file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert_Priv_Key',

decryption by password = 'passw0rd!')

go

:connect secondayServer

ALTER Availability GROUP AG_TDE JOIN;

go

:connect secondayServer

--restore database

restore database TDE_AlwaysOn from disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_full.bak' with norecovery

go

:connect secondayServer

restore log TDE_AlwaysOn from  disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_tran.trn' with norecovery

go

:connect secondayServer

--Create hadr endpoint

create endpoint Hadr_Endpoint as TCP(

      Listener_port = 5022) for data_mirroring(Role=all, encryption= REQUIRED ALGORITHM AES)

go

:connect secondayServer

--start the endpoint

alter endpoint Hadr_Endpoint state = started;

go

:connect secondayServer

--grant connect on endpoint to the SQL Service account(s)

use master

go

grant connect on endpoint::[Hadr_Endpoint] to [corp\sqlsvc1]

go

:connect secondayServer

alter database TDE_AlwaysOn set HADR Availability group = AG_TDE

go

Author:

Sravani Saluru , Support Engineer, Microsoft India  GTSC

Kumar Bijayanta, Support Engineer, Microsoft India  GTSC

Reviewed by:

Karthick Krishnamurthy, Technical Advisor, Microsoft India GTSC