SQL Server 配置管理器的下拉列表中缺少 SSL 证书

本文介绍了SQL Server 配置管理器的下拉列表中缺少 SSL 证书的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试配置 SQL Server 2014，以便可以使用 SSL 远程连接到它.服务器上安装了有效的通配符证书，并且证书的域名 (example) 与服务器的 FQDN (test.windows-server-test.example) 匹配.

问题是在 SQL Server 配置管理器中，证书没有列出，所以我无法选择它.

也就是说，我被困在

Certificate 值是 SHA1 哈希，可以通过检查证书的属性找到:

或证书的扩展属性，您可以通过使用 certutil.exe -store My 看到:

只需复制Cert Hash(sha1)"值，删除所有空格并作为 Certificate 值的值放置在注册表中.进行设置并重新启动 SQL Server windows 服务后，将在 C:Program FilesMicrosoft SQL Server...MSSQLLog 目录中的文件 ERRORLOG 中看到像线

2016-04-25 21:44:25.89 服务器证书 [Cert Hash(sha1)"C261A7C38759A5AD96AC258B62A308A26DB525AA"] 加载成功用于加密.

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) matches the server's FQDN (test.windows-server-test.example).

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.

解决方案

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, where test.widows-server-test.example is the FQDN of your computer. It's not enough that you use for example CN = *.example and Subject Alternative Name, which contains DNS Name=*.example and DNS Name=test.widows-server-test.example, DNS Name=test1.widows-server-test.example, DNS Name=test.widows-server-test2.example 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_MACHINESYSTEMCurrentControlSetServicesTcpipParameters

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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.SQL2014MSSQLServerSuperSocketNetLib, 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 FilesMicrosoft SQL Server...MSSQLLog directory the line like

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