How to Back Up Your Certificates

Introduction

In SQL Server, certificates are stored within the database in which they were created. That means that they are backed up together with the database. However, as a certificate might hold the "key" to a lot of your data, it is often advisable to have a separate backup. In fact, I recommend that you create a separate backup of every certificate as soon as you have created it.

Certificate backups also provide a simple way to move or copy a certificate from one database to another.

The BACKUP CERTIFICATE Statement

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

This statement writes the public portion of the certificate to the specified file. Other than the BACKUP DATABASE statement, BACKUP CERTIFICATE has no option to overwrite existing files, so the file must not exist.

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:

The BACKUP CERTIFICATE statement does not produce any output by itself. To be able to demonstrate it anyway, I added a call to xp_cmdshell that shows the created files:

There are two things to note here. First, the private key is always written to a separate file. There is no way to create a single-file backup of a certificate in SQL Server. Second, in SQL Server all keys are at all times protected by either a password or by another key in the encryption hierarchy. That extends to the backup files too. As backup files are intended to be stored of site, the SQL Server encryption hierarchy is not available to protect them. Therefore, we have to provide a password to protect the key. This password needs to comply with the SQL Server password policy.

Anybody who can guess the password has free reign on your data. However, without the password there is no way to restore the certificate. So, select a complex, long and random password and then store it somewhere secure, independent of the backup files.

Backing Up a Password Protected Certificate

The above example works for certificates that are protected by the database master key. If you want to back up a certificate that is protected by a password, you need to provide that password in the BACKUP CERTIFICATE statement. As the password is only required to open the private key, it is provided in the WITH PRIVATE KEY section of the CREATE CERTIFICATE statement, using the DECRYPTION BY PASSWORD clause:

The two passwords do not have to be the same. In fact, I recommend that you do not reuse the certificate password to protect the backup.

Summary

Certificates are part of the database and as such are protected by your database backup strategy. However, there are many situations, in which it is advisable to create a separate backup for all your certificates. To do that, the BACKUP CERTIFICATE statement can be used.