How to set key spec or KEYEXCHANGE property when generating a self-signed certificate using openssl

Question

I am using open ssl on 'windows 2012R2' to generate a self-signed certificate. Using the command below I have generated the certificate

openssl genrsa -des3 -out ab.key 
openssl req -new -x509 -key ab.key -out ab.crt
openssl pkcs12 -inkey ab.key -in ab.crt -export -out ab.pkcs12
openssl x509 -in ab.crt -out ab.pem

Getting the error while import the certificate - The selected certificate  does not have the KeySpec 
Exchange property. This property is required by SQL Server to import a certificat Import error: 0x2, Windows Native Error: 0x80092004

I have checked the opennssl config file but could not get like where to set this property.

Answer 1

One way of doing it is to convert your certificate to pfx (pkcs12) format and it will get the default value for KeySpec i.e KeySpec = 1 -- At_KEYEXCHANGE

Use the openssl command 'pkcs12' as following:

openssl pkcs12 -inkey mssql-key.pem -in mssql-cert.crt -export -out mssql-cert.pfx

You will get follwoing in the output of certutil:

certutil -dump -v .\mssql-cert.pfx

Output Extract:

.......

hCryptProv = 000001372C300D00

KeySpec = 1 -- AT_KEYEXCHANGE

......

Answer 2

One thing I found after much trial and error is that SQL Server does not work well if your cert includes a chain of certs, or at least that was the issue in my case. My CA is Microsoft and I was issued a new cert for my server using the FQDN of the host, all well and good. But when I exported the certificate from MMC to a PFX file, all of the certs in the Certification Path were exported by default. SQL Server was importing the issuing certificate just above the host certificate, and that one did not have the KEYSPEC = 1 option set. It is an easy fix, if you know where to look. In the Export Certificate Wizard in MMC, just clear the option to "Include all certificates in the certification path" and then SQL Server imports the correct one.