Creating and importing an external certificate to SQL Server

Published Mon, 3 Mar 2014 • 6 comments

This was kind of a pain to get working, so I'm logging the steps here. Certificates can be used in SQL Server to authenticate remote communication (e.g. for mirroring).

Most resources show you how to create certificates for this directly in SQL Server, but for the purpose of being able to manage configuration externally, I think it's better to be able to generate an x509 certificate using normal tools (e.g. OpenSSL) and import that into SQL Server.

There's two bits of information on this that are not very well publicised -

The certificate itself must be in DER binary format to import correctly.
The private key must be in Microsoft's proprietary "PVK" format.

If these requirements aren't met, then a you will get a cryptic:

Msg 15468, Level 16, State 6, Line 1
An error occurred during the generation of the certificate.

To generate the proprietary PVK file from a regular RSA private key generated in OpenSSL a 3rd party utility is required

For this example, we'll generate a key and self-signed certificate using OpenSSL and convert it to the correct format for SQL Server, and import the certificate.

Generate 2048 bit RSA key

openssl genrsa -des3 -out sql.key 2048
Generate certificate signing request

openssl req -new -key sql.key -out sql.csr
Sign key with itself for 20 years (!)

openssl x509 -req -in sql.csr -days 7300 -signkey sql.key -out sql.pem
Convert to binary DER in sql.cer

openssl x509 -in sql.pem -inform PEM -out sql.cer -outform DER
Use pvk utility from above to convert to Microsoft format

pvk -in sql.key -out sql.pvk -topvk
Now in SQL Server:

create certificate mysqlcert
      from file = 'c:\temp\sql.crt'
      with private key 
        (file = 'c:\temp\sql.pvk', 
         encryption by password = 'password entered during key generation', 
         decryption by password = 'password entered in step above')

About the Author

Richard Nichols is an Australian software engineer with a passion for making things.

Follow him on twitter or subscribe by RSS or email.

Discuss / Comment

There are 6 comments.

chris allen on Fri, 9 Jan 2015 at 10:18

You saved me a lot of time- thank you!
Jeremy Lubich on Wed, 21 Jan 2015 at 12:31

I was working for 3 weeks to get stronger certificates setup that could import into SQL. This solution is great, because it avoids going through our CA by simply creating the self signed certs. I never could understand when we were using openssl why SQL wouldn't use the pvk files it created. Now, I see the pvk converter has to put them into the right Microsoft format. I sure wish this was on the OPENSSL for TDE blog MS put out.

Note: I tried a couple certificate lengths, and found that even though SQL will import up to 4096 bits, when you use them for creating the database encryption key, it gives an error "An error occurred during encryption". I backed the size down to 2048, and it created the TDE key, no problem.
CY on Thu, 14 May 2015 at 10:58

Your final import script references [sql.crt] And while your example code creates 5 files, none of them are named .CRT.

I'm assuming that you meant .CER for the import certificate instead of .CRT??
Graham Hardie on Thu, 18 Aug 2016 at 08:39

Step 5 - can be done in OpenSSL as well now:

openssl rsa -in <filename>.key -outform PVK -pvk-strong -out <filename>.pvk
Morten Skarstad on Fri, 26 Aug 2016 at 07:48

Thank you!
benzin on Thu, 19 Jan 2017 at 05:12

I end up with this MS SQL message (SQL Server version 12.0.2): Msg 15208, Level 16, State 19, Line 1 The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Creating and importing an external certificate to SQL Server

Published Mon, 3 Mar 2014 • 6 comments

About the Author

You might also enjoy reading -

Discuss / Comment

Add a comment