How to Restore a Certificate in SQL Server

Introduction

A few days ago, we talked about that backing up your certificates is an important part of your SQL Server recovery strategy. A recovery strategy cannot be complete without a way to execute a restore. Yet, SQL Server does not have a RESTORE CERTIFICATE statement. So, how do you restore a certificate from a backup taken with BACKUP CERTIFICATE?

Restoring your Certificates

The BACKUP CERTIFICATE statement actually creates the backup file in a well-defined format that is part of the X.509 standard, the DER format. That format however is incidentally the same format that the CREATE CERTIFICATE statement can take as input. To demonstrate, let us first create a simple certificate and then take a backup:

To show that the backup file is indeed in a standard format, you can for example use the openssl command line tool:

The -inform der parameter specifies that the certificate is provided in DER format. In the below screenshot I used xp_cmdshell to execute the above statement.

Now we can drop the existing certificate in the database and then use the CREATE CERTIFICATE statement to restore it:

The CREATE CERTIFICATE statement has the usual two parts, one for the actual certificate including the public key, and one for the private key. The FROM FILE clause specifies that the certificate should be loaded from the referenced file instead of being newly created. The WITH PRIVATE KEY clause specifies the file containing the private key. It also specifies that password that was used to protect that file during the backup.

The CREATE CERTIFICATE statement itself does not produce any output, but we can use the sys.certificates catalog view to see that the restore was successful:

Restoring Password Protected Certificates

The entire WITH PRIVATE KEY clause is optional. If you leave it out, the certificate is created without the private key. If you provide it as shown above, the private key of the restored certificate is going to be protected by the database master key. This is the case even if the certificate's private key was protected by a password at the time the backup was taken. That password is not actually stored in the backup file. If you need to restore a password-protected certificate, you need to specify the password during the restore process using the ENCRYPTION BY PASSWORD clause:

For this reason, you need to make sure, when backing up a password-protected certificate, to not only store the password used to protect the backup file in a safe place, but also the password that people expect the certificate to be protected with. Or, even better, switch the certificate to be protected by the database master key instead.

Other Certificate Sources

The CREATE CERTIFICATE cannot only read DER formatted files that were created by the BACKUP CERTIFICATE statement. It can in fact create a certificate in SQL Server based on any DER formatted file. Such a file could for example be created using Microsoft's MakeCert tool.

Summary

BACKUP CERTIFICATE writes a certificate file in the DER format. That CREATE CERTIFICATE statement can be used to restore such a backup. It can also be used to create a certificate from any other source, as long as it is provided in DER format.