Migrating DB encrypted by master key

  • CharlieRL

    Old Hand

    Points: 398

    I'm migrating DBs from a 2008 R2 instance onto a 2017 instance.  I have one DB that is encrypted by the master key, how do I migrate this successfully across to a new instance?

  • Site Owners

    SSC Guru

    Points: 80378

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

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

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    So this is a side-by-side upgrade instead of an in-place (direct upgrade of current instance)?

    Just verifying before I try to answer the question.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • oogibah

    Right there with Babe

    Points: 782

    Migrating the master key is easy as long as you have the master key password, you just create that master key on the new instance OR you alter an existing master key on the new instance to include that password and you'll also need the certificate files, which you can either export from your existing instance or hopefully, you have a backup of somewhere.

    see below:

     

    USE master 



    GO



    --if there is no master key need to Create instead of alter

    -- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master Key Password From Origin Instance'

    ALTER MASTER KEY

    ADD ENCRYPTION BY PASSWORD = 'Master Key Password From Origin Instance'



    GO



    CREATE CERTIFICATE ORIGIN_EncryptCert

    FROM FILE = 'C:\Program Files\Microsoft SQL Server\Backup\Origin_EncryptCert' --CERT FILE

    WITH PRIVATE KEY

    (

    FILE = 'C:\Program Files\Microsoft SQL Server\Backup\Origin_EncryptCertKey.pvk', --KEYFILE

    DECRYPTION BY PASSWORD = 'Cert Password from Origin Instance'

    );



    GO
  • gareth.barnes

    Old Hand

    Points: 339

    You might need to bear in mind that as of SQL Server 2017 the encryption algorithm used when creating a symmetric key was changed from SHA1 to SHA2 which means that even recreating the same key that existed in 2008R2 doesn't result in being able to decrypt on 2017 and higher. I'm not certain if this will be the same case for the master key but if you do encounter issues it might be due to the algorithm change. I recently wrote this article about the process we went through to test a migration from 2008R2 to Azure SQL Database.

  • Brandie Tarvin

    SSC Guru

    Points: 172707

    I did not know about the encryption algorithm changing.

    This might mean the need to unencrypt the database before moving it and then re-encrypting it after the move.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • fassantanaatm

    Newbie

    Points: 1

    SQL Server 2008 R2 and below use 3Des algorithm to encrypt SERVICE MASTER KEY. SQL Server 2012 and above use AES256 algorithm. So, it's recommeded to regenerate the key.

     

    ALTER SERVICE MASTER KEY REGENERATE;

  • Mike Good

    SSCertifiable

    Points: 7383

    I just sync the service master key from the old server to the new one.  Simple process: 1) BACKUP SERVICE MASTER KEY TO FILE using some pwd; 2) copy the file to new server; 3) RESTORE SERVICE MASTER KEY FROM FILE using same pwd.  Then the DB master key just works after you restore the DB.  We use same process when migrating to new h/w or new version, and for AG nodes in a cluster...anywhere you need to be able to restore a DB that has a DB master key and have it just work.

    Note: when we migrated to 2016 we did need to recreate our certificates, due to algorithm change https://www.sqlservercentral.com/articles/upgrade-from-sql-2012-to-sql-2016-problem-with-certificates

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

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