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 .

Lucy
  • 299
  • 4
  • 7
  • 14

2 Answers2

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.

Lucy
  • 299
  • 4
  • 7
  • 14
0

I ran into this issue because I was storing the certificate and key file in C:\Users\{account}\Documents\, which the SQL Server process user does not have permissions for. Moving the files to C:\temp\ resolved the issue.

Mark Smith
  • 1