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.

It can be that the SSL certificate, which you imported, have wrong KeySpec: `AT_SIGNATURE` instead of `AT_KEYEXCHANGE`. You can examine PFX using `certutil.exe -dump -v My.pfx` and search for `KeySpec = 1 -- AT_KEYEXCHANGE`. You can remove the certificate (export to PFX before if you not already have it as PFX) and import it once more using `certutil.exe -v -importPFX My.pfx AT_KEYEXCHANGE` — Oleg, Apr 24 '16 at 00:15
Is certificate installed in Computer certificate store? Is `CN` value from the certificate is the same as the full DNS name of the computer (where SQL server run)? Have the certificate the Subject Alternative Name with anothe "DNS Name" values? — Oleg, Apr 24 '16 at 00:44
Cert is installed in IIS Server Certificates, and being used successfully for a website. Cert is for `*.example.com` and the computer's name is `test.widows-server-test.example.com`. Is that what you mean? — Jonah, Apr 24 '16 at 01:04
I mean that for IIS Server you have less restrictions as for SQL Server. It's enough (for IIS Server) that *the client* can resolve `test.widows-server-test.example.com` and to get the IP address of your server or the rooter which makes NATings and forward the request to your server. On the other side SQL Server required that `CN` value of the certificate match the DNS name of your server (locally). You can try to generate self-signet certificate or server certificate from your domain (local CN). The certificate should be visible in SQL configuration. — Oleg, Apr 24 '16 at 08:31
Look at the section "SQL Server Certificate Requirements" from https://www.mssqltips.com/sqlservertip/3299/how-to-configure-ssl-encryption-in-sql-server/ for example or https://msdn.microsoft.com/en-us/library/ms191192.aspx, where you can read **"The name of the certificate must be the fully qualified domain name (FQDN) of the computer"**. — Oleg, Apr 24 '16 at 12:40
`-KeySpec KeyExchange` was the issue for me. (but I checked already that the Subject matches SQL servername/FQDN) — Tilo, Jun 15 '17 at 21:46

Oleg · Accepted Answer · 2016-04-25T20:44:44.093

30

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.

edited Apr 25 '16 at 20:44

answered Apr 24 '16 at 12:51

Oleg

220,925
34
403
798

Thanks, so I changed the computer name to "test.example.com" because of the [warning here](https://support.dnsimple.com/articles/ssl-certificate-hostname/), and now in `HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters` both Domain was "example.com" and Hostname was "test". The cert was still not showing up. I manually changed Hostname to "example.com" but the cert is still not showing up. Am I missing a step? – Jonah Apr 24 '16 at 20:16
@Jonah: Do you set "Force Encryption" to Yes in SQL Server Configuration Manager? Do you restarted SQL Server? Can the SQL Server be restarted? Can you see in the SQL ERRORLOG something like "The certificate [Cert Hash(sha1) ... ] was successfully loaded for encryption."? What exactly problem you have currently? Which error message you have? – Oleg Apr 24 '16 at 20:31
I just tried setting "Force Encryption" to Yes, and I restarted SQL Server from services successfully. The only possibly relevant entry in ERRORLOG is: `A self-generated certificate was successfully loaded for encryption.` Note, that would *not* refer to the wildcard cert I am trying to load, which is not self-generated. My current problem is the same as in the OP: The certificate dropdown is still blank. I cannot select a cert for SQL Server to use. – Jonah Apr 24 '16 at 20:47
@Jonah: Sorry, but your should post details of the certificate. If you created A self-generated certificate, than how exactly, which which properties, where (in which certificate store) you installed it and so on. – Oleg Apr 24 '16 at 20:53
To clarify, I was saying I did *not* create a self-generated certificate. The wildcard cert was issued by Komodo. – Jonah Apr 24 '16 at 20:55
@Jonah: You should open the certificate and examine **Subject** and **Subject Alternative Name** parts. The **Subject** part should have CN value which have to be the same like Hostname.Domain from the registry. – Oleg Apr 24 '16 at 21:00
update: looks like there are 2 additional certs on the server, which may be self-generated (I did not make them myself). They are all for different domains than the "example.com" which is the name of the server. – Jonah Apr 24 '16 at 21:00
The subject and Subject ALternative Name of the correct cert are both "*.example.com" which matches the Hostname and Domain from the registry (example.com). – Jonah Apr 24 '16 at 21:02
@Jonah: You can't use such certificate for SQL server. You can set `Domain` in registry equal to `example.com`, but you can't set `Hostname` equal to `*`. I think that you have to get another SSL certificate. Do you have IIS on the server? Is ports 80 and 443 of your computer are accessible from the internet? If yes then you can get certificate *for the computer* for free from https://letsencrypt.org/ wining https://github.com/Lone-Coder/letsencrypt-win-simple/releases for example – Oleg Apr 24 '16 at 21:09
hmmm, maybe i will have to do that. why does [this page](https://thesqldude.com/tag/wildcard/) claim that "SQL Server 2008 R2 and onwards support wildcard certificates"? – Jonah Apr 24 '16 at 21:15
@Jonah: You can read in the article the following: "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." The name `"*.example.com"` which have the certificate don't corresponds the requirement. Stop! I see more information which I have to read before... – Oleg Apr 24 '16 at 21:21
Okay, I see. I will try the self-signed route. Btw, on my production server (where I need to do this eventually, too) my website applications use the COMODO wildcard cert. Will installing a self-signed cert for Sql Server interfere with those? – Jonah Apr 24 '16 at 21:23
@Jonah: The article reference to https://support.microsoft.com/en-us/kb/258858 which is about support of wildcard certificates for HTTPS, but it's wrong that SQL Server can use it too. SQL documentation write always about the exact match of CN name from the Subject property of the certificate and fully qualified domain name (FQDN) of the server computer. Thus I still think that you can't use the certificate for SQL server. You can get for free the **individual certificate for the server** from https://startssl.com, but you have to validate before your domain. – Oleg Apr 24 '16 at 21:27
Oh, one last thing, do you know the answer to my question about possible conflicts between a self-signed cert and the wildcard cert on my production server? – Jonah Apr 24 '16 at 21:34
1

@Jonah: As soon I know all certificates can be installed at the same time in the certificate store. It's just the store. One service (or program) can use one certificate and otheother program will use another one. For example you can configure IIS fo use *specific* certificate for SSL. The existence any other certificates on the same store is not important. No conflicts should exist. – Oleg Apr 24 '16 at 21:45
thanks again, and if you get a chance, the saga continues: http://stackoverflow.com/questions/36830411/how-can-i-give-sqlserver-permission-to-read-my-ssl-key – Jonah Apr 25 '16 at 02:00
@Jonah: I wrote **UPDATED 2** part of my answer with many pictures. I think that you can came back to the usage of original certificate. I don't have and valid wildcard certificate, but I could successfully configure Let's Encrypt certificate to be used by SQL Server by placing SHA1 Hash value of the certificate on `Certificate` value of the Registry. I'd recommend you to try to do the same. – Oleg Apr 25 '16 at 20:48

score 7 · Answer 2 · answered Jun 14 '18 at 17:46

7

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.

answered Jun 14 '18 at 17:46

Joe Mroczek

91
1
2

This appears to be the case despite the fact that the value generated by SSCM is lowercase. Wonders never cease. – J Cracknell Jul 19 '18 at 21:18
After Oleg step this resolve my issue, just make it upper case - SQL Server Version 2016 – Dany Jan 03 '23 at 09:20

score 2 · Answer 3 · edited Jun 20 '20 at 09:12

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:

C:\Program Files\Microsoft SQL Server[Your Sql Server Instance]\MSSQL\
C:\ProgramData\Microsoft\Crypto\RSA\MachineKeys
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!!

score 2 · Answer 4 · answered Mar 23 '21 at 18:43

2

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$

answered Mar 23 '21 at 18:43

user15463676

21
1

1

You only need to give Read permission - this fixed my issue too. – Aidan Jul 28 '21 at 05:32
1

This is what I needed too, this needs upvotes! – Adam Nofsinger Nov 15 '21 at 22:39

score 1 · Answer 5 · answered Nov 22 '20 at 07:50

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!

score 1 · Answer 6 · answered Nov 02 '22 at 07:53

1

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

answered Nov 02 '22 at 07:53

David P

11
1

it's strange and seems to be contradictory. But for SQL Server 2019 it's indeed showing up in SQL server Configuration manager after changing it to lower case. – user1655072 Feb 06 '23 at 18:37

score 0 · Answer 7 · answered Nov 01 '18 at 11:40

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.

score 0 · Answer 8 · answered Jun 03 '19 at 10:58

0

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

answered Jun 03 '19 at 10:58

user2702772

137
7

score 0 · Answer 9 · edited Jan 27 '20 at 17:44

0

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.

edited Jan 27 '20 at 17:44

double-beep

5,031
17
33
41

answered Jan 27 '20 at 17:22

Donovan Kidd

1

score 0 · Answer 10 · edited May 13 '22 at 00:55

0

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

IIS Server Certificate list example

edited May 13 '22 at 00:55

Jeremy Caney

7,102
69
48
77

answered May 12 '22 at 12:00

user18033157

1
2

If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/31756725) – Reza Heidari May 16 '22 at 23:45

score -1 · Answer 11 · answered May 06 '22 at 09:03

-1

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

answered May 06 '22 at 09:03

László Kiss

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 09 '22 at 17:16

SSL Certificate missing from dropdown in SQL Server Configuration Manager

11 Answers11

Linked