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.
You might also enjoy reading -
- Diagnosing transactions causing SQL Server's transaction log truncation to fail
- Setting Up iptables for SSH/HTTP/HTTPS for new server install
- Unfortunately Interesting Java Generics
- 5 Reasons Why NetBeans Rocks