In an effort to harden security in my data infrastructure I am experimenting with using SSL to encrypt my database connections. I’m just using a self-signed certificate for testing and there are three primary steps to make this work:
- create the certificate
- load the certificate in the certificate store of the server
- configure SQL Server to use the certificate
Create the certificate
You’ll need to use the makecert.exe program to create a self-signed certificate. If you don’t already have it somewhere on your computer, you can find makecert.exe in the Windows SDK. Using makecert.exe is easy, simply change FQDN to the fully qualified domain name of your server and MyCert.cer to whatever file name you want and run it from the command line below:
makecert -r -n CN=”FQDN” -b 01/01/2000 -e 01/01/2050 -eku 220.127.116.11.18.104.22.168.1 -ss my -sr localMachine -sky exchange -sp “Microsoft RSA SChannel Cryptographic Provider” -sy 12 C:\MyCert.cer
If you want to get a great explanation of what all the various switches used mean check out the Obtaining Certificates section here.
Load the certificate
On the SQL Server open up the MMC and add the certificates
snap-in for the local computer. Import the certificate to the Trusted Root Certification Authorities store so it will be available to configure in SQL Server later. You also need to ensure the SQL Server service has permission to the certificate. To do this you right click on the certificate in MMC and select All Tasks > Manage Private Keys. From here you can grant read permission to the local SQL Server instance group.
If you neglect this step you may receive the error below when you restart SQL Server later.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error
Configure SQL Server
To add the certificate to SQL Server you open the SQL Server Configuration Manager and under SQL Server Network Configuration right click the Protocols for %instancename% go to Properties and the Certificate tab. Select the certificate you just created then restart the SQL Server service. Now clients will be able to connect to your SQL Server with encrypted communications.
Configuring SQL Server to use a self-signed certificate is fairly easy but there are some gotchas you can avoid:
- you must use the fully qualified domain name for the server name
- you must use the Microsoft RSA SChannel Cryptographic Provider for the self-signed certificate
- you must make sure the SQL Server service account (or a group it belongs to) has read permission to the certificate in the certificate store
You can get exhaustive documentation about SQL Server and SSL in Books Online.