OPenSSL certificate for SQL Server

I created a SSL certificate using OpenSSL for my Microsoft WebServer 2003, IIS 6.0. I am able to install and use it on the client and the server side as well. Now I want to use it for my SQL Server express 2008 R2 but it does not appear in the certificate list of the SQL Server configuration manager. I tried a lot of this hints in the internet but none of them worked. I also found a list of prerequisites on the MS server but I am not sure how to understand it:

1. The certificate must be in either local machine or current user certificate store.

2. The certificate must have a good time stamp, i.e. the current system time must be in the valid time window of the certificate.

3. The certificate must be meant for Server Authentication, i.e. the certificate's Enhanced Key Usage property has to be turned on for Server Authentication (1.3.6.1.5.5.7.3.1).

4. The Certficate’s key spec must include AT_KEYEXCHANGE property. Usually, the certficate's key usage should include Key Encipherment.

5. The certificate’s subject CN must match the FQDN of the server machine, or the FQDN of the virtual server if the server runs on failover cluster. This implies that required certificates must be provisioned on all nodes in the failover cluster.

Items 1, 2 and 3 are established. Item 4 is default as far as I was able to read. Item 5 is also true: the CN matches the FQDN.

Can anybody help me?

The following steps are performed by me for creating the certificate:

set home=c:\openSSL\bin 
set randfile=File.rnd 
openssl genrsa -des3 -out firma-private.key 2048 
openssl req -new -config 2.config -key firma-private.key -x509 -days 3065 -out firma-ca.cer 
openssl x509 -req -days 3065 -in certreq.txt -CA firma-ca.cer -CAkey firma-private.key -CAcreateserial -out extern-server.cer 

Contents of 2.config:

[ req ] 
default_bits       = 2048 
distinguished_name = req_DN 
string_mask        = nombstr 

[ req_DN ] 
countryName                     = DE 
countryName_default             = DE 
countryName_min                 = 2 
countryName_max                 = 2 
stateOrProvinceName             = N........ W........ 
stateOrProvinceName_default     = N........ W........ 
localityName                    = W........ 
localityName_default            = W........ 
0.organizationName              = S.....-C...... 
0.organizationName_default      = S.....-C...... 
organizationalUnitName          = Software 
organizationalUnitName_default  = Software 
commonName                      = s.....-开发者_如何学Pythonc.......dyndns.org 
commonName_max                  = 64 
commonName_default              = s.....-........dyndns.org 
emailAddress                    = Info@S.....-C.......de 
emailAddress_max                = 40 
emailAddress_default            = Info@S.....-C.......de 

Thanks in advance,

Andree

I know this is months old but I ran across it in my search for the same answer.

I found the fix at This Link

Basically it seems the problem was that the SQL service, running as "Network Service" didn't have permission to use the key. I followed the steps to grant Network Service the rights to the key and all worked perfectly.

Don't forget to include the private key to the file you are installing in the certificate store, otherwise the certificate will not show up in the list. Also check that you used the correct FQDN as the certificate's common name, otherwise it won't show up either.

Here's what I did:

# generate key    
openssl genrsa -des3 -out server.key 2048

# remove pass
openssl rsa -in server.key -out server.key

# generate sign request, be sure to include the correct FQDN
# (host name followed by primary dns suffix)
openssl req -new -key server.key -out server.csr

# generate self signed certificate
openssl x509 -req -in server.csr -signkey server.key -out server.crt

# include both the certificate and the private key in a PKCS12 keystore
# (leave the export key empty)
openssl pkcs12 -export -out server.p12 -inkey server.key -in server.crt

Then add the server.p12 file to the local computer's certificate store (with the certificates snap-in). Finally, you should be able to select the certificate in the SQL Configuration Manager.

However, the server won't start if it has no read access to the private key you just imported. To do so you must also give the account that runs the SQL server (e.g. NETWORK SERVICE) the appropriate rights. Simply right click on the certificate you just imported (in the certificates snap-in) and choose "Manage Private Keys" under "All Tasks". There you can add the service account and give it read permission.