Copying a certificate from one database to another database

Sometimes it is necessary to copy a certificate from one database to another database. The most common method I have seen to do this is involves taking a backup of the certificate to disk from one database and then restoring the certificate to the other database.

There is however, a lesser known alternative option available, provided you are working with SQL Server 2012 and above. Sadly despite it being 2017, this is not as foregone a conclusion for SQL Server DBAs as it should be. This alternate option is known as CREATE CERTIFICATE FROM BINARY. There are a few caveats with this option. Chief among them is that you cannot use a variable for the binary value, so you will likely end up needing to use some dynamic SQL.

One of the nice aspects to this feature from an administration and a security perspective is that you do not need to worry about accidentally leaving a copy of your certificate on a disk somewhere or having to remember to delete it after you have imported it into your user database.

First let’s create a certificate in the master database.

Now let’s create a user database we want to copy this certificate to.

I find that the easiest way to perform the copy operation is to use a temporary table. The first password is the password for the private key component of the certificate, similar to the ENCRYPTION BY PASSWORD value you would use if you were creating a certificate from a backup file. Note that the CERTENCODED and CERTPRIVATEKEY functions do not accept the name of the certificate you are using but instead use the id value that is stored in the sys.certificates system catalog. You can either lookup this value or use the CERT_ID function as I have in the example below.

Now in the context of the application database we can create the new certificate from the data in the temporary table. Note we need to use some dynamic SQL here but it is not incredibly complicated. For legibility purposes when working with dynamic SQL I like to follow a method described to me by a colleague, Mark Wilkinson who during all the spare time he gets while raising four kids likes to blog over at m82labs.com. In this post he describes the method he likes to use to untangle dynamic SQL. Note that in our convert function we are adding a style of 1, which adds a leading 0x when converting a binary value to a hex string.

To confirm we have successfully duplicated the certificate we can run the code below.

We have successfully copied a certificate from one database to another. Of course, having the ability to copy certificates from one database to another without needing to take a backup in no way negates the need to keep an offsite backup of your certificate so you can always restore it in the event of disaster. When you first create your certificate you should take a backup and store the backup some place safe. Or at least some place your systems administrators tell you is safe.

Below is some example code to backup a certificate.

Go and have fun folks!