Encrypting your SQL Server 2012 AlwaysOn Availability database

Views 2279

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 (AG) 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 (AG). Normally, if you are going to create a new AG or need to add a database into an existing AG, 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 the wizards.

Whether you are creating a new AG, 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:

   1:  Use Master;
   2:  
   3:  
   4:    -- Create a Master Key
   5:    Create Master Key Encryption By Password = 'P@ssw0rd1';
   6:  
   7:    -- Backup the Master Key
   8:    Backup Master Key
   9:    To File = '\PathEncryption_BackupsMyServer_MK.key'
  10:    Encryption By Password = 'P@ssword2';
  11:  
  12:    -- Create Certificate Protected by Master Key
  13:    Create Certificate MyServer_Cert
  14:    With Subject = 'My DEK Certificate';
  15:  
  16:    -- Backup the Certificate
  17:    Backup Certificate MyServer_Cert
  18:    To File = '\PathEncryption_BackupsMyServer_Cer'
  19:    With Private Key (
  20:    File = '\PathEncryption_BackupsMyServer_PrivKey.cer',
  21:    Encryption By Password = 'P@ssword3'
  22:    );
  23:  
  24:  
  25:    -- Move to the database you wish to enable TDE on
  26:    Use TestTDE_WithAlwaysOn;
  27:  
  28:  
  29:    -- Create a Database Encryption Key
  30:    Create Database Encryption Key
  31:    With Algorithm = AES_128
  32:    Encryption By Server Certificate MyServer_Cert;
  33:  
  34:  
  35:    -- Enable the Database for Encryption by TDE
  36:    Alter Database TestTDE_WithAlwaysOn
  37:    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:

   1:  Use Master;
   2:  
   3:  
   4:    -- Create a Master Key
   5:    Create Master Key Encryption By Password = 'P@ssw0rd1';
   6:  
   7:    -- Backup the Master Key
   8:    Backup Master Key
   9:    To File = '\PathEncryption_BackupsMyServer2_MK.key'
  10:    Encryption By Password = 'P@ssword2';
  11:  
  12:    -- Create Certificate Protected by Master Key
  13:    Create Certificate MyServer_Cert
  14:    From File = '\PathEncryption_BackupsMyServer_Cer'
  15:    With Private Key (
  16:    File = '\PathEncryption_BackupsMyServer_PrivKey.cer',
  17:    Decryption By Password = 'P@ssword3'
  18:    );

 

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:

   1:  -- The Below following code needs to be run using SQLCMD Mode 
   2:    :Connect PrimaryReplicaServer
   3:  
   4:    Use Master
   5:    Go
   6:  
   7:    -- 1/ Create AlwaysOn AAG with TDE enabled database
   8:    Create Availability Group [SQL2012_TDE]
   9:    With (Automated_Backup_Preference = Secondary)
  10:    For Database [TestTDE_WithAlwaysOn]
  11:    Replica on N'PrimaryReplicaServer'
  12:    With (Endpoint_URL = N'TCP://PrimaryReplicaServer.Domain1.Com:5022',
  13:    Failover_Mode = Manual,
  14:    Availability_Mode = Asynchronous_Commit,
  15:    Backup_Priority = 50,
  16:    Secondary_Role(Allow_Connections = All)
  17:    ),
  18:    N'SecondaryReplicaServer'
  19:    With (Endpoint_URL = N'TCP://SecondaryReplicaServer.Domain1.Com:5022',
  20:    Failover_Mode = Manual,
  21:    Availability_Mode = Asynchronous_Commit,
  22:    Backup_Priority = 50,
  23:    Secondary_Role(Allow_Connections = All)
  24:    );
  25:    Go
  26:  
  27:    :Connect SecondaryReplicaServer
  28:  
  29:    -- 2/ Join the Secondary Replica to the Newly Created AAG.
  30:    Alter Availability Group [SQL2012_TDE] Join;
  31:    Go
  32:  
  33:    :Connect PrimaryReplicaServer
  34:  
  35:    -- 3/ Create a Full Backup
  36:    Backup Database [TestTDE_WithAlwaysOn]
  37:    To Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
  38:    With Copy_Only;
  39:  
  40:    Go
  41:  
  42:    :Connect SecondaryReplicaServer
  43:  
  44:    -- 4/ Start the restoration process to bring your database to a synchronised state
  45:    Restore Database [TestTDE_WithAlwaysOn]
  46:    From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
  47:    With NoRecovery;
  48:  
  49:    Go
  50:  
  51:    :Connect PrimaryReplicaServer
  52:  
  53:    -- 5/ Create a TLog Backup
  54:    Backup Log [TestTDE_WithAlwaysOn]
  55:    From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak';
  56:  
  57:    Go
  58:  
  59:    :Connect SecondaryReplicaServer
  60:  
  61:    -- 6/ Start the restoration process to bring your database to a synchronised state
  62:    Restore Log [TestTDE_WithAlwaysOn]
  63:    From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
  64:    With NoRecovery;
  65:  
  66:    Go
  67:  
  68:    :Connect SecondaryReplicaServer
  69:  
  70:    -- 7/ Join the Database to the AAG and bring it into the readable synchronised state.
  71:    Alter Database [TestTDE_WithAlwaysOn]
  72:    Set HADR Availability Group = [SQL2012_TDE];
  73:  
  74:    Go

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

   1:  Import-Module "SQLPS" -DisableNameChecking
   2:  
   3:    # Create Full Database Backup
   4:    Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
   5:    "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak”
   6:    -ServerInstance "ServerCoreNode1"
   7:  
   8:    # Create TLog backup
   9:    Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
  10:    "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak"
  11:    -ServerInstance "ServerCoreNode1" -BackupAction Log
  12:  
  13:    # Restore Database and Log on Secondary (NoRecovery)
  14:    Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
  15:    "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak"
  16:    -ServerInstance "ServerCoreNode2" -NoRecovery
  17:  
  18:    Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
  19:    "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak"
  20:    -ServerInstance "ServerCoreNode2" -RestoreAction Log -NoRecovery
  21:  
  22:    # Create an In-Memory representation of Primary Replica
  23:    $PrimaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode1"
  24:    -EndPointURL "TCP://ServerCoreNode1.Test.Com:5022" -AvailabilityMode
  25:    "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate
  26:  
  27:    # Create an In-Memory representation of Secondary Replica
  28:    $SecondaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode2"
  29:    -EndPointURL "TCP://ServerCoreNode2.Test.Com:5022" -AvailabilityMode
  30:    "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate
  31:  
  32:    # Create the Availability Group
  33:    New-SQLAvailabilityGroup -Name "SQL2012_TDE" -Path
  34:    "SQLServer:SQLServerCoreNode1Default" -AvailabilityReplica
  35:    @($PrimaryReplica,$SecondaryReplica) -Database "TestTDE_WithAlwaysOn"
  36:  
  37:    #Join the Secondary Replica to the Availability Group
  38:   Join-SQLAvailabilityGroup -Path "SQLServer:SQLServerCoreNode2Default"
  39:    -Name "SQL2012_TDE"
  40:  
  41:    # Join the Secondary Database to the Availability Group
  42:    Add-SQLAvailabilityDatabase -Path
  43:    "SQLServer:SQLServerCoreNode2DefaultAvailabilityGroupsSQL2012_TDE"
  44:    -Database "TestTDE_WithAlwaysOn"

If we already have an AG, 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:

   1:  -- The Below following code needs to be run using SQLCMD Mode 
   2:  Connect PrimaryReplicaServer
   3:   -- Add your database to the Existing Availability Group
   4:   ALTER AVAILABILITY GROUP [SQL2012_TDE] ADD DATABASE [TestTDE_WithAlwaysOn];
   5:   GO

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

References & Further Reading

Transparent Data Encryption – http://msdn.microsoft.com/en-us/library/bb934049.aspx

Create Master Key – http://technet.microsoft.com/en-us/library/ms174382.aspx

Create Certificates – http://msdn.microsoft.com/en-us/library/ms187798.aspx

Create Database Master Key – http://technet.microsoft.com/en-us/library/aa337551.aspx

Move a TDE protected Database – http://technet.microsoft.com/en-us/library/ff773063.aspx

Backup Master Keys – http://technet.microsoft.com/en-us/library/ms174387.aspx

Backup Certificates – http://msdn.microsoft.com/en-us/library/ms178578.aspx

Manually Prepare a DB for for AG’s – http://msdn.microsoft.com/en-us/library/ff878349.aspx

Encrypted Databases with AG’s – http://msdn.microsoft.com/en-us/library/hh510178.aspx

Create an AG with Powershell – http://msdn.microsoft.com/en-us/library/gg492181.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search