One of our customers came up with a requirement where they wanted to Migrate On-prem Database to Azure SQL Managed instance. The databases had traditional column level encryption enabled.
He has restored the database on the SQL Managed instance by Backup/Restore approach. Now when he was trying to read the encrypted column on the destination database, It was showing NULL values after decryption.
Basically post migration decryption was not working properly.
To start with we have checked the symmetric key and the certificate used within the database on the source and destination and made sure that it is same.
In column level encryption the database master key , symmetric key and the certificate is used within the database. When we do the backup/restore, all these three will get migrated as well and we have confirmed it by making sure same is present on the destination.
SELECT name, key_length, algorithm_desc, create_date, modify_date FROM sys.symmetric_keys;
SELECT name, subject, start_date, expiry_date FROM sys.certificates
Here in this case, symmetric key is unable to decrypt (On the SQL MI) as it is not able to open that key as this has been migrated from other (On-Prem) server. Database master key is not able to communicate with the service master key of the new instance (and this is expected behavior).
Scenario 1: - Password with which Database Master Key on the Source database where column encryption was created is known.
Solution here will be to run the procedure "sp_control_dbmasterkey_password" and in password filed use the password of database master key which you have used during encryption configuration on source on-Prem database.
Link: - sp-control-dbmasterkey-password-transact-sql
Post this you will be able to see the data in encrypted column now.
Scenario 2 : - Password with which Database Master Key on the Source database where column encryption was created is Incorrect or Lost
If the password is incorrect, when you try to run the procedure "sp_control_dbmasterkey_password" it will fail with error :
In this case, follow the below steps:
i) Take the backup of the Source On-Prem SQL DB and restore it on the On-prem Server only with different name .
ii) Regenerate the Master Key with the password . You can try putting your new password.
USE [Restore_DB] -- Here you can put your Restore DB name
REGENERATE WITH ENCRYPTION BY PASSWORD = xxx';
GO
iii) You should be able to open the Master Key with the new password on this restore DB now.
Use [Restore_DB]
OPEN MASTER KEY DECRYPTION BY PASSWORD='xx'
iv) Take a backup of this this new DB from the SQL ON-prem and restore it on the SQL Managed instance now.
v) Once Restore is done Run the "sp_control_dbmasterkey_password" command to open the Key. Post this you should be able to view the data in encrypted column successfully.
Microsoft