July 12, 2018 at 9:20 pm
Hi,
I am upgrading an SQL instance from 2012 to 2016 by building a new server and using backup/restore to migrate the databases to the new 2016 instance.
I've checked, that none of the databases are encrypted:
select * from sys.databases where is_encrypted = 1
Do I still need to backup/restore and cryptographic objects as part of the upgrade? If so, is it only limited to the service master key? I understand any cryptographic objects on the database level will be copied as part of the database backup/restore.
Thanks.
July 12, 2018 at 10:52 pm
If your Database Master Key (DMK) is encrypted by the Service Master Key, then you will need to re-encrypt the DMK with the new Service Master Key on the new instance of SQL Server after you recover the database.OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your DMK password.';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
That's easier than transferring Service Master Keys. Any DMK encryptions by password will remain in the database through the upgrade and are accessible regardless of the SMK.
Eddie Wuerch
MCM: SQL
July 12, 2018 at 11:21 pm
Eddie Wuerch - Thursday, July 12, 2018 10:52 PMIf your Database Master Key (DMK) is encrypted by the Service Master Key, then you will need to re-encrypt the DMK with the new Service Master Key on the new instance of SQL Server after you recover the database.OPEN MASTER KEY DECRYPTION BY PASSWORD = 'your DMK password.';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;
That's easier than transferring Service Master Keys. Any DMK encryptions by password will remain in the database through the upgrade and are accessible regardless of the SMK.
How can I tell if my DMK is encrypted with SMK?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply