Restoring encrypted database on another server (using Backup Encryption)
Create a brand new master key on your second instance. i.e. don't create it from backup you taken from 1st instance. Then restore certificate from the backup taken and then try. I guess you don't need master key and only certificate is required for restore purposes. Follow the below steps:
Step1: Create Master Key
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'MasterKey_Password';
Step2: Verify permissions on cert and pvt key
Make sure SQL Server service account of second instance has FULL permissions on cert and pvt key that you created.
Step3: Create cert from backup
CREATE CERTIFICATE BackupEncryptTestCert
FROM FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE.cer'
WITH PRIVATE KEY
(
FILE = 'E:\GKKeys\SMGK_BACKUP_CERTIFICATE_PRIVATE_KEY.key' ,
DECRYPTION BY PASSWORD = 'smGK_BackupCertificate_BACKUP_Password'
)
Step4: Restore the DB
I can't comment but I'd like to add a bit of detail to this answer.
- Database master key passwords do not need to match between instances
- When you backed up the certificate you added a private key and password, this private key is independent of the existing database master key.
- This certificate can now be created using the .cert, .key, and private key password on any other instance that has a database master key (as long as the service account has permissions as SQLPRODDBA mentions)
SQLITY post on backing up certificates
in SQL Server all keys are at all times protected by either a password or by another key in the encryption hierarchy. That extends to the backup files too. As backup files are intended to be stored of site, the SQL Server encryption hierarchy is not available to protect them. Therefore, we have to provide a password to protect the key.