HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

The certificate, asymmetric key, or private key file is not valid or does not exist

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
asymmetriccertificatethefileprivateexistvaliddoesnotkey

Problem

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 .

Solution

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'


  1. 4.
  2. 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.

Code Snippets

CREATE CERTIFICATE [CertficateName] 
FROM FILE = 'C:\FolderName\NameOfCert.cer'
WITH PRIVATE KEY ( 
    FILE = 'C:\FolderName\NameOfCertKey.key' ,   
    DECRYPTION BY PASSWORD = 'YourPassword'
);
USE Master ;
Open Master Key Decryption by password = 'YourPassword'
Backup master key to file = 'C:\SQL FodlerName\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'YourPassword';
    GO
Use master 
    restore master key
    FROM FILE = 'C:\FolderName\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'YourPassword'
    ENCRYPTION BY PASSWORD = 'YourPassword'
Alter Database [DatabaseName]
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
USE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';

Context

StackExchange Database Administrators Q#207168, answer score: 3

Revisions (0)

No revisions yet.