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:
- Download and install OpenSSL from here.
- 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.
- Move the three files into the bin folder where you installed OpenSSL (default folder is C:\OpenSSL-Win32\bin).
- Open the command prompt and navigate to the bin folder where you installed OpenSSL.
- 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.
September 8, 2010 at 3:29 pm
[...] 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 [...]
September 8, 2010 at 4:29 pm
[...] 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 [...]
December 4, 2010 at 3:12 am
[...] final buscando un poco en Google, encontré esto. Es para SQL Server 2008, pero el mismo método sirve para IIS [...]
February 17, 2011 at 3:38 am
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.
February 17, 2011 at 7:34 am
I couldn’t agree more!
June 1, 2011 at 10:10 am
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?
June 2, 2011 at 7:10 am
You should have been given the private key when you purchased the certificate. If not, you need to contact the vendor.
June 15, 2011 at 6:25 am
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.
June 15, 2011 at 7:20 am
Glad you figured it out!
July 10, 2011 at 6:17 pm
Looks like someone is copying your posts.
http://sqlanddotnetdevelopment.blogspot.com/2011/04/ssl-and-sql-server-2008-creating.html
July 11, 2011 at 7:11 am
Thanks for the heads up.
March 23, 2012 at 1:10 pm
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!