SQL: SSL and SQL Server 2008 – Creating the Certificate

One of the nice security features of SQL Server 2005 and 2008 is that it allows you to encrypt the traffic between the server and client using your own SSL certificate. This can be done using a self signed or a third party certificate from a credible CA but note that using a self signed certificate is less secure and introduces the possibilty for a man in the middle attack. The process for doing such is outlined very well on many different websites including this MSDN article.

I requested a certificate for our domain from GlobalSign and received the certificate back in the form of a text file with three sections: Regular Certificate (X509), Intermediate Certificate, and the Private Key. The file looked like the following:

Regular Certificate (X509)
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Intermediate Certificate
-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----
Private Key
-----BEGIN RSA PRIVATE KEY-----
...
-----END RSA PRIVATE KEY-----

Having never worked with SSL before I wasn’t quite sure how to go from a text file to the needed PKCS #12 Certificate File format (.pfx file) to import the certificate into the server’s certificate store. After searching around I found that a tool called OpenSSL would do the trick. (You can download the binary files for Windows here.) It is a command line tool that allows you to manipulate SSL certificates in many different ways.

To generate the needed PKCS #12 Certificate File using OpenSSL do the following:

1. Download and install OpenSSL from here.
2. Save the three portions of the certificate into three separate files (Note: The Intermediate Certificate is optional, if you were not supplied with one, just skip the steps involving it):
   • privatekey.txt – Copy and paste the contents of the private key including the begin and end lines.
   • certificate.txt – Copy and paste the contents of the Regular Certificate including the begin and end lines.
   • intermediate.txt – Copy and paste the contents of the Intermediate Certificate including the begin and end lines.
3. Move the three files into the bin folder where you installed OpenSSL (default folder is C:\OpenSSL-Win32\bin).
4. Open the command prompt and navigate to the bin folder where you installed OpenSSL.
5. Enter the following line and press enter (Omit ‘-certfile intermediate.txt’ if you do not have an Intermediate Certificate):

   openssl pkcs12 -export -out certificate.pfx -inkey privatekey.txt -in certificate.txt -certfile intermediate.txt
   

After the process completes, there will be a certificate.pfx file in the bin directory that can be used to import the certificate into the servers personal certificate store.