0

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?

  • "I have no idea where this private key comes from" >> This key is created by running the CREATE DATABASE ENCRYPTION KEY statement, right? – TT. Apr 13 '18 at 11:11
3

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 = '**********'   ); 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.