2

I need to backup my service master key in managed instance and then restore it to secondary managed instance.

Priya Sharma
  • 35
  • 6
  • Priya how did you resolve your problem? I have a SQL Managed Instance Failover Group and I'm having issues because the two instances obviously have different service master keys, so when failover occurs the secondary database can't be decrypted because the service master key is not the expected one "Please create a master key in the database or open the master key in the session before performing this operation." – Robert Shattock Mar 01 '21 at 22:30
  • Refer to my answer below - I solved my issue by adding 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) – Robert Shattock Mar 07 '21 at 04:10

2 Answers2

0

you cannot do that but I would recommend to create a Key in KeyVault and then use it to encrypt the primary and secondary managed instance. Just make sure both instances are in the same subscription.

Rizwan
  • 318
  • 1
  • 5
0

I couldn't find anyone providing an example of backing up the service master key in an Azure SQL Managed Instance so I'm thinking it's not possible.

However I'm assuming you wanted to do it because you were having issues decrypting CLE (Cell/Column Level Encryption) on the secondary instance. I had this same issue Decryption issue with Azure SQL Managed Instance CLE on secondary instance of a failover group and the best solution was to create a database master key password on the master database of the secondary instance e.g.

EXEC sp_control_dbmasterkey_password @db_name = N'MyDatabaseWithCLE',   
    @password = N'XX MY MASTER KEY PASSWORD XX’, @action = N'add';  
GO

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.

Robert Shattock
  • 135
  • 1
  • 8