Is it (always) necessary to backup a certificate with private key?

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.

Method 1

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.

sqlity Backup Certificate explanation

To back up a certificate you can use
the BACKUP CERTIFICATE statement. In its simplest form, it looks like
this:

 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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply