It’s been while I did some writing, thought to share some good stuff came across during last couple of years, one of them was implementing SSL (wildcard certificate) with SQL Server 2012.
yeah!!! I did it.
I am not going to explain here on what is SSL and how it works and blah blah all the technical stuff, for that I will have related reference links at the end of this page that you can use to build the technical knowledge.
To make it simpler I will keep the article maximum to text only.
Note: wildcard SSL support was added to SQL Server 2008 R2 and later versions.
example of wildcard certificate *.xyzdomain.com
Step 1: (import SSL Certificate)
Import wildcard SSL certificate on the MS SQL Server. that’s going to be the usual import process that we perform mostly in our environments.
Step 2: (certificate not listed in SCM dropdown menu)
in most of the cases wildcard certificates are not shown in SQL Server Configuration Manager Dropdown list. and my method of configuration was to follow the registry keys and the support to SQL Server.
You can see the certificate if you open up mmc and add the snap-in for Certificates. When asked to chose the account, select “Computer Account” for the Local computer. Both of the above certificates will be present under Personal –> Certificates
once you see the certificate is listed here you need to use the following command to get the wildcard SSL certificate hash value.
open command prompt with elevated permissions and use the following command
*** certutil –store “my” ***
look at the highlighted Cert Hash(sha1) value, copy it and take it to notepad. or use the next command and redirect the output to a text file.
*** certutil –store “my” > c:\temp\certlist.txt ***
open this certlist.txt and look for your required wildcard certificate Hash value.
copy the value and carefully remove the spaces
Step 3: (add hash value to registry)
Open up the registry (regedit) and navigate to the SQL Server instance hive. the default location for SQL Server would be
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\SuperSocketNetLib
create a string value “Certificate” on the above location or modify already created value and Paste the hash value noted in step-2.
once completed this step restart SQL Server Service to load the SSL Certificate.
Step 4: (force encryption = true)
on the same registry location change the “ForceEncryption” value to 1 as show in the image of step-3
or use the SQL Configuration manager to enforce the encryption
once completed this step restart SQL Server Service to load the SSL Certificate.
Troubleshooting :
SQL Server Service not starting after adding the registry value, no need to panic, you need to check couple of things.
some of the errors can be seen in error logs.
TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.
TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.
Unable to load user-specified certificate [Cert Hash(sha1) “your certificate hash value“]. The server will not accept a connection. You should verify that the certificate is correctly installed.
1. verify that SQL Server Service user has rights to read the Certificate.
identify the service account it could be a domain account or any of the built-in accounts like LocalService/NetworkService by going to services.
grant read permission to this user on SSL certificate.
Open MMC with certificates snap-in for local computer
Expand Certificates (Local Computer) -> Personal -> Certificates and find the wildcard SSL certificate.
Right click on the certificate and click All Tasks -> Manage Private Keys.
Click the Add button under the Group or user names list box.
enter the SQL Server service user identified earlier and click ok.
Now start the SQL Service again and in my case it was like Bingo!!!!.
2. verify that SQL Server Certificate is not expired.
if the certificate is expired buy a new certificate any certificate authority and follow the same process.
3. verify that the hash value is added without any spaces.
this is tricky. export the selected registry key and open the *.reg (exported file) with notepad. now save as “type the new file name.reg” and choose the encoding “ANSI”.
open the newly created registry file and see if you find any “?” or space in the HASH value. if yes, remove it and save. now you can import this registry file by double clicking on it. and restart the service.
this step was important for me and I was able to solve my service starting issue with this step in my staging environment.
that’s all folk’s!!!
References :
How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
https://support.microsoft.com/en-us/kb/316898
Encrypting Connections to SQL Server
https://msdn.microsoft.com/en-us/library/ms189067.aspx
How to Enable Channel Encryption
https://blogs.msdn.microsoft.com/sql_protocols/2005/10/03/how-to-enable-channel-encryption/
Accepted wildcards used by server certificates for server authentication
https://support.microsoft.com/en-us/kb/258858
Configure Windows Service Accounts and Permissions
https://msdn.microsoft.com/en-us/library/ms143504.aspx#Registry
https://msdn.microsoft.com/en-us/library/ms143504.aspx#Windows
Sajid Fiaz 