Attempting to restore an encrypted backup on another server

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.

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.

1 Like

Thank you for sharing :+1: