CERTPRIVATEKEY: Scripting the Private Key of a Certificate

Introduction

Friday we talked about how you can use CERTENCODED to script certificates. In that article I mentioned that CERTENCODED only encodes the public side of the certificate. Today I would like to show you how to do the same with the certificate's private key.

The CERTPRIVATEKEY Function

The CERTPRIVATEKEY function takes in its simpler form two input parameters. The first is the certificate_id of the certificate in question. The second is the password that is used to protect the private key. As with all other T-SQL commands that expose the private key of a certificate, this one too requires a password that is used to scramble the output. Anytime you want to work with the resulting output, you need to specify that same password again.

Besides of that additional password, CERTPRIVATEKEY is used the same way as CERTENCODED:

And, just like CERTENCODED, CERTPRIVATEKEY returns a single VARBINARY value. That value contains the binary representation of the certificate's private key:

Private Key Encryption

The output of the private key is encrypted with the supplied password. That password is first extended to a key using additional random information. That random information is different for each execution, so do not expect the output to be the same on consecutive executions:

You can completely circumvent the encryption step by specifying an empty password. However, that is not recommended.

Password Protected Certificates

The two-parameter form of the CERTPRIVATEKEY function requires the certificate to be protected by the database master key. If you want to use CERTPRIVATEKEY with a certificate that is protected by a password, you have to specify that password when calling the function. For that, CERTPRIVATEKEY has a third optional parameter:

The returned value does not contain any information about the certificates original protection method. In fact, when recreating the certificate, you need to specify anew, if you want it to be protected by the database master key or by a password. If the password needs to be the same, you need to store that somewhere independent of the binary value that is returned by CERTPRIVATEKEY.

Summary

CERTPRIVAETKEY can be used to generate a binary representation of the private key of a SQL Server certificate. The resulting value is protected by the supplied password. That encryption step involves additional random data, so consecutive executions, even if they use the same password, will always result in different output values.