Migrate Database with column level encryption to Azure

  • gareth.barnes

    SSC Veteran

    Points: 212

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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.

  • gareth.barnes

    SSC Veteran

    Points: 212

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

  • gareth.barnes

    SSC Veteran

    Points: 212

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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.

  • gareth.barnes

    SSC Veteran

    Points: 212

    Thanks for your help so far. Unfortunately we've tried again and still can't get it to work. Here's what we did:

    1. Created a new key on the source server using:
      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];?

    2. Decrypted the data with the original key and encrypted it with the new key
    3. Verified that the new key could be used to decrypt the data at source - this worked so we know the new key was created successfully
    4. Migrated data from the local server through to SQL Azure database using Data Migration Assistant
    5. Took a backup of the local certificate using
      BACKUP CERTIFICATE [CERTNAME] TO FILE = 'E:\Backup\CERTNAME.cert'
      WITH PRIVATE KEY (FILE = 'E:\Backup\CERTNAME.prvk',
      ENCRYPTION BY PASSWORD = '<password>');?

    6. Retrieved the binary values from those backup files using
      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?

    7. Switched to Azure and created a new master key using encryption by password
    8. Recreated the certificate using the syntax below where the binary values were those generated above
      CREATE CERTIFICATE CERTNAME
      FROM BINARY = 0x308201D53082013EA...0E51E
      WITH PRIVATE KEY ( BINARY = 0x1EF1B5B0000000000...5D9E4B5
      , DECRYPTION BY PASSWORD = '<password>')?

    9. Recreated the key using the same syntax as in point 1 above
    10. Tried to decrypt the data in Azure using the key and we still only got NULL values which is what happens when it can't be decrypted

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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?

  • gareth.barnes

    SSC Veteran

    Points: 212

    Thanks Steve, yes we're on Azure v12.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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.

     

  • gareth.barnes

    SSC Veteran

    Points: 212

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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.

     

  • gareth.barnes

    SSC Veteran

    Points: 212

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716284

    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 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply