Click here to monitor SSC
  • Av rating:
  • Total votes: 5
  • Total comments: 7
Warwick Rudd

Encrypting Your SQL Server 2012 AlwaysOn Availability Databases

04 July 2012

It is likely that you'll want to add a database with TDS Encryption to your AlwaysOn Availability Group. If you do so you'll find that you can't use the SSMS wizard to do so. So, how do you achieve it? Read on...

Previously, in my article SQL Server 2012 AlwaysOn, I discussed the components that make up SQL Server 2012’s AlwaysOn and how you can configure your SQL Server environment using AlwaysOn Availability Groups (AAG) to meet the ever-increasing need for ‘High Availability’ (HA) and ‘Disaster Recovery’ (DR). One of the benefits that I outlined was the ability to use the Enterprise Edition feature ‘Transparent Data Encryption’ (TDE) to secure your databases.

Transparent Data Encryption

TDE allows you to protect your databases by performing real-time I/O encryption utilising keys. This prevents anyone who does not have these keys from accessing the data. I am not going to dive into the details of TDE in this article; there are links in the References & Further Reading section later in the article for you to get a better understanding of TDE.

Configuring your AlwaysOn environment

If you have not yet set up and configured a SQL Server 2012 AlwaysOn environment, then please have a read of my article SQL Server AlwaysOn. In this article I shall assume that you already have an environment set up that is already using AlwaysOn Availability Groups (AAG). Normally, if you are going to create a new AAG or need to add a database into an existing AAG, you can simply use the wizards provided in SSMS. However, if you are going to add a database that has been configured to be encrypted using TDE then you will not be able to use these wizards.

Whether you are creating a new AAG, or you are needing to add a database that has been encrypted using TDE, there are steps that you need to complete on your SQL Server 2012 AlwaysOn Primary Replica. These are:

  • Create a Master Key on the Primary Replica
  • Backup the Master Key
  • Create a Certificate protected by the Master Key
  • Backup the Certificate
  • Create a Database Encryption Key
  • Enable a TDE on a Database

Here is some example code that can be used to perform these tasks:

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'P@ssw0rd1';

-- Backup the Master Key
BACKUP MASTER KEY
   TO FILE =
'\\Path\Encryption_Backups\MyServer_MK'
  
ENCRYPTION BY Password = 'P@ssword2';

-- Create Certificate Protected by Master Key
CREATE Certificate MyServer_Cert
  
WITH Subject = 'My DEK Certificate';

-- Backup the Certificate
BACKUP Certificate MyServer_Cert
  
TO FILE = '\\Path\Encryption_Backups\MyServer_Cer'
  
WITH Private KEY (
      
FILE = '\\Path\Encryption_Backups\MyServer_PrivKey',
      
ENCRYPTION BY Password = 'P@ssword3'
  
);


-- Move to the database you wish to enable TDE on
USE TestTDE_WithAlwaysOn
GO

-- Create a Database Encryption Key
CREATE DATABASE ENCRYPTION KEY
   WITH
Algorithm = AES_128
  
ENCRYPTION BY Server Certificate MyServer_Cert;


-- Enable the Database for Encryption by TDE
ALTER DATABASE TestTDE_WithAlwaysOn
  
SET ENCRYPTION ON;

We need to undertake some configuration steps on the Secondary Replicas in order to allow us to be able to replicate the Availability Databases. We need to:

  • Create a Master Key on the Secondary Replica
  • Backup the Master Key
  • Create Certificate from the Primary Replica

Here is some example code that can be used to perform these tasks:

USE MASTER
GO

-- Create a Master Key
CREATE MASTER KEY ENCRYPTION BY Password = 'P@ssw0rd1';

-- Backup the Master Key
BACKUP MASTER KEY
   TO FILE =
'\\Path\Encryption_Backups\MyServer2_MK'
  
ENCRYPTION BY Password = 'P@ssword2';

-- Create Certificate Protected by Master Key
CREATE Certificate MyServer2_Cert
  
FROM FILE = '\\Path\Encryption_Backups\MyServer_Cer'
  
WITH Private KEY (
      
FILE = '\\Path\Encryption_Backups\MyServer_PrivKey',
      
Decryption BY Password = 'P@ssword3'
  
);

The above will need to be undertaken on every Secondary Replica in the SQL Server 2012 AlwaysOn environment. After the Replicas are all configured with the Certificate, the encrypted database(s) can then be made available on all of the Replicas.

When we are using the ‘New Availability Group Wizard’, we have to select those database(s) which will participate in the AlwaysOn Availability Group. If one of the databases has been enabled for encryption utilising TDE, we will not be able to use the wizard to create the AlwaysOn Availability Group. To achieve this, we need to use T-SQL or PowerShell to create the Availability Group.

This T-SQL Code (to run in SQLCMD mode in SSMS) will manually create an Availability Group:

-- The following code needs to be run using SQLCMD Mode

-- The following code needs to be run using SQLCMD Mode
:Connect PrimaryReplicaServer

USE MASTER
GO

-- 1/ Create AlwaysOn AAG with TDE enabled database
CREATE Availability GROUP [SQL2012_TDE]
  
WITH (Automated_Backup_Preference = Secondary)
  
FOR DATABASE [TestTDE_WithAlwaysOn]
   Replica ON N'PrimaryReplicaServer'
      
WITH (Endpoint_URL = N'TCP://PrimaryReplicaServer.Domain1.Com:5022',
              
Failover_Mode = Manual,
              
Availability_Mode = Asynchronous_Commit,
              
Backup_Priority = 50,
              
Secondary_Role(Allow_Connections = ALL)
           ),
      
N'SecondaryReplicaServer'
      
WITH (Endpoint_URL = N'TCP://SecondaryReplicaServer.Domain1.Com:5022',
              
Failover_Mode = Manual,
              
Availability_Mode = Asynchronous_Commit,
              
Backup_Priority = 50,
              
Secondary_Role(Allow_Connections = ALL)
           );
GO

:Connect SecondaryReplicaServer

-- 2/ Join the Secondary Replica to the Newly Created AAG.
ALTER Availability GROUP [SQL2012_TDE] JOIN;
GO

:Connect PrimaryReplicaServer

-- 3/ Create a Full Backup
BACKUP DATABASE [TestTDE_WithAlwaysOn]
TO DISK = '\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_DB.bak'
WITH Copy_Only;

GO

:Connect SecondaryReplicaServer

-- 4/ Start the restoration process to bring your database to a synchronised state
RESTORE DATABASE [TestTDE_WithAlwaysOn]
FROM DISK = '\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_DB.bak'
WITH NoRecovery;

GO

:Connect PrimaryReplicaServer

-- 5/ Create a TLog Backup
BACKUP LOG [TestTDE_WithAlwaysOn]
FROM DISK = '\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_TL.bak';

GO

:Connect SecondaryReplicaServer

-- 6/ Start the restoration process to bring your database to a synchronised state
RESTORE LOG [TestTDE_WithAlwaysOn]
FROM DISK = '\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_DB.bak'
WITH NoRecovery;

GO

:Connect SecondaryReplicaServer

-- 7/ Join the Database to the AAG and bring it into the readable synchronised state.
ALTER DATABASE [TestTDE_WithAlwaysOn]
  
SET HADR Availability GROUP = [SQL2012_TDE];

GO

Alternatively if you prefer to use PowerShell here is some code to manually create an Availability Group:

Import-Module "SQLPS" -DisableNameChecking

# Create Full Database Backup

Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
"\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_DB.bak” -ServerInstance "ServerCoreNode1"

# Create TLog backup

Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
"\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_TL.bak" -ServerInstance "ServerCoreNode1" -BackupAction Log

# Restore Database and Log on Secondary (NoRecovery)

Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
"\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_DB.bak" -ServerInstance "ServerCoreNode2" -NoRecovery

Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
"\\Path\AlwaysOn_Backups\TestTDE_WithAlwaysOn_TL.bak" -ServerInstance "ServerCoreNode2" -RestoreAction Log -NoRecovery

# Create an In-Memory representation of Primary Replica

$PrimaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode1" -EndPointURL
"TCP://ServerCoreNode1.Test.Com:5022" -AvailabilityMode "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate

# Create an In-Memory representation of Secondary Replica

$SecondaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode2" -EndPointURL
"TCP://ServerCoreNode2.Test.Com:5022" -AvailabilityMode "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate

# Create the Availability Group

New-SQLAvailabilityGroup -Name "SQL2012_TDE" -Path "SQLServer:\SQL\ServerCoreNode1\Default" -AvailabilityReplica @($PrimaryReplica,$SecondaryReplica) -Database "TestTDE_WithAlwaysOn"

#Join the Secondary Replica to the Availability Group

Join-SQLAvailabilityGroup -Path "SQLServer:\SQL\ServerCoreNode2\Default" -Name "SQL2012_TDE"

# Join the Secondary Database to the Availability Group

Add-SQLAvailabilityDatabase -Path "SQLServer:\SQL\ServerCoreNode2\Default\AvailabilityGroups\SQL2012_TDE" -Database "TestTDE_WithAlwaysOn"

If we already have an AAG, and we need to add a database that has been configured for encryption, then a slight modification to the T-SQL code above will allow us to achieve this. Swap Steps 1 & 2 in the T-SQL Code above for the code below:

-- The following code needs to be run using SQLCMD Mode

:Connect PrimaryReplicaServer
-- Add your database to the Existing Availability Group
ALTER AVAILABILITY GROUP [SQL2012_TDE] ADD DATABASE [TestTDE_WithAlwaysOn];
GO

We have now configured the environment to have an Encrypted database participate in a SQL Server 2012 AlwaysOn Availability Group.

References & Further Reading

Warwick Rudd

Author profile:

Warwick Rudd is a Microsoft Certified Master – SQL Server 2008, Certified Trainer and is currently the Principal Consultant at SQL Masters Consulting (http://www.sqlmastersconsulting.com.au/). Warwick is a frequent speaker at local SQL Server User Groups and SQL Saturday events in Australia. You can find Warwick online with his blog (http://www.sqlmastersconsulting.com.au/blog) or online at @Warwick_Rudd on Twitter. When not playing with the SQL Stack, Warwick likes to get away to the cold and spend time snowboarding.

Search for other articles by Warwick Rudd

Rate this article:   Avg rating: from a total of 5 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Backup
Posted by: timothyawiseman@gmail.com (view profile)
Posted on: Tuesday, July 10, 2012 at 12:41 PM
Message: Excellent article, thank you.

I would emphasize something you already said: Backup all relevant encryption keys and certificates. Not having a backup of the encryption keys and certificates would create complications in a disaster recovery scenario. Of course, the nature of a HA system can mitigate the possibility of loss of these backups a little bit, but that is no reason to neglect having backups of these items.

Subject: Step-7 Errors Out /Unable to bring Replica to Synchronized State
Posted by: ksmani99 (view profile)
Posted on: Friday, June 21, 2013 at 4:37 PM
Message: Hi Warwick,

Following the steps mentioned, I get an error on step 7, as the database goes to Restoring mode (in my Sec Replica) after I execute Step 6 (Restore Log to Secondary Replica)

Executing Step 7 throws
"Database TestTDE_WithAlwaysOn does not exist" Make sure that the name is entered correctly. As the DB is in Restore mode, SQL is failing to read.

Can you help me here?

Thanks
Subbu

Subject: Not the right way
Posted by: SQLZEN (view profile)
Posted on: Sunday, July 28, 2013 at 5:30 AM
Message: Hi Warwick,

after several trials in my side, it seems to not working in this way.

You must add the database in the Always On Availability Group before creating a Database Encryption Key and Activating the encryption.

Elsewhere you will always receive an error message with insufficient transaction log data to preserve the log backup chain of the principal database.

Subject: Step-7 Errors out
Posted by: jnrt74 (view profile)
Posted on: Monday, August 12, 2013 at 4:08 PM
Message: ksmani99 so the error you are receiving is when you are trying to join your DB to the AG. If you are able to send through to my your scripts for me to have a look at I can get back to you.
Thanks

Subject: Not The Right Way
Posted by: jnrt74 (view profile)
Posted on: Monday, August 12, 2013 at 4:11 PM
Message: SQLZen, you do not need to add the DB to the AG prior to enabling TDE. This can be done in either approach. I can see your issue though. In my script above I have the backup database statement using the With Copy_Only option. With this option being used it will not maintain the chain as you have mentioned. If you just perform the backup and restore process this is fine.

Thanks

Subject: Copy Only.
Posted by: CDeaton (view profile)
Posted on: Monday, September 23, 2013 at 11:28 AM
Message: I am in the process of adding a DR site to a 2 node group and the primary and secondary replica are encrypted. I was planning on adding the keys and certs as mentioned above and then just adding the DR server to the group. However I did just notice the copy only backup which will not work. The only method I can think of is to drop the availability group and start over with three nodes.

Subject: With Copy Only
Posted by: jnrt74 (view profile)
Posted on: Friday, October 11, 2013 at 8:15 PM
Message: CDeaton Yes you are correct. I will get that removed. That was left over from some test scripts. Thanks

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.