Background

My team have been exploring the possibility of migrating our on-premise SQL servers to Azure SQL Database. Most of the time we were able to carry out proof of concept migrations using Azure Data Migration Assistant successfully but we had to make some adjustments in order to migrate one database that used column level encryption.

In the past when we have moved the database between on-premise servers we've been able to change the master key to encrypt by password before taking a backup and then restoring at the destination server. We reset the master key to encrypt using the service key. Given that we can't restore from a local backup to Azure SQL Database we used the Azure Migration Assistant to copy the database including the data up to Azure, but that tool ignores any certificates and keys in the database.

A lot of advice on the Internet seemed to suggest that you should simply be able to create a new symmetric key in Azure using the same configuration information as had been used locally. This didn't work for us and it became apparent that the reason for that was that as of SQL Server 2017 the encryption algorithm used when creating a symmetric key was changed from SHA1 to SHA2. This meant that even though the keys had the same GUID and in our case were also created from the same certificate they were different and therefore the new key couldn't be used to decrypt data that had been encrypted with the old key.

Solution

After a few false starts and having to consider the possibility of decrypting the data locally prior to the migration and then encrypting again at the destination, we decided to try temporarily encrypting the existing fields using just the certificate rather than the key because we knew we could recreate the certificate correctly on Azure.

To preserve the original data as far as was possible and to try to ensure that we made this a repeatable process we wrote a script to create new fields for each field that used column level encryption. The new fields had the same name as the existing fields with an underscore appended to make it easy to identify them e.g.

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN_')
   ALTER TABLE TABLE1 ADD ENCRYPTED_COLUMN_ [varbinary](max) NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN1_') 
  ALTER TABLE TABLE1 ADD ENCRYPTED_COLUMN1_ [varbinary](max) NULL
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN2_') 
  ALTER TABLE TABLE1 ADD ENCRYPTED_COLUMN2_ [varbinary](max) NULL

In total there were around 50 fields across 4 tables that we added using this script. We then added data into these new columns:

OPEN SYMMETRIC KEY CURRENT_KEY
DECRYPTION BY CERTIFICATE CERTNAME
UPDATE [dbo].[TABLE1]
SET
[ENCRYPTED_COLUMN_] = ENCRYPTBYCERT(Cert_ID('CERTNAME'), DecryptByKey(ENCRYPTED_COLUMN, 1, AUTHENTICATOR)),
[ENCRYPTED_COLUMN1_] = ENCRYPTBYCERT(Cert_ID('CERTNAME'), DecryptByKey(ENCRYPTED_COLUMN1, 1, AUTHENTICATOR)),
[ENCRYPTED_COLUMN2_] = ENCRYPTBYCERT(Cert_ID('CERTNAME'), DecryptByKey(ENCRYPTED_COLUMN2, 1, AUTHENTICATOR))
CLOSE SYMMETRIC KEY CURRENT_KEY
GO

Now that the data was encrypted with the certificate we were ready to move it using Azure Data Migration Assistant. Whilst the data was moving we backed up the existing certificate:

BACKUP CERTIFICATE [CERTNAME] TO FILE = 'E:\Backup\CERTNAME.cert'
WITH PRIVATE KEY (FILE = 'E:\Backup\CERTNAME.prvk',
ENCRYPTION BY PASSWORD = '<password>');
GO

We can't restore from a file system in Azure so we have to convert the backup file to binary on the on-premise server in order to use it on Azure SQL Database:

SELECT 1, BulkColumn FROM OPENROWSET(BULK N'E:\Backup\CERTNAME.cert', SINGLE_BLOB) rs
SELECT 1, BulkColumn FROM OPENROWSET(BULK N'E:\Backup\CERTNAME.prvk', SINGLE_BLOB) rs
GO

Switching to Azure we then created a new database master key and then recreated the original certificate using the binary values from the result set above:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>'
GO
CREATE CERTIFICATE CERTNAME
FROM BINARY = 0x308201D53082013EA...0E51E
WITH PRIVATE KEY ( BINARY = 0x1EF1B5B0000000000...5D9E4B5, 
DECRYPTION BY PASSWORD = '<password>');
GO

Moving forward in Azure we wanted to use a new certificate which would work with the SHA2 algorithm and an associated key to encrypt the data so we created those:

CREATE CERTIFICATE AZURECERTNAME WITH SUBJECT = 'AZURECERTSUBJECT'; 
GO
CREATE SYMMETRIC KEY [AZUREKEY] WITH
KEY_SOURCE = 'Our Own Key Source Value',
ALGORITHM = AES_256, 
IDENTITY_VALUE = 'Our Own Identity Value' 
ENCRYPTION BY CERTIFICATE AZURECERTNAME;
GO

In order to use the original certificate in Azure we needed to change the compatibility level of the database to support SHA1:

ALTER DATABASE AzureDBName
SET COMPATIBILITY_LEVEL = 100
GO

Once the Azure Data Migration Assistant had completed we could then decrypt the data using the original certificate and re-encrypt it back into the original columns using the new certificate and key:

OPEN SYMMETRIC KEY AZUREKEY
DECRYPTION BY CERTIFICATE AZURECERTNAME

--CERTNAME IS THE NAME OF THE ORIGINAL CERTIFICATE RECREATED ON AZURE
UPDATE [dbo].[TABLE1]
SET
[ENCRYPTED_COLUMN] = EncryptByKey(key_guid('AZUREKEY'), DECRYPTBYCERT(Cert_ID('CERTNAME'),  ENCRYPTED_COLUMN_), 1, AUTHENTICATOR),
[ENCRYPTED_COLUMN1] = EncryptByKey(key_guid('AZUREKEY'), DECRYPTBYCERT(Cert_ID('CERTNAME'),  ENCRYPTED_COLUMN1_), 1, AUTHENTICATOR),
[ENCRYPTED_COLUMN2] = EncryptByKey(key_guid('AZUREKEY'), DECRYPTBYCERT(Cert_ID('CERTNAME'),  ENCRYPTED_COLUMN2_), 1, AUTHENTICATOR)
CLOSE SYMMETRIC KEY AZUREKEY
GO

The final step was to then tidy up the additional temporary fields and set the database compatibility level back up to the highest available to ensure we get any additional benefits of migrating to Azure:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN_') ALTER TABLE TABLE1 DROP COLUMN ENCRYPTED_COLUMN_
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN1_') ALTER TABLE TABLE1 DROP COLUMN ENCRYPTED_COLUMN1_
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE1' AND COLUMN_NAME = 'ENCRYPTED_COLUMN2_') ALTER TABLE TABLE1 DROP COLUMN ENCRYPTED_COLUMN2_
GO
ALTER DATABASE AzureDBName
SET COMPATIBILITY_LEVEL = 150
GO

Our database in Azure SQL Database was now encrypted using the latest algorithm and had been migrated from our on-premise server maintaining security of the data at all stages in the process. We are aware that the encryption and decryption by certificate stages will be slow and will potentially use Azure DTUs quickly so will be monitoring this during some further test migrations using larger data sets.