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:

1. Using Makecert util:

• Firstly, the pre-requisite for using this method is to have Windows SDK installed on the machine.
• Navigate to the location where you have the makecert util and then Run the below command from elevated CMD prompt:
• Run the following command to create the certificate:

makecert -r -pe -n "CN=MININT-Q99PLQN.*************.com" -b 10/16/2015 -e 12/01/2020 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

• We will have the certificate created under the MMC console --> Certificate snap in --> Local Computer --> Personal section
• As per the parameters specified, the certificate will be created with the following set of specifications:
  • The common name of the certificate will be “MININT-*************.com” which is the FQDN of the machine.
  • The private key will be enabled for exporting.
  • Certificate will be created in the Computer account -> Personal -> Certificate store
  • Validity period will be 10-16-2015 to 12-01-2020
  • The server authentication will be enabled. [eku = 1.3.6.1.5.5.7.3.1]
  • Key Spec value will be set to 1. [AT_KEYEXCHANGE will be enabled]
  • The algorithm used here for encryption is Microsoft RSA SChannel Cryptographic Provider.

2.Using Certreq command:

• Firstly, we need to save the below script in a text document with a .inf extension.

[Version] Signature = "$Windows NT$" [NewRequest] Subject = "CN = MININT-Q99PLQN.*************.com" FriendlyName = test1.contoso.com MachineKeySet = true RequestType=Cert ;SignatureAlgorithm = SHA256 KeyLength = 4096 KeySpec = 1 KeyUsage = 0xA0 MachineKeySet = True Exportable = TRUE Hashalgorithm = sha512 ValidityPeriod = Years ValidityPeriodUnits = 10 [EnhancedKeyUsageExtension] OID=1.3.6.1.5.5.7.3.1

• 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 it does not require the Windows SDK installed and the key length can be subjected to changes where as if it is using makecert it would be by default set 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)

New-SelfSignedCertificate -DnsName MININT-Q99PLQN.*************.com -CertStoreLocation cert:\LocalMachine\My -FriendlyName test99 -KeySpec KeyExchange

Scenario: 2

I will be registering the SSL self-signed certificate using the following 2 methods:

• Through the SQL Server Configuration Manager
• Through explicit registration

Steps to be followed:

1. Through SQL Server Configuration Manager:

• Initially need to check the health of the certificate using the CheckSQLssl.exe tool.
• Here are the pre-requisites for the SSL certificate to use it for SQL server:
  • Certificate must be present in the Local computer certificate store or the current user certificate store.
  • Certificate age must be present within the validity period.
  • Certificate must be meant for server authentication. (EKU should specify Server Authentication [1.3.6.1.5.5.7.3.1])
  • Certificate must be created using the KEY_SPEC option of AT_KEYEXCHANGE (KEY_SPEC=1)
  • Common name of the certificate should be the host name or the FQDN of the server computer.
  • Running the tool using the command prompt will generate the following report

• On getting all the validation checks ‘OK’ regarding the pre-requisites of the certificate we can go ahead register it.
• On SSCM, expand SQL server network configuration -> Right click on ‘Protocols for <Instance name> -> Properties. Turn the ‘Forced Encryption’ to Yes.

• Click on the  ‘Certificate’ tab where the certificates will be listed and select the required certificate from the list and restart the service.

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

The certificate  [Cert Hash(sha1) "BFB714872C7B2CD761ADEB1893BFC99581D3420B"]  was successfully loaded for encryption.

• To verify the thumbprint, in MMC double click on the certificate which is loaded, click on  ‘Details’ tab and click on thumbprint in the list.

2.Through explicit registration:

• Even after the validation checks are proved to be OK by the CheckSQLssl tool and still if the certificate is not listed in SSCM, then follow this technique.
• Run ‘regedit’ and open HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer\SuperSocketNetLib and enter the thumbprint of the certificate without spaces to the ‘Certificate’ value.
• Note that in case of a clustered environment in those nodes whose FQDN does not match with the certificate name, the certificate will not be listed in the configuration manager. In that case explicit registration is the only way to register the certificate. The reason being to use SSL for clustered environment, the Certificate Name should be the Virtual Network Name of the SQL Failover Cluster instance.
• Then on restarting the SQL service the SSL certificate will be loaded to SQL and this can be verified again by analyzing the SQL server error logs.

Hope this helps.. Happy creating and registering!!