5

I'm trying to perform a restore of a database from a different server onto my localhost server .That database is encrypted (the bak file ) ,so I have to decrypt it ,that's fine I have all the cert files and the password ,but for some reason I'm still getting the following error :

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

This is the code I'm executing:

      CREATE CERTIFICATE [Certificate1] 
FROM FILE = 'C:\Location of the certs'
WITH PRIVATE KEY ( 
    FILE = 'C:\Location of the certs' ,   
    DECRYPTION BY PASSWORD = 'password'
);

PS. I'm no expert at backups and restores .

2
  • Are you giving the path a file name (not just a folder)? The instructions on that aren't extremely clear.
    – Jacob H
    May 18, 2018 at 13:17
  • @JacobH Yes I have the filename included in the path
    – Lucy
    May 18, 2018 at 13:54

1 Answer 1

3

Here are the steps that I had to take to restore the encrypted database onto a different server(localhost).

1.Create the certificates :

CREATE CERTIFICATE [CertficateName] 
FROM FILE = 'C:\FolderName\NameOfCert.cer'
WITH PRIVATE KEY ( 
    FILE = 'C:\FolderName\NameOfCertKey.key' ,   
    DECRYPTION BY PASSWORD = 'YourPassword'
);

2.

USE Master ;
Open Master Key Decryption by password = 'YourPassword'
Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'YourPassword';
    GO

3.Restore Master Key

Use master 
    restore master key
    FROM FILE = 'C:\FolderName\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'YourPassword'
    ENCRYPTION BY PASSWORD = 'YourPassword'

4. This is the last step you ,be careful at this stage as it took me a while to get that each command needs to be run separately :

Alter Database [DatabaseName]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

Next run it separatly

USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

Next command run separatly

RESTORE DATABASE [DatabaseName] FROM DISK = 'C:\Folder\FULL\NameoftheBakFilethat ourAreRestoring.BAK' 
WITH Replace , STATS = 5 

Do not forget to set back to Multi-User mode the Dabase.

1
  • 2
    How do you resolve the error? I got the same exact error on your first step, importing existing cert and key.
    – 2myCharlie
    Aug 20, 2020 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.