Using OpenSSL, I'd like to generate a self-signed certificate for usage with Microsoft SQL server 2008 R2. This might be unusual, but I'd like to learn what's going on behind the scenes.
I have several problems:
1) The certificate, after having it installed into the trusted root certificates of the local computer, is not listed in MSSC utility.
2) Therefore, I have forcibly added it by changing the registry as described in several posts. After having done so, the SQL server service didn't start any more. The event log shows
Unable to load user-specified certificate [Cert Hash(sha1) "350F3D0D987525361CE4CC5E6D72274228C5AF5B"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
followed by other error messages which are undoubtedly caused by that first one.
I am nearly sure that the certificate I generated does not fulfill SQL server's requirements. However, for the life of me, I can't figure out what is missing. This leads to the third problem:
3) Many posts out there recommend the tool CheckSQLssl.exe to examine the certificate and to debug issues. However, I can't find that tool anywhere (not on my SQL server, no download available, even Microsoft doesn't seem to know about it).
These are the commands I am using to generate the certificate:
openssl genrsa -out key.pem 2048 openssl req -new -key key.pem -out csr.pem -subj /CN=server.example.com openssl req -x509 -sha256 -days 36500 -key key.pem -in csr.pem -out cert.pem openssl x509 -extfile /etc/ssl/pp-openssl.cnf -sha256 -days 36500 -signkey key.pem -in cert.pem -out ext.pem openssl pkcs12 -export -out cert.pfx -inkey key.pem -in ext.pem
So I generate the key and a CSR which contains the server's FQDN as common name (lines 1 and 2). Then I sign the CSR and generate a self-signed X509 certificate (line 3). Then I add the EKU for Server Authentication (see below) (line 4) to the certificate and finally convert it into PFX format (line 5).
pp-openssl.cnf just contains one line to add the correct EKU:
When examining that PFX certificate on the server, it seems that it should meet the requirements:
C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx |find "EXC" KeySpec = 1 -- AT_KEYEXCHANGE Algorithm Class: 0xa000(5) ALG_CLASS_KEY_EXCHANGE
and (only relevant snippet of the output)
C:\Users\Administrator\Desktop>certutil -dump -v cert.pfx 220.127.116.11: Flags = 1(Critical), Length = c Enhanced Key Usage Server Authentication (18.104.22.168.22.214.171.124.1)
I have read that the EKU and AT_KEYEXCHANGE are important requirements; both are obviously met.
What else could be missing? I'll provide the complete output of
certutil -dump -v if it helps.