All we need is an easy explanation of the problem, so here it is.
I’m starting a project to encrypt database files. I’ve created a master key and a certificate the following way:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master_K3y'; GO CREATE CERTIFICATE MY_DB_CERT WITH SUBJECT = 'Some subject here'; GO
I have two options to backup the certificate: with or without a private key. I have no idea where this private key comes from, as I did not provide one. Perhaps it was generated for me when creating the certificate?
In any case, my first backup statements did not provide a private key clause.
USE master; GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Master_K3y'; GO BACKUP MASTER KEY TO FILE = 'MASTER_KEY.bak' ENCRYPTION BY PASSWORD = 'yeK_r3tsaM'; GO BACKUP CERTIFICATE MY_DB_CERT TO FILE = 'MY_DB_CERT.bak'; GO
I am wondering if that is enough to restore everything in a working state, say when moving to a new server? If not, what is the use-case for backing up a certificate without a (the?) private key?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The code above will only backup the PUBLIC key portion of the certificate. This however by itself, is useless.
The private key is the part needed to decrypt the database – without this the database cannot be decrypted and therefore cannot be accessed.
To back up a certificate you can use
the BACKUP CERTIFICATE statement. In its simplest form, it looks like
BACKUP CERTIFICATE ACertificate TO FILE ='C:\temp\ACertificate.cert';
But what about the Private Key? The above statement creates a backup
of the public portion of the key only. That is however not the
important part. The important part is the private key of the
certificate. To create a backup of the private key too, we have to add
the WITH PRIVATE KEY clause to the BACKUP CERTIFICATE statement:
BACKUP CERTIFICATE ACertificate TO FILE ='C:\temp\ACertificate.cert' WITH PRIVATE KEY( FILE = 'C:\temp\ACertificate.prvk', ENCRYPTION BY PASSWORD = '**********' );
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂