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).
Active2 months ago
Apr 12, 2018 As an alternative way, you can first launch Console Root via running mmc, then click 'File'Add/Remove snap-in', then scroll down to certificates, click add, choose the computer accountLocal computer, this can launch the certificates in Local machine mode. Certificates in the AIA Container are published to clients in the Enterprise physical store described in more detail below. Registry (Physical Store) These are certificates published into the local registry of the computer. The certificates can be found in the following registry key: HKEYLOCALMACHINE SOFTWARE Microsoft SystemCertificates. Feb 25, 2019 To view your certificates in the MMC snap-in, select Console Root in the left pane, then expand Certificates (Local Computer). A list of directories for each type of certificate appears. From each certificate directory, you can view, export, import, and delete its certificates. View certificates with the Certificate Manager tool.
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.
marc_s
606k137137 gold badges11591159 silver badges12921292 bronze badges
JonahJonah
8,4741515 gold badges6565 silver badges132132 bronze badges
5 Answers
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
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
Local Machine Certificate Manager Login
2016-04-25 21:44:25.89 Server The certificate [Cert Hash(sha1) 'C261A7C38759A5AD96AC258B62A308A26DB525AA'] was successfully loaded for encryption.
OlegOleg
213k99 gold badges313313 silver badges722722 bronze badges
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.
Joe MroczekJoe Mroczek
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.
How To Install Certificates On Computer
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.
Open Local Machine Certificate Store
googled it and found out a solution:
Make sure the windows account running SQL Server service (
NT ServiceMSSQLServer in my case) has full permissions to the following folders/register entry:
I checked No.1
NT ServiceMSSQLSERVER has already had the permission.
I checked No.2,
NT ServiceMSSQLSERVER 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!!
RickyRicky
5,91333 gold badges2121 silver badges3131 bronze badges
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.
Mary NobakhtMary Nobakht
An additional failure mode is key length - SQL requires a minimum keylength of 2048. With DH channel disabled.
user2702772user2702772
Certificate Manager Windows 7Not the answer you're looking for? Browse other questions tagged sql-serverssliiscertificate or ask your own question.Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |