SSL Certificate missing from dropdown in SQL Server Configuration Manager

Question

I am trying to configure SQL Server 2014 so that I can connect to it remotely using SSL. A valid, wildcard cert is installed on the server, and the cert's domain name (example.com) matches the server's FQDN (test.windows-server-test.example.com).

The problem is that in SQL Server Configuration Manager, the certificate is not listed, so I cannot select it.

That is, I am stuck on step 2.e.2 from this MS tutorial.

Answer 1

so if your certificate is not showing in the list, you need to create a new valid one.

when creating my certificate request this is everything i did to eventually fix it: I had to use a legacy certificate request (not CNG)

certificate properties:

SUbject Tab

Subject Name: Common Name: MYPC01.Domain.local (as in its case sensitive)

Alternative Name: DNS :

• MYPC01.Domain.local
• MYPC01
• mypc01

Extensions Tab

Key usage: Digital signature, Key Encipherment

Extended Key Usage: Server Authentication

Private Key

Cryptographic Service Provider

Microsoft RSA SChannel Cryptographic Provider (encryption)

NOTE im not security expert ive no idea what this means, but i tried something else it didnt work and got an error, the ms docs said to use this RSA provider

Key Options

Key size 2048

enable Make Private key Exportable

Key Type

select "Exchange"

Save the req file somewhere

inside microsoft certificate services certserv

Request certificate

advanced request

Submit a certificate request by using a base-64-encoded CMC or PKCS #10 file, or submit a renewal request by using a base-64-encoded PKCS #7 file.

Submit a Certificate Request or Renewal Request

open the certificate request file in notepad, copy / paste the contents into this page

select template: Web server

submit

download the certificate

double click the certificate to open it, then hit the import certificate button.

import into the computer account

open sql configuration manager , find the sqlserver service, right click > properties select and copy the user it is running as... in my case nt service\MSSQLSERVICE

open the certificates mmc snap in

your certificate should be listed under: Personal > Certificate

right click the certificate > All tasks > manage private Keys

click add, find the user your sqlserver is running as by pasting the text you copied in and check names (you might need to change the location to the local server)

make sure your sql user has read permissions, hit ok.

go back to sql configuration manager

sql Server Network configuration> Protocols for MSSQLSERVER (instancename) > righclick >properties

hopefully at this point it should be listed.

you need to then restart the sql service.

if the sqlserver service fails to start, check you have used a valid Cryptographic Service Provider. as stated above, for me it had to be Microsoft RSA SChannel Cryptographic Provider (encryption). you can go back to sqlserver config manager, and clear the invalid certificate from the protocols, this should allow you to restart the server, go to the sqlserver logs and get any error messages.

I hope this helps somebody else, as i have spent 3 days trying to get this to finally work

you can then verify its is working by opening ssms, connect by using the FQDN (mypc01.domain.local)

options:

enable encrypt Connection

enable Trust Certificate

connect to the server, in the properties pane, under connection details it has Connection Encrypted: Encrypted

I think you need to then change the connection strings your programs use to make the sql driver use ssl.

Answer 2

Good answer from Oleg !
The missing thing here was to ANSI encode the "thumbprint" string (no space, uppercased) because something invisible bug...

In the "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Log\ERRORLOG" I had the 0xd "The data is invalid" error.

https://learn.microsoft.com/en-US/troubleshoot/sql/database-engine/startup-shutdown/fails-start-error-17182

Answer 3

SQL Server 2019 I found that the certificate thumbprint had to be entered into the certificate registry key in lower case for Configuration Manager to see it.

SQL Server will read the registry value and use it whether the registry key is in upper or lower case. But configuration Manager will only display it if it is in lower case

Answer 4

My problem was that the Certificate Store was for WebHosting, but to see the certificate in SSRS it must be Personal.

Answer 5

USE UPPER CASE for Certificate in Registry editor LOL Still not shown in config manager but TLS is working for SQL connections.

Answer 6

I was still having problems even after following the above. This is my fix: in the certificates mmc right click the certificate All tasks->Manage Pricate Keys. Give the service account full control. In my case I am using NT Service\MSSQL$

Answer 7

I faced similar issue in SSRS, wherein certificate issued by microsoft active directory CA was not visible in the dropdown in SSRS. After lot of searches, trial and error I could fix it by following this link.

https://learn.microsoft.com/en-us/archive/blogs/sqlserverfaq/can-tls-certificate-be-used-for-sql-server-encryption-on-the-wire

Brief of it is as below: The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer.

So in our case we suggested to request the Certificate Authority to change the Subject name to ABC-SQLServer.abc.local (FQDN of SQL Server) instead of abc-corp.abc.com Once this change was done, we loaded certificate again in MMC and now we could see the certificate loaded in SQL Server Configuration Manager!

Hope it helps someone facing same issue!

Answer 8

I have also run into an issue copying out of the MMC as detailed in the article here. Using the certutil and copying that into the registry value worked perfectly.

Answer 9

Once I followed steps in Updated 2 section of accepted answer, I can't start the SQL Server service, got those errors in Event Viewer:

Unable to load user-specified certificate [Cert Hash(sha1) "thumbprint of certificate"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.

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.

got error in SQL Server error log:

The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.

googled it and found out a solution:

Make sure the windows account running SQL Server service (NT Service\MSSQLServer in my case) has full permissions to the following folders/register entry:

1. C:\Program Files\Microsoft SQL Server[Your Sql Server Instance]\MSSQL\
2. C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
3. HKLM\System\CurrentControlSet\Services\WinSock2\Parameters

I checked No.1 NT Service\MSSQLSERVER has already had the permission.

I checked No.2, NT Service\MSSQLSERVER has no permission and I added the permission. It popped up an error saying one of files in that folder was denied the operation, but I just ignored it (nothing else I can do)

I didn't check No.3 and tried starting SQL Server, it worked!!

Answer 10

An additional failure mode is key length - SQL requires a minimum keylength of 2048. With DH channel disabled.

Answer 11

I logged on to the server with SQL Server domain account( had to add the account to local admins temporarily) and imported the certificate in personal folder of the SQL Server service account. rebooted the server, and then SQL Server could see the certificate. Hope it helps someone.

Answer 12

I want to add this for future folks that may stumble on a similar issue I encountered with SQL 2016 SP2 and failover cluster. The certificate thumbprint added to the registry had to be all upper case.

Hope this helps the next guy.

Answer 13

After communication in comments I can suppose that your main problem is the CN part of the certificate which you use. To have successful TLS communication for IIS Server one have no such strong restrictions like SQL Server has.

Microsoft require (see here) that The name of the certificate must be the fully qualified domain name (FQDN) of the computer. It means that the Subject part of the certificate looks like CN = test.widows-server-test.example.com, where test.widows-server-test.example.com is the FQDN of your computer. It's not enough that you use for example CN = *.example.com and Subject Alternative Name, which contains DNS Name=*.example.com and DNS Name=test.widows-server-test.example.com, DNS Name=test1.widows-server-test.example.com, DNS Name=test.widows-server-test2.example.com and so on. Such certificate will be OK for TLS, but SQL Server will discard it. See the article, which describes close problems.

I recommend you to create self-signed certificate with CN equal to FQDN of the SQL Server and to verify that the certificate will be seen by SQL Server Configuration Manager.

UPDATED: I analysed the problem a little more with respect of Process Monitor and found out that two values in Registry are important for SQL Server Configuration Manager: the values Hostname and Domain under the key

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

If I change Domain and Hostname to the values which corresponds CN of the certificate then the certificate will be already displayed in the SQL Server Configuration Manager. It could be not all problems, but it shows that SQL Server required much more as a web server (IIS for example).

UPDATED 2: I examined the problem once more in details and I think I did found the way how one can configure common SSL certificate which you already have (for example free SSL certificated from Let's Encrypt, StartSSL or some other).

It's important to distinguished what do SQL Server Configuration Manager from the configuration required by SQL Server. The Certificate tab of the properties of the Configuration Manager have more hard restrictions as SQL Server. I describe above only the restrictions of SQL Server Configuration Manager, but one can make configuration directly in the Registry to use more common SSL/TLS Certificate by SQL Server. I describe below how one can do this.

What one need to do one can in the Registry under the key like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\SuperSocketNetLib, where the part MSSQL12.SQL2014 can be a little different in your case. The SQL Server Configuration Manager help us to set two values in the registry: ForceEncryption and Certificate:

The Certificate value is SHA1 hash which can be found by examining the properties of the certificate:

or extended properties of the certificate, which you see by usage certutil.exe -store My:

One need just copy the "Cert Hash(sha1)" value, remove all spaces and to place as the value of Certificate value in the Registry. After making the settings and restarting SQL Server windows service one will see in file ERRORLOG in C:\Program Files\Microsoft SQL Server\...\MSSQL\Log directory the line like

2016-04-25 21:44:25.89 Server The certificate [Cert Hash(sha1) "C261A7C38759A5AD96AC258B62A308A26DB525AA"] was successfully loaded for encryption.