patternsqlMinor
The certificate, asymmetric key, or private key file is not valid or does not exist
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 :
This is the code I'm executing:
PS. I'm no expert at backups and restores .
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 :
2.
3.Restore Master Key
Next run it separatly
Next command run separatly
Do not forget to set back to Multi-User mode the Dabase.
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';
GO3.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 IMMEDIATENext 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 = 5Do 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';
GOUse 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 IMMEDIATEUSE MASTER
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'YourPassword';Context
StackExchange Database Administrators Q#207168, answer score: 3
Revisions (0)
No revisions yet.