Blog About Contact

Creating and importing an external certificate to SQL Server

Published Mon, 3 Mar 2014 • 8 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 -

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.

  1. Generate 2048 bit RSA key

    openssl genrsa -des3 -out sql.key 2048

  2. Generate certificate signing request

    openssl req -new -key sql.key -out sql.csr

  3. Sign key with itself for 20 years (!)

    openssl x509 -req -in sql.csr -days 7300 -signkey sql.key -out sql.pem

  4. Convert to binary DER in sql.cer

    openssl x509 -in sql.pem -inform PEM -out sql.cer -outform DER

  5. Use pvk utility from above to convert to Microsoft format

    pvk -in sql.key -out sql.pvk -topvk

  6. 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.

You might also enjoy reading -

Discuss / Comment

There are 8 comments.

Add a comment

  • {{e.error}}

Thanks for your comment!/

Valid email address required.
Posting message, please wait...