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.

About these ads
Posted in Security, SQL. Tags: , , . 21 Comments »

21 Responses to “SQL: SSL and SQL Server 2008 – Creating the Certificate”

  1. IIS: Disappearing SSL Certificate Problem Resolved « Nick Olsen's Programming Tips Says:

    [...] To resolve the problem I created the needed PKCS #12 Certificate File following the steps outlined here, and then clicked on the Import link (not the Complete Certificate Request… link) in the [...]

  2. SQL: SSL and SQL Server 2008 – Service Doesn’t Start – Error Code -2146885628 « Nick Olsen's Programming Tips Says:

    [...] correct format to import into the server’s Personal Certificate Store using the steps found here, I was finally ready to select the certificate and encrypt the database traffic. As outlined here I [...]

  3. Instalar certificado en IIS 7 | Aloquevamos.com Says:

    [...] final buscando un poco en Google, encontré esto. Es para SQL Server 2008, pero el mismo método sirve para IIS [...]

  4. Ryan Says:

    I spent hours searching for a solution to this problem and this article resolved it! Thanks a million, I was at a complete loss! SSL issuing companies really do over-complicate things and should supply tools on their site that allow users to do the public and private key merge or ask the customer for the private key and do the merge themselves.
    IIS7 should also alert the user that the certificate hasn’t installed correctly if a private key wasn’t supplied.

    • Nick Olsen Says:

      I couldn’t agree more!

  5. Carry Megens Says:

    Thanks for the info. The private key is however not in our received signed (Comodo) results.
    Where does IIS7 store the private key it has used to generate the Certificate Sign Request?
    Anybody know how to export that private key info?

    • Nick Olsen Says:

      You should have been given the private key when you purchased the certificate. If not, you need to contact the vendor.

      • Carry Megens Says:

        Hi Nick, nope the IIS7 server keeps the private key on the server. The Certificate Authority signs the Cerificate Sign Request (CSR) and returns the signed certificate holding their chain (intermediate and trusted authoritive root certificate).
        When you use the MMC with certificates plugin you can however see the certificate requests and export the private key form there.
        Hope this helps someone.

      • Nick Olsen Says:

        Glad you figured it out!

  6. some guy Says:

    Looks like someone is copying your posts.

    http://sqlanddotnetdevelopment.blogspot.com/2011/04/ssl-and-sql-server-2008-creating.html

    • Nick Olsen Says:

      Thanks for the heads up.

  7. Chris Missal (@ChrisMissal) Says:

    If you have already installed the certificate on another server, you can export the .pfx file if you know the password. This is what I needed when building a second server. Thanks for the info!

  8. Daniel Persson Says:

    Thank you, thank you, thank you!

  9. Taylor S. Says:

    Thanks a bunch, very easy to follow. Even someone as new to SSL Certificates as I am

    Odd that such a simple task is not handled by your CA. Could there be a reason they are delivered in this manner?

    • carry2webCarry Megens Says:

      @Taylor S
      The private key is as it says, yours, its private. Leave it in a secure place on your server. Do NOT expect a CA to deliver you a private key. They SIGN your certificate and make it official that it is yours. You SIGN with your private key and the certificate proves to recipients it is YOUR server.
      Hope this explains why this is by design as it is.

  10. Deeshay Says:

    Thank you for this tutorial!

  11. Sergei Says:

    Can anybody say where I can get private key for this, if I get certificate on goDaddy?
    Please help.

    • carry2webCarry Megens Says:

      @Sergei
      The private key is as it says, yours, its private. Leave it in a secure place on your server. Do NOT expect a CA to deliver you a private key. They SIGN your server certificate request and make it official that it is yours. Your server SIGN with its private key and the certificate proves to recipients it is YOUR server.
      Hope this explains why this is by design as it is.

  12. panksy Says:

    Awesome thanks you saved me a lot of time – well obviously I wasted some before I found this.

  13. Dwarf Says:

    I appreciate your tutorial it worked !

  14. Liam Says:

    Thank you, this saved me :-)


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 67 other followers

%d bloggers like this: