June 22, 2017 at 12:48 pm
Using SQL server 2014 enterprise edition.
I am trying to restore a database using encrypted bak file. I have a master key file, a certificate file and a bak file.
I first run a script for restoring the master key using the following script:
Restore master key from file = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificateKey.key' decryption by password = 'MyPassword'encryption by password = 'MyPassword'
When I run the script above I get error: The certificate, asymmetric key, or private key data is invalid.
I run the following script to create certificate using the following script:
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificate' WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\SFTPUpload\CLM_bkpcertificateKey.key"' , DECRYPTION BY PASSWORD = 'MyPassword2');
I get 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.
I have verified that the file path (unc) is valid. What could be the reason for the errors?
Thanks in advance for your help.
June 22, 2017 at 1:51 pm
The first thing to check is, does the SQL Server service account have access to the UNC path? Whether you do or not has no bearing on it, as when you run the commands indicated, they execute as the service account.
An easy test if the service account has access to that path would be to try to run a backup of one of your databases to that location (ideally, a small database so it only takes a moment.)
June 23, 2017 at 5:05 am
Restoring to a new server or on the original server?
You don't need a backup of the DMK, you just need the Certificate and the database backup.
Please read my article at this link and also this too
dk98681 - Thursday, June 22, 2017 12:48 PMI first run a script for restoring the master key using the following script:
Restore master key from file = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificateKey.key' decryption by password = 'MyPassword'encryption by password = 'MyPassword'When I run the script above I get error: The certificate, asymmetric key, or private key data is invalid.
Read the articles i have linked.
dk98681 - Thursday, June 22, 2017 12:48 PM
I run the following script to create certificate using the following script:
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificate' WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\SFTPUpload\CLM_bkpcertificateKey.key"' , DECRYPTION BY PASSWORD = 'MyPassword2');I get 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.
I have verified that the file path (unc) is valid. What could be the reason for the errors?
Thanks in advance for your help.
As suggested check permissions to the share and the NTFS permissions to the folder
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2017 at 8:10 am
The same account can do a backup of a db to the folder location mentioned in the script, so the permissions to the folder are good. What else do I need to check?
Thanks.
June 23, 2017 at 8:35 am
dk98681 - Friday, June 23, 2017 8:10 AMThe same account can do a backup of a db to the folder location mentioned in the script
You have more than one UNC path specified.
Permissions will be required for the database engine service account
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2017 at 8:50 am
That could be a typo. I have the same path:
--Query 1 : Restoring master key
-----------
Restore master key from file = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificateKey.key'
decryption by password = 'MyPassword'
encryption by password = 'MyPassword'
--Query 2 : Restoring certificate
--------------------------------
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificateKey.key'
WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\UploadFolder\CLM_bkpcertificateKey.key"' ,
DECRYPTION BY PASSWORD = 'MyPassword1');
June 23, 2017 at 9:05 am
dk98681 - Thursday, June 22, 2017 12:48 PM
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificate' WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\SFTPUpload\CLM_bkpcertificateKey.key"' , DECRYPTION BY PASSWORD = 'MyPassword2');
Not according to the statement above.
Are you restoring the backup to a new server?
Have you read my articles?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2017 at 9:39 am
yes, I read the blog.
yes I am restoring the backup to a new server.
Where are you seeing the difference the file path?
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bbkpcertificate' --path to certificate file
WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\UploadFolder\CLM_bkpcertificate.key"' , --path to master key
DECRYPTION BY PASSWORD = 'MyPassword2');
June 23, 2017 at 9:53 am
dk98681 - Friday, June 23, 2017 9:39 AMyes I am restoring the backup to a new server.
In that case do not restore the DMK from the source server, just create a new master key.
Copy the cert backup and its private key backup to the new server and create the cert from the backup files
dk98681 - friday, June 23, 2017 9:39 AM
Where are you seeing the difference the file path?
See below highlighted in red
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bkpcertificate' WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\SFTPUpload\CLM_bkpcertificateKey.key"' , DECRYPTION BY PASSWORD = 'MyPassword2');
dk98681 - friday, June 23, 2017 9:39 AM
CREATE CERTIFICATE Olympus_bkpcertificate FROM FILE = '\\MyShares\Projects\UploadFolder\CLM_bbkpcertificate' --path to certificate file
WITH PRIVATE KEY (FILE = '"\\MyShares\Projects\UploadFolder\CLM_bkpcertificate.key"' , --path to master key
DECRYPTION BY PASSWORD = 'MyPassword2');
It's not the path to the master key, it's the path to the private key backup file 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply