Sunday, 5 April 2009

Using SSL Certificates for SQL 2005 & 2008

I'm in the process of going back and editing some of my older posts so that I can link to this blog "without shame" and I noticed a posting about changing the MSX Encrypt Channel option to 0. I don't do that any more. I WANT my SQL servers to have SSL certs and I actively force SSL encryption on all traffic to and from my MSX servers.
So; how does one setup a SQL server to use an SSL cert in a domain when you're running the SQL service with a low-privliged (non-admin) domain user account (you ARE doing that, right?!)?

Simple.

  • First off, you're going to need to get a copy of winhttpcertcfg. Download that from MS and install it on the SQL host.
  • Then you'll need to request a cert from your domain CA and install it in the local computer store. I won't cover that process here, but it's not overly difficult.
  • Ensure you get a cert that can do SSL (webserver certs work 100%).
    When you request the cetificate ensure that the name is SQLHOSTNAME.FQDN For clusters use VIRTUALHOSTNAME.FQDN and ensure the private key is marked exportable. Store the certificate in the local computer store.
  • Once you have the certificate installed you need to grant access to the private key to the SQL service account: winhttpcertcfg.exe -c -g LOCAL_MACHINE\MY -s "SQLHOSTNAME.FQDN" -a "DOMAIN\SQLSERVICE_ACCOUNT"
  • Once you successfully grant access for the service account use the SQL Server Configuration Tool snap-in, and setup your protocols to use the cert. Then bounce the SQL service. You should see a note in the startup log that says something like "successfully loaded certificate for encryption" and NOT "loaded self-signed certificate for encryption".
The process is a little more involved for clusters (at least Veritas Storage Foundations clusters) and involves exporting the private key, importing it on the other nodes, and editing the registry with the certificate hash value as the SQL config tool mmc won't list the cert in its drop-down because it matches the VIRTUAL name and not the HOST name.

See this KB for all the MMC stuff I was talking about.
See this MSDN article for more info and an annotation from me.
UPDATE: Looks like I blogged about this previously as well!

No comments: