0

I have migrated my database from on premises SQL server, using native restore from url, to managed instance and configured failover group to it. I have opened encryption key on both primary and secondary database but still sometime need to re open encryption key after fail over.

Priya Sharma
  • 35
  • 6
  • What do you mean by manually opening and re-opening encryption key? – Mladen Andzic - Msft Feb 06 '20 at 15:02
  • OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘******’ ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY CLOSE MASTER KEY.. I used these statements to open master key after migrating database to managed instance.After that i configured fail over group for my managed instance and when i trigger fail over ,sometime my data gets encrypted again.. and i need to use same statements to open keys. – Priya Sharma Feb 07 '20 at 04:10
  • @PriyaSharma did you resolve your issue? I'm assuming its because the encrypted data is replicated and expecting the service master key from the primary server which is different to the secondary server. I'm just trying to figure out how to resolve this (e.g. can I backup the service master key from primary server and restore it to secondary or use Azure Key Vault or something else). Let me know what you ended up doing. – Robert Shattock Mar 01 '21 at 22:36

1 Answers1

0

The problem is that the decryption of your database master key is based off your service master key - which is different between your primary and secondary instance (and I haven't found a way in Azure SQL Managed Instance to backup and restore that SMK between instances - non-Azure hosted SQL Server allows this). So with your current approach you'll only be able to decrypt on the primary instance when you've run your OPEN MASTER KEY... ALTER MASTER KEY... statements... and on the secondary instance it will fail.

What's the answer? As per https://stackoverflow.com/a/66467547/1450351 you need to add a database master key password on the master database of the secondary instance (if the decryption of the database master key doesn't work with the service master key, it looks for a database master key password - that way it didn't need the service master key to be the same on both instances) e.g.

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