0

We have an Azure SQL Managed Instance Failover Group setup with a primary and secondary instance – the issue I’m hitting is that we use cell (column) level encryption (CLE) for some of our database table columns. My limited understanding is that the decryption of these depends on the service master key. I think the issue is that the database master key gets encrypted with the service master key and then the databases get synchronised between instances but synchronisation won’t do the server (instance) level data i.e. Service Master Key… so on the primary instance the data can be decrypted but on the failover instance it can’t. Hence you get an error like this:

Please create a master key in the database or open the master key in the session before performing this operation.

If I run the below SQL on my user database it will fix the issue until I failover, at which point I’ll need to run it again. So not ideal from a failover perspective and also means I can’t use the secondary instance as a readonly instance.

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘XXX’ 
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘XXX'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

Below is the only article I could find describing the problem (scroll towards end where it says “Decrypt data in the new primary replica”), and it solves the problem by backing up the service master key from the primary instance and restoring it to the secondary instance, but it's an on-premise setup vs our Azure setup, and the issue is I don’t know how (or if its even possible) to do a backup and restore of the service master key in Azure.

https://www.sqlshack.com/column-level-sql-server-encryption-with-sql-server-always-on-availability-groups/

I did try and backup the service master key from the primary instance so I could restore it to the secondary instance but I could not see a way to do this export in an Azure SQL Managed Instance - https://learn.microsoft.com/en-us/sql/t-sql/statements/backup-service-master-key-transact-sql?view=sql-server-ver15 … I tried giving it blob storage location which was a bit of a stretch and it didn’t like it:

BACKUP SERVICE MASTER KEY TO FILE = 'https://ourstorage.blob.core.windows.net/database-backups/service_master_key.key' ENCRYPTION BY PASSWORD = 'YYYY';

Msg 3078, Level 16, State 2, Line 69 The file name "https://pptefsaaseprd.blob.core.windows.net/database-backups/ase_prod_service_master_key" is invalid as a backup device name for the specified device type. Reissue the BACKUP statement with a valid file name and device type.

I’ve heard mention of perhaps using Azure Key Vault instead but couldn’t find any examples and ideally don’t want to cause any breaking changes to code/sql.

To give some more context our current stored procedures do something like the following:

       OPEN SYMMETRIC KEY SSN_Key_Surname
          DECRYPTION BY CERTIFICATE Surname;
 
       /* SQL making use of the decrypted column */
 
       CLOSE SYMMETRIC KEY SSN_Key_Surname;

So that’s where I’m at. Hopefully I’m just missing a simple step – surely this is not an uncommon scenario? i.e. if you have Azure SQL Managed Instances in a failover group, with column level encryption where the database master key is encrypted by the service master key, how do you configure things so data can be decrypted on both primary and secondary instance?

I'd imagine for this to work you'd need to be able to backup the service master key from the primary instance and restore it to the secondary instance - is this possible in Azure?

Robert Shattock
  • 135
  • 1
  • 8

1 Answers1

0

As expected I was just missing a simple step as described here https://stackoverflow.com/a/58228431/1450351

The Database Master Key (DMK) is encrypted with the Service Master Key (SMK) which is unique to each SQL Service instance and you want it this way.

SQL Server has an alternate way of decrypting the DMK. If the DMK cannot be decrypted with the SMK, it searches the credential store for a password that matches the same family GUID. If it finds a family GUID that matches your database it will attempt to decrypt the DMK with the stored password. If that succeeds then it will use the DMK to encrypt or decrypt credentials or keys within the database.

So the use of sp_control_dbmasterkey_password will store a family GUID based on the database name and the password decrypting the DMK in the master database.

To ensure that the DMK works when a AG fails from the primary to a secondary, run the sp_control_dbmasterkey_password on the secondary as part of your process to join an database to an AG.

So on the secondary instance I had to run this on the master DB

EXEC sp_control_dbmasterkey_password @db_name = N'MyDatabaseWithCLE',   
    @password = N'XX MY MASTER KEY PASSWORD XX’, @action = N'add';  
GO
Robert Shattock
  • 135
  • 1
  • 8