Cell Level Encryption With Always On Availability Groups

Article
1/17/2024

Introduction

It's just a part of progression. Something new comes out and it's not always trivial to test it or think of all the many consequences it may have down the road. I never took too much time to think about cell level encryption when working with Availability Groups until I started seeing a bunch of questions crop up. Immediately I knew what the issue was from the error given but I realized just how little I had contemplated all of the aspects that may or may not be touched by the use of something like this.

There are many great posts on Transparent Data Encryption with Always On Availability Groups, one of which was written by my friend and fellow MCM, Edwin Sarmiento. What I do not find a bunch of is using cell based encryption with Always On.

This might be for the lack of usage, it might be due to everyone being an expert or not even using encryption in the same way as I'm going to demonstrate... it is however, something I am seeing much more of and wanted to get some information out there. It seems to be trivial at first... you're just encrypting the data. You call some system functions, pass in the right parameters and VIOLA, the data appears! There can be much more to it than this, especially with readable secondary's as we'll soon see!

Setup

I like to use the AdventureWorksLT2012 database which can be downloaded from CodePlex.

Once downloaded, attach the database to an instance of SQL Server 2012 or higher. In this scenario I'm currently using 2014, but version does not change the behavior. Once attached, make sure the database is in the full recovery model and setup an availability group with just this database in it. You can call it whatever you like, I called mine AWLT_AG. Please note that throughout the script I have references to servers in my test environment - you can change these to match yours. My primary is named Brisbane and my secondary is named Tampa.

Once the availability group is setup, we'll need to copy some data and create some objects for this test. You can follow along the post with me, or download the entire script and go at your own pace. Please note that the script uses sqlcmd mode to run. The script to create them is below:

-- Create all of our Test Cases
use AdventureWorksLT2012
GO
 
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys WHERE name  = '##MS_DatabaseMasterKey##')
BEGIN
    PRINT 'Database Master Key Does Not Exist, Creating...'
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '$trongP@$$'
    PRINT 'Database Master Key Created!'
END
GO
 
 
-- Create encryption certs and keys
CREATE CERTIFICATE SMK_DMK_CERT
WITH SUBJECT = 'Certificate encrypted by the SMK->DMK automatic decryption process.';
GO
 
CREATE SYMMETRIC KEY SMK_DMK_CERT_SYM
WITH ALGORITHM = AES_256
ENCRYPTION BY  CERTIFICATE SMK_DMK_CERT
GO
 
CREATE CERTIFICATE PW_CERT
ENCRYPTION BY  PASSWORD = '@nother$trongP@$$'
WITH SUBJECT = 'Certificate encrypted by a password only'
GO
 
CREATE SYMMETRIC KEY PW_CERT_SYM
WITH ALGORITHM = AES_256
ENCRYPTION BY  CERTIFICATE PW_CERT
GO
 
-- Copy the data to a new table and encrypt
OPEN SYMMETRIC KEY SMK_DMK_CERT_SYM DECRYPTION BY CERTIFICATE SMK_DMK_CERT
OPEN SYMMETRIC KEY PW_CERT_SYM DECRYPTION BY CERTIFICATE PW_CERT WITH PASSWORD  = '@nother$trongP@$$'
 
SELECT CustomerID, FirstName, LastName, ENCRYPTBYKEY(KEY_GUID('SMK_DMK_CERT_SYM'), EmailAddress) AS  [Email_SMK_DMK_CERT], ENCRYPTBYKEY(KEY_GUID('PW_CERT_SYM'), EmailAddress) AS  [Email_PW_CERT]
INTO dbo.CustomerEncrypted
FROM SalesLT.Customer
 
CLOSE SYMMETRIC KEY PW_CERT_SYM
CLOSE SYMMETRIC KEY SMK_DMK_CERT_SYM
GO
 
-- look at the data which is encrypted
SELECT * FROM dbo.CustomerEncrypted
GO
 
-- Create our Reading Procedures
 
CREATE PROCEDURE  dbo.GetCustomerEmail_SMK_DMK_CERT (@CustomerID INT)
AS
BEGIN
    -- Open the key
    OPEN SYMMETRIC KEY SMK_DMK_CERT_SYM DECRYPTION BY CERTIFICATE SMK_DMK_CERT
 
    -- Get the Customer's email
    SELECT CustomerID, FirstName, LastName, CAST(DECRYPTBYKEY(Email_SMK_DMK_CERT) AS  NVARCHAR(255)) AS  EmailAddress 
    FROM dbo.CustomerEncrypted
    WHERE CustomerID = @CustomerID
 
    -- Close the key
    CLOSE SYMMETRIC KEY SMK_DMK_CERT_SYM
END
GO
 
CREATE PROCEDURE  dbo.GetCustomerEmail_PW_CERT (@CustomerID INT)
AS
BEGIN
    -- Open the key
    OPEN SYMMETRIC KEY PW_CERT_SYM DECRYPTION BY CERTIFICATE PW_CERT WITH PASSWORD  = '@nother$trongP@$$'
 
    -- Get the Customer's email
    SELECT CustomerID, FirstName, LastName, CAST(DECRYPTBYKEY(Email_PW_CERT) AS  NVARCHAR(255)) AS  EmailAddress 
    FROM dbo.CustomerEncrypted
    WHERE CustomerID = @CustomerID
 
    -- Close the key
    CLOSE SYMMETRIC KEY PW_CERT_SYM
END
GO

Great, now we're ready to start!

Let's Start Testing!

Let's start at the very, very beginning and make sure our two procedures work as expected on our primary replica.

-- Test to see that we can read the data
EXEC dbo.GetCustomerEmail_SMK_DMK_CERT @CustomerID = 114
EXEC dbo.GetCustomerEmail_PW_CERT @CustomerID = 114
GO

http://www.seangallardy.com/wp-content/uploads/2014/07/SP_Output_Both_Good.png

Success - initially we know everything is baselined and working properly. This doesn't seem so hard!

Now, let's connect to our secondary and test to see if we can read the data there (make sure to change your server names and enabled sqlcmd mode). First let's check the procedure "dbo.GetCustomerEmail_SMK_DMK_CERT" and see what happens:

:CONNECT TAMPAuse AdventureWorksLT2012EXEC dbo.GetCustomerEmail_SMK_DMK_CERT @CustomerID = 114GO

If you're doing this along with me, you'll get an error. Something to the effect of the image below.

http://www.seangallardy.com/wp-content/uploads/2014/07/Error_In_SP_SMK_DMK_CERT_SYM.png

Hmmm, that's not good. Let's try the other one...

:CONNECT TAMPAuse AdventureWorksLT2012EXEC dbo.GetCustomerEmail_PW_CERT @CustomerID = 114GO

http://www.seangallardy.com/wp-content/uploads/2014/07/PW_SYM_SP_Good.png

This one seemed to work just fine, why didn't the other one? Let's take a look at why we had the error in the first one.

The error we received was stating to either create a master key in the database or open the master key in the session before performing an operation and then letting us know that the key "SMK_DMK_CERT_SYM" was not open. In the SQL Server Encryption Hierarchy the Service Master Key (SMK) can automatically encrypt and decrypt Database Master Keys (DMK). That works all well and good on our primary instance where we created the objects because it used the SMK to encrypt everything... however SMKs are uniquely generated per instance. This means on the read only secondary, the SMK was different and it attempted to automatically decrypt the DMK for use to decrypt the certificate, etc, but it couldn't because it wasn't the same as the one used to encrypt it. Thus it couldn't transparently open the DMK nor continue down the line. This, is in essence, the crux of the problem. Notice we had no issue with the password protected certificate as it is encrypted with the password and nothing else. Also note that DMKs do not change in the scope of databases, thus we can easily use mirroring or always on availability groups to keep database level secrets guarded without issue.

Digging a Little Deeper

There is a great graphic showing the multitude of different hierarchies to encrypt secrets and data in the database (encryption hierarchy link above), and not all of these paths require the SMK. Nor does the SMK have to transparently encrypt and decrypt the DMK, as the removal of the SMK from the DMK can be accomplished through the ALTER MASTER KEY (though the reliance on the password can be dropped as well, one of these options must be chosen at all times).

There are times when having the SMK encrypt the DMK is desirable as the DMK password (if that's an option) would need to be known in order to decrypt any of the secrets should the database be restored or copied to another server or when the instance is assumed safe and the developer does not want to include the passwords for decryption in the application. It also allows for easy key rotation, especially at the server and database level without much effort, which may or may not be required by your InfoSec/ITSec team or government/industry compliances.

There are many more options for encryption than the two I presented here, but it shows how much SQL Server can and will do for you without much effort or knowing that it could be doing it for you at all. Most of the examples in Books Online (or TechNet, or MSDN... whatever it is called today) show checking or creating a DMK but not explaining the concept of why it is needed or how to use it. Almost all of the examples assume the transparent decryption through the SMK in order to obtain any secrets in the database. While I understand the need for simplicity, it would have been nice for some blurbs to tell the person following along that automatic key decryption was being used. While I understand not everyone is going to take the time to read up on the topic, I do feel that with the current level of questions I see on the topic it could have saved more time than it took to read/implement.

Possible Solutions to the Problem

We know what our issue is, and if you're followed the whole way along, as well and following and reading of the links, then you can probably guess what possible solutions we have to this very specific case.

Solution#1 - Remove the reliance on the SMK

Since we're relying on the SMK which is causing us all of the issues, we can simply remove that reliance at the cost of having to manually open the DMK in our stored procedure. Below is an example of changing the procedure (we're going to create a new one) to open the DMK using the original DMK password:

-- Remedy the SMK_DMK_CERT procedure by opening the DMK using the password
CREATE PROCEDURE  dbo.GetCustomerEmail_PW_DMK_CERT_SYM (@CustomerID INT)
AS
BEGIN
    -- First, we must open the DMK since it isn't being opened automatically by the SMK
    -- as the SMK has changed since this is a different instance with a different
    -- service account and machine name.
    OPEN MASTER KEY DECRYPTION BY PASSWORD  = '$trongP@$$'
 
    -- Open the key
    OPEN SYMMETRIC KEY SMK_DMK_CERT_SYM DECRYPTION BY CERTIFICATE SMK_DMK_CERT
 
    -- Get the Customer's email
    SELECT CustomerID, FirstName, LastName, CAST(DECRYPTBYKEY(Email_SMK_DMK_CERT) AS  NVARCHAR(255)) AS  EmailAddress 
    FROM dbo.CustomerEncrypted
    WHERE CustomerID = @CustomerID
 
    -- Close the key
    CLOSE SYMMETRIC KEY SMK_DMK_CERT_SYM
    CLOSE MASTER KEY
END
GO

Once created, we can check to see if it works on both servers:

-- Try our *new* SPEXEC dbo.GetCustomerEmail_PW_DMK_CERT_SYM @CustomerID = 114GO-- Still works on the local instance  :CONNECT TAMPAuse AdventureWorksLT2012EXEC dbo.GetCustomerEmail_PW_DMK_CERT_SYM @CustomerID = 114GO-- Now it works!

It works on both instances, and this is because we're not relying on the automatic decryption of the keys through the SMK but rather are opening them manually. This may or may not fit your security model.

Solution #2 - Have the Same SMK

I'm not particularly fond of this solution in that its scope is an entire instance of SQL Server. This could lead to other security issues or nuances that may arise, especially if the server is used to house other databases. If the server is specifically for an application and only those databases exist, then I would be less hesitant to use this approach. I look at it as the mirroring/AG security setup... you can choose to use the same service account for SQL Server across all of the instances participating so that the security on the endpoint is easier (arguably not really) or you could choose to use different accounts and manually have to set their permissions. While it only takes a few more moments to take the extra step, I feel it's worth it.

If we wanted to make the SMKs the same, we could do so by creating a back up of the current SMK on our primary instance and restoring it to the other instances involved. The problem is going to be in the fact that when restoring an SMK, it will need to use the old one to decrypt all of the current keys and secrets then encrypt them using the new SMK. This is not possible on a read-only database such as readable secondary or restoring databases and may cause issues. Using the FORCE option can get past some of these issues, but if there are other databases on the instance I would not recommend this approach. Changing the SMK, either by regenerating it or restoring it, will cause any data or secrets that can't be decrypted to be completely lost forever. The data will still exist, but you'll have no way of decrypting it ever again (ok, a tad harsh but you get the picture).

An example of doing this is below:

-- backup the SMK
BACKUP SERVICE MASTER KEY
TO FILE = 'S:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Brisbane_SMK.cer'
ENCRYPTION BY  PASSWORD = '$trongP@$$@gain'
GO
 
-- Copy it over to the other server
-- Note that attempting to change the SMK will require
-- that all of the secrets the SMK encrypts to be decrypted with the old
-- SMK and then encrypted with the new one. Since the database is in a read-only
-- state, this can't happen if the database currently resides on the replica.
-- This would need to be done before adding it, or removing the AG and adding it back
-- after the SMK has been changed.
-- NOTE: You can fail the AG over (Assuming no other databases are in a restoring, read-only
--       state and use the FORCE option of the RESTORE SERVICE MASTER KEY command.
 
-- Once it is copied over, restore the SMK from our primary to our replica
:CONNECT TAMPA
RESTORE SERVICE MASTER KEY
FROM FILE = 'S:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Tampa_SMK.cer'
DECRYPTION BY PASSWORD = '$trongP@$$@gain'
GO

If we now try our original procedure which relies on the automatic decryption by the SMK we will now see that it works:

-- Test the original SP again:CONNECT TAMPAuse AdventureWorksLT2012EXEC dbo.GetCustomerEmail_SMK_DMK_CERT @CustomerID = 114GO-- Success!

Again, I must stress the significance of solving the issue this way which could lead to unwanted results including possible data loss if other databases exist and the force option is used.

Share via