Migrate database with encrypted columns to SQL 2016

  • shahgols

    SSCrazy Eights

    Points: 9871

    Hi all,

    I need to migrate a SQL 2012 database that has a few tables with encrypted columns, to a SQL 2016 instance.  I have done this same process between two 2012 instances, and it always worked, but I am unable to do so with 2016.  Below is the code I run after I restore the database to 2016, and it's the exact same code that I ran when I restored to 2012.  Does anyone know if backup/restore of  databases with encrypted columns works between different versions SQL instances?

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Some Password';

    GO

    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;

    GO

    CLOSE MASTER KEY;

    GO

     

  • Site Owners

    SSC Guru

    Points: 80373

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Evgeny Garaev

    SSCertifiable

    Points: 6675

    When you run these commands does it throw any errors or warning?

  • gareth.barnes

    Old Hand

    Points: 347

    It isn't the exact scenario but I wrote an article late last year about migrating from 2008R2 to Azure SQL database which caused problems because the encryption algorithm had changed between those versions of SQL Server. Here's a link to the article - hopefully it will solve the problem you're experiencing.

    https://www.sqlservercentral.com/articles/migrating-column-level-encryption-from-2008r2-to-azure-sql-database

     

Viewing 4 posts - 1 through 4 (of 4 total)

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