Migrate database with encrypted columns to SQL 2016

• shahgols

  SSCrazy Eights

  Points: 9874

  More actions

  July 11, 2020 at 12:43 am

  #3769104

  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: 80342

  More actions

  July 12, 2020 at 1:10 am

  #3769190

  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: 7428

  More actions

  July 12, 2020 at 11:55 pm

  #3769285

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

• Gareth Barnes

  Old Hand

  Points: 348

  More actions

  July 16, 2020 at 8:26 am

  #3770468

  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