CERTENCODED: How to Script your Certificates

Introduction

When dealing with certificates in SQL Server we sometimes need to be able to copy a specific certificate from one database to another. For example, when using certificates for permission management, we often need the same certificate to exist in the user database as well as in master.

A few days ago I wrote about how to create a backup of your certificate. To copy a certificate you can certainly create a backup and then restore it to another database. In fact, that is the only way to do it in SQL Server versions before SQL 2012. However, in SQL 2012 Microsoft introduced the capability to script out certificates. In this article, I am going to show you how to use that new feature.

CERTENCODED in Action

Scripting a certificate through SQL Server Management Studio is not (yet) possible. Instead we have to generate the CREATE CERTIFICATE statement ourselves. For that we can use the CERTENCODED built-in function. CERTENCODED takes one parameter, the certificate_id of the certificate we want to script, and returns a VARBINARY value that contains all the public information of the certificate, including the public key.

Let us look at an example. First, we need a certificate to script:

This snippet uses a conditional drop technique to first clean up. Then it creates a simple certificate that is protected by the database master key.

With the certificate in place, we can now use the CERTENCODED function:

As CERTENCODED takes the certificate_id and not the name, we have to use the CERT_ID function to get to that value.

The output of CERTENCODED is a lengthy VARBINARY value:

Now we have a binary representation of our certificate. The next step is to turn that value into a certificate again. For that we can use yet another variation of the CREATE CERTIFICATE statement.

CREATE CERTIFICATE FROM BINARY

We have already seen two ways to create a certificate with different forms of the CREATE CERTIFICATE statement. The first form is the default. It generates a new certificate based on the values provided. The second form takes a DER formatted file following the X.509 standard as input and can be used to restore a certificate backup.

Now it is time to look at the third form of the CREATE CERTIFICATE statement. CREATE CERTIFICATE has a FROM BINARY clause that you can use to create a certificate for a binary value. The syntax looks like this:

You just specify the certificate name and the binary value that you got for example by using the CERTENCODED function. The binary value has to be specified as a constant. You cannot directly create a certificate from a value in a variable.

Scripting a Certificate

Now that we have the two parts that are required to script a certificate, we can build a query to return the CREATE CERTIFICATE statement for a specific certificate:

This query returns the CREATE CERTIFICATE statement for the specified certificate. However, as it is a query against the sys.certificates catalog view, you can certainly use it to script out all certificates too. You just have to remove the WHERE clause.

In the above single-certificate form the result of the query looks like this:

What About The Private Key?

This entire article is only concerned with the public parts of the certificate. To also script and create the private key, additional functionality is required. However, in most situations, which require the same certificate in more than one database, copying just the public key is enough. How to also include the private key is topic of a later article.

Summary

Besides of generating a new certificate and creating one base on a file, the CREATE CERTIFICATE statement can be used to create a certificate based on a binary value. The CERTENCODED function can be used to generate such a binary value for an existing certificate.