SSL on SQL – Foundation

A few days back, I was working with one of our partners who had a requirement of creating a SSL self-signed certificate through MMC console. As we are already aware that it is a complex and a tedious procedure, tried developing a script to ease the task for us. Also found that there were a lot of partners asking for assistance in having a script based approach to create the certificates.
Tried finding a way out by looking through various discussion forums which yielded nothing, but queries to build a script to accomplish the task. Addressing this requirement of the partner pool, here is the blog explaining the script based way of creating the Self-signed certificates and registering them meeting the pre-requisites of SQL server.
By developing the script based way of creating the certs, it is just at the run of a command we will get the SSL self-signed certificates created and ready to be registered. Along with the creation of the certificate, this blog also explains the different ways of registering those certificates.

Scenario 1:

I will be creating a SSL self-signed certificate using the following 3 methods:

Using Makecert util from the SDK.

Using certreq command and a script.sine

Using powershell command.

Steps to be followed:

Using Makecert util:

Firstly, the pre-requisite for using this method is to haveWindows SDKinstalled on the machine.

Navigate to the location where you have themakecertutil and then Run the below command from elevatedCMDprompt:

Navigate to the location where you have saved this request.inf file and then Run the below command from elevated CMD prompt

Certreq -new -f .inf .cer

We will have the certificate created under the MMC console --> Certificate snap in --> LocalComputer --> Personal section

The advantages of this technique is that itdoes not requirethe Windows SDK installed and the key length can be subjected to changes where as if it is usingmakecertit would be bydefaultset to '2048' for 'RSA' and '512' for 'DSS'

3.Using Power-shell command

Here is the approach to create the SSL certificate satisfying the pre-requisites to load it for SQL server using the power-shell command.

Run Powershell as an administrator and enter the following command (where DnsName = Host name or FQDN of the machine)

Click on the‘Certificate’ tabwhere the certificates will be listed and select the required certificate from the list andrestart the service.

Thus the SSL certificate will be loaded to the selected SQL server and this can be verified by analyzing theSQL error logsfor the below message and verifying it with the thumbprint of the certificate in MMC.

To verify the thumbprint, in MMC double click on the certificate which is loaded, click on‘Details’ taband click onthumbprintin the list.

2.Through explicit registration:

Even after the validation checks are proved to be OK by theCheckSQLssltool and still if the certificate is not listed in SSCM, then follow this technique.

Run ‘regedit’ and openHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLiband enter thethumbprintof the certificate without spaces to the ‘Certificate’ value.

Note that in case of aclustered environmentin those nodes whoseFQDN does not matchwith the certificate name, the certificate will not be listed in the configuration manager. In that caseexplicit registrationis the only way to register the certificate.

Then on restarting the SQL service the SSL certificate will be loaded to SQL and this can be verified again by analyzing theSQL server error logs.