SQLTeam.com | Weblogs | Forums

Attempting to restore an encrypted backup on another server


#1

SQL2017 Standard Edition CU12

I am trying to get encrypted backups to work on some new VMs. On the first VM I have no problem creating the master key, the certificate and it's backup and then running a db backup. My problem is on the second server. I have copied the certificate and the private key onto the second server and set MSSQLSERVER to have full rights to the files.

When I try to restore the certificate:

USE [master];
GO
CREATE CERTIFICATE BackupCertificate
FROM FILE = 'D:\DBEncrypt\BackupCertificate.cer'
WITH PRIVATE KEY 
( 
    FILE = 'D:\DBEncrypt\BackupCertificate_PRIVATE_KEY.key'
   ,ENCRYPTION BY PASSWORD = 'PrivateKeyPassword'
);
GO

I keep getting the following error message:

Msg 15208, Level 16, State 12, Line 23
The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

I have just spent half an hour on google and cannot see why this is happening.

Any ideas? It is probably something simple.


#2

Just looked at this again and got it to work.

The syntax should be:
DECRYPTION BY PASSWORD
not
ENCRYPTION BY PASSWORD
!!!!

Ahhh... More caffeine required.


#3

Thank you for sharing :+1: