Encryption with an AlwaysOn cluster

Question

I have a database that was moved off of an old SQL Server 2008R2 instance and into a SQL Server 2012 AlwaysOn cluster. There are several fields within the database that are encrypted using SQL Servers built-in encryption features (master key, cert, symmetric key.)

I have run the following commands on my QA AO instance (the same steps that had been run on the old server):

 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 CREATE CERTIFICATE myCert  
    WITH SUBJECT = 'password'

 CREATE SYMMETRIC KEY myKeyName    
    WITH ALGORITHM = TRIPLE_DES 
    ENCRYPTION BY CERTIFICATE myCert 

Additionally I had to run the following commands to correctly decrypt the data:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

When I run this command I then see all of the data decrypted:

OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users
CLOSE SYMMETRIC KEY myKeyName

So far so good. However, if I run these same steps on my production AO cluster this query:

select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from  users

returns NULL for the password. To make this a little bit more maddening, this statement (run in the context of the QA environment) decrypts everything from both databases just fine:

 OPEN SYMMETRIC KEY myKeyName
 DECRYPTION BY CERTIFICATE myCert 

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) 
 FROM users

 SELECT TOP 1000 
    userid, 
    CONVERT(nVARCHAR(255),DECRYPTBYKEY(password))  
 FROM PRODUCTIONAO.prod_database.dbo.users

 CLOSE SYMMETRIC KEY myKeyName

I am not sure why this would work on my QA instance, but not on my production instance. Any help would be greatly appreciated!

Answer 1

The reason your last query works is due to the fact that you're using the QA instance's key/cert to do the decryption of the production data. In QA you can auto-decrypt the cert with the database master key (DMK) since it's encrypted by the QA service master key (SMK) as follows:

Service Master Key (QA)
  Database Master Key (QA)
    Certificate (QA)
      Symmetric Key (QA)
        Data (Prod)

In prod, you have a different SMK so the only option to open the DMK is by using the password. It seems that you ran the following in the QA environment but not in prod:

/* Add service master key encryption to the database master key */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Try this in prod:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'
OPEN SYMMETRIC KEY myKeyName
DECRYPTION BY CERTIFICATE myCert 
select TOP 1000 userid, CONVERT(nVARCHAR(255),DECRYPTBYKEY(password)) from users
CLOSE SYMMETRIC KEY myKeyName

If that returns data, you need to add the SMK encryption to your DMK in production (the first script). The other option is backing up the SMK from the source instance and restoring it on the secondary. I'd only recommend this in HA use-cases where the instances are fail-over partners and both are in the same environment. Sharing SMKs between QA and prod is a bad practice.