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!

Bowe

What is the difference if any between your QA box and your Prod box? Is it all on the same hardware? Same OS? Same Service Pack etc? – Skimp

They are different AO Clusters, but the two machines in each cluster are identical. The machines are virtual. – Bowe

Added another answer for you. – Skimp

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.

Fraase

Answer 2

When a database master key is created, the server saves 2 versions of key. One version is encrypted by the master service key and by default is used by the server. The second version is encrypted by the password that you supply to the server when you create the database master key. This version usually is not used. When you move your database into a different environment (production in your case), the new server has a different master service key. Since it is not the service key that was used to encrypt the database’s master key, it also can’t be used in order to open the database’s master key. This is where you should use the version that was encrypted with your password. You need to open the master key using your password, then encrypt it using the new service key and close it. After you do that, the database’s master key can work with the master service key, so you don’t need to do it again.

steps/code:

open master key decryption by password = 'WriteYouOriginalPasswordHere'

alter master key add encryption by service master key

close master key

Skimp