You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

Just spent some time figuring out why a Microsoft SQL Server didn't grok my fresh TCS certificate.

Generating key materials

Did this on a host with OpenSSL:

openssl req -new -keyout server.key -out server.csr -subj /CN=testing.terena.com/

After sending the CSR to SURFnet, I got a signed certificate (server.pem) back, and the 3 chains concatenated into one file (chain.pem).

Now combine all certificates into one file, and create a PFX from these materials:

cp server.pem all.pem
cat chain.pem >> all.pem
openssl pkcs12 -export -inkey server.key -in all.pem -out server.pfx

Copy this file to the Windows server, and run mmc, then add the Certificates snap-in.

When it asks for who to manage certificates, select an account that the SQL Server has access to.

In our case that was the "Local System" account, and we were running the snap-in as Administrator, so all is well.

Expand: Console Root -> Certificates (Local Computer) -> Personal.

Right-click -> All Tasks -> Import.

Now navigate to the pfx file and import it. Include all extended properties.

I kept Mark this key as exportable unchecked, as I already have the key material in PEM format in a different place.

We don't need this, and any malicious export attempt will be more difficult this way.

 

Configuring SQL Server to use the certificate

Run the SQL Server Configuration Manager, expand the SQL Server Network Configuration, and right-click Protocols for MSSSQLSERVER (or whatever your instance is called).

On the Certificate tab you should be able to see your certificate.

In my case nothing would show up (sad)

According to How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager), "the name of the certificate must be the fully qualified domain name (FQDN) of the computer". The TCS certificates we use can only contain a valid FQDN as the Subject's Common Name (CN), so this is OK. I checked permissions and those seemed to be OK as well.

Turned out that the server did not have a Full Computer Name yet...

After fixing that the certificate showed up and everything worked. After setting Protocols for MSSSQLSERVER to Yes, I checked with Wireshark and indeed no more plain text queries.

 

PS: you can also use this certificate to secure other stuff, like Remote Desktop.

  • No labels