November 7, 2019 at 2:19 pm
Hi,
We've got an on-premise database that uses column level encryption and are currently investigating whether we could migrate it to the SQL Database service in Azure. In the past if 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 then resetting the master key to encrypt using the service key.
Given that we can't restore from a local backup to Azure SQL Database we've used the Azure Migration Assistant to copy the database including the data up to Azure but that has completely missed out the certificate and key. We've tried recreating them with the same name but when we try to decrypt we simply get a null value. We can encrypt and decrypt any new records we create in Azure but can't get at the original records.
We've had a good search but can't find any articles on migrating database with column level encryption to Azure - does anyone have any experience?
Thanks,
Gareth Barnes
November 7, 2019 at 6:23 pm
You cannot recreate the certificate. You get a new cert. For the symm key ( I assume sym key), this requires the same identity value and key source (and algorithm) to recreate.
For the cert, make a backup. Then use CREATE CERTIFICATE ... FROM FILE to restore the cert in Azure SQL Database.
November 8, 2019 at 9:07 am
Thanks Steve. We had looked at trying to create the certificate from a backup but this article (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql?view=sql-server-ver15) says you can't create from a file in Azure SQL Database and where would the file be given there's no file system? Sorry if I'm missing something, I'm still struggling with some of the differences between having SQL running in a VM to the PaaS offering.
November 8, 2019 at 3:21 pm
Sorry, this should help: https://techcommunity.microsoft.com/t5/Azure-Database-Support-Blog/Using-Certificates-in-Azure-SQL-Database-Import/ba-p/368949
From FILE doesn't work, but you can FROM BINARY.
November 11, 2019 at 4:40 pm
Thanks, we managed to get a little bit further but it turns out that the original key was created without a specified KEY_SOURCE or IDENTITY_VALUE which I think means we can't recreate it because the default values used will differ between the two servers. I guess we could create a new key on the original server and specify those values this time then decrypt with the original key and encrypt with the new key prior to migrating and then recreate the new key on the new server to perform the decrypt. Thanks again for your help.
November 12, 2019 at 1:28 am
I do think you need a new key, decrypt and encrypt again. A pain, but better than getting into a situation where you're stuck.
November 12, 2019 at 2:32 pm
Thanks for your help so far. Unfortunately we've tried again and still can't get it to work. Here's what we did:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys
WHERE [name] = 'NEWKEYNAME')
CREATE SYMMETRIC KEY [NEWKEYNAME] WITH
KEY_SOURCE = 'WE ADDED SOME TEXT HERE',
ALGORITHM = AES_256,
IDENTITY_VALUE = 'WE ADDED SOME OTHER TEXT HERE'
ENCRYPTION BY CERTIFICATE [CERTNAME];?
BACKUP CERTIFICATE [CERTNAME] TO FILE = 'E:\Backup\CERTNAME.cert'
WITH PRIVATE KEY (FILE = 'E:\Backup\CERTNAME.prvk',
ENCRYPTION BY PASSWORD = '<password>');?
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?
CREATE CERTIFICATE CERTNAME
FROM BINARY = 0x308201D53082013EA...0E51E
WITH PRIVATE KEY ( BINARY = 0x1EF1B5B0000000000...5D9E4B5
, DECRYPTION BY PASSWORD = '<password>')?
We've checked the content of sys.certificates and sys.symmetric_keys and the key_guids and cert_serial_numbers are the same between the two servers if that means anything.
The only thing that is noticeably different is that the source server is running SQL 2008R2 so the Database Master Key is using TRIPLE_DES encryption whereas the one in Azure is using AES_256 but I don't know if that is significant. It feels like we're close but something obviously isn't quite right with our process.
November 12, 2019 at 8:45 pm
Hmmm, I'll have to try this out myself. This looks right, though honestly, you ought to be able to just create the sym key, no cert needed, and see if that decrypts.
Let me see if I can make a quick repro. You are on Azure SQL DB v12, right?
November 13, 2019 at 8:18 am
Thanks Steve, yes we're on Azure v12.
November 15, 2019 at 1:23 am
Did you open the key? Here's what I did, and I'll write this up as an article for a repro, but on a local 2017 server, I did this:
CREATE CERTIFICATE MyCert WITH SUBJECT = 'A Test Encryption Cert', EXPIRY_DATE = '2020-01-10'
GO
CREATE SYMMETRIC KEY MyTestKey
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'The Steve Key', KEY_SOURCE = 'Some Random Workstation'
ENCRYPTION BY CERTIFICATE MyCert
GO
CREATE TABLE EncryptedData
( DataKey INT IDENTITY (1,1) CONSTRAINT EncryptedDataKey PRIMARY KEY
, PlainText VARCHAR(100)
, EncryptedText VARBINARY(max)
)
GO
INSERT dbo.EncryptedData (PlainText, EncryptedText)
VALUES ('Denver Broncos', NULL),
('Denver Nuggets', NULL),
('Colorado Avalanche', NULL),
('Colorado Rockies', NULL)
GO
OPEN SYMMETRIC KEY MyTestKey DECRYPTION BY CERTIFICATE MyCert
GO
UPDATE dbo.EncryptedData
SET EncryptedText = ENCRYPTBYKEY(KEY_GUID('MyTestKey'), PlainText)
GO
SELECT top 10
ed.DataKey, ed.PlainText, ed.EncryptedText
, CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
FROM dbo.EncryptedData AS ed
GO
This sets up all the data, and I can see plain text and decrypted text match.
Next, on an Azure SQL DB, I did this:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test';
GO
CREATE CERTIFICATE MyCert WITH SUBJECT = 'A Test Encryption Cert', EXPIRY_DATE = '2020-01-10'
GO
CREATE SYMMETRIC KEY MyTestKey
WITH ALGORITHM = AES_256,
IDENTITY_VALUE = 'The Steve Key', KEY_SOURCE = 'Some Random Workstation'
ENCRYPTION BY CERTIFICATE MyCert
GO
CREATE TABLE EncryptedData
( DataKey INT IDENTITY (1,1) CONSTRAINT EncryptedDataKey PRIMARY KEY
, PlainText VARCHAR(100)
, EncryptedText VARBINARY(max)
)
GO
I bcp'd out and in the data, going from local 2017 to Azure SQL Database. This gave me data. I ran this query first:
SELECT top 10
ed.DataKey, ed.PlainText, ed.EncryptedText
, CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
FROM dbo.EncryptedData AS ed
I got NULL for the decrypted text. Next I ran this:
OPEN SYMMETRIC KEY MyTestKey DECRYPTION BY CERTIFICATE MyCert
go
SELECT top 10
ed.DataKey, ed.PlainText, ed.EncryptedText
, CAST(DECRYPTBYKEY(ed.EncryptedText) AS VARCHAR(100)) AS DecryptedText
FROM dbo.EncryptedData AS ed
I see the data correctly.
NOTE: I did not export/import the cert. These are different certs in the databases, just with the same name. This is NOT recommended because it can cause confusion.
November 15, 2019 at 1:58 pm
Thanks Steve. The difference with your repro is the use of 2017. Looking at this article https://feedback.azure.com/forums/908035-sql-server/suggestions/33116269-identical-symmetric-keys-do-not-work-between-sql-s even with an existing certificate and using the same KEY_SOURCE, ALGORITHM and IDENTITY_VALUE the generated key is different between versions 2017 and above compared with earlier (we're using 2008R2). The article suggests if you're running 2017 you can enable a trace flag to revert it to the same algorithm as earlier versions but that isn't an option in Azure.
November 15, 2019 at 3:49 pm
That makes total sense. SHA1 is bad, though I'd have hoped they would have a trace flag on at least 2012 to switch this.
What I suspect you'll need to do is move the un-encrypted data and then re-encrypt it in Azure SQL DB. Not the best way to do this, but short of installing a 2017 instance, I'm not sure there is a solution.
Certainly you could move encrypted data to a 2017 instance in Azure, use the trace flag to decrypt, then re-encrypt with a new key that duplicates what you want in Azure SQL.
You could use dev edition, but I think this is a violation of license terms, as you're processing production data.
I think everything here is going to be a PIA for you.
November 15, 2019 at 4:29 pm
Thanks for all your help with this. We've actually come up with another solution based on a couple of other things we came across when scouring the internet. On the 2008R2 server we're creating new fields and then adding data into those based on decrypting with the key and then encrypting by certificate. We can then migrate to Azure SQL DB, recreate the original certificate on there using the from binary option to enable decryption. At the same time we then create a new certificate on Azure and a new symmetric key using that certificate which will operate using the SHA2 algorithm. To use the old certificate we have to temporarily lower the compatibility level on the database but if we decrypt by certificate and then re-encrypt using the new key we can update the original fields, drop the temporary ones and put the compatibility level back up again and we have fully encrypted columns again using the newest algorithm. We're aware that decrypting by certificate can be slow but are happy to accommodate that during a migration as long as we revert to keys at the other end before the client application tries to use the data.
November 15, 2019 at 7:23 pm
That's a good solution, albeit slow, and it will burn DTUs, but it should work.
This would make a great article or tutorial if you want to write it up.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply