December 9, 2019 at 10:30 am
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?
December 10, 2019 at 11:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
December 10, 2019 at 11:21 am
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.
December 10, 2019 at 7:26 pm
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
December 11, 2019 at 8:56 am
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.
December 11, 2019 at 1:25 pm
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.
December 12, 2019 at 2:34 pm
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;
December 12, 2019 at 2:37 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply