SSL error connection into SQL Server 2016 Standar on Windows Server 2016 Standard?

Question

SSL error connection into SQL Server 2016 Standar on Windows Server 2016 Standard?

techresearch7777777 1,961

Hello, we have Microsoft SQL Server 2016 Standard Edition (64-bit) on Windows Server 2016 Standard.

Application team end user is getting following connection error when connecting from Redhat 9 and OpenJDK-17:

ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "Certificates do not conform to algorithm constraints". ClientConnectionId:dd076007-a438-4bd4-9eb5-f5e7f62bbbf3)

Nov 22, 2024 4:28:38 PM org.apache.catalina.core.ApplicationDispatcher invoke

SEVERE: Servlet.service() for servlet [jsp] threw exception

java.security.cert.CertPathValidatorException: Algorithm constraints check failed on signature algorithm: SHA1withRSA

at java.base/sun.security.provider.certpath.AlgorithmChecker.check(AlgorithmChecker.java:237)

at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkAlgorithmConstraints(SSLContextImpl.java:1564)

at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkAdditionalTrust(SSLContextImpl.java:1493)

at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1437)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:638)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369)

at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:396)

App team end user mentions SQL Server might be using a certificate signed with the SHA1withRSA algorithm and Red Hat 9 has restrictions on the SHA1 algorithm when using the DEFAULT cryptographic policy and that SHA1 is a weak algorithm.

And is asking our DBA team...if we can confirm SQL Server is using certificates based on the SHA1 algorithm and can we update the certificates with more secure algorithms?

From our DBA team perspective am wondering where to start...is it correct to say that Certificates mentioned here that SQL Server uses by default are on the Windows OS level and that TLS v1.2 (including lower versions v1.0 & v1.1 are enabled by default)?...or something else/concept to consider to solve their connection issue?

Thanks in advance.

2 answers

Your answer

Answer 1

Erland Sommarskog 119.9K MVP

SQL Server uses whatever certificate you tell it use. If you don't give it a certificate, it will create a self-signed certificate. I don't know on the top of my head which algorithm it is created by.

In any case, it is better to install a certificate, and then make sure that all clients trust this certificate. If you produce one locally (cheap), you will need to distribute it to the clients. If you buy one, the client only needs to have the issuer's root certificate.

I suggest that you read https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/certificate-overview?view=sql-server-2016 and the topics that follow to get a better understanding.

techresearch7777777 1,961 Reputation points

2024-12-03T02:10:43.2433333+00:00

Thanks Erland for your reply, really appreciate it.

Doing some research around I’ve came across few sites that sounds like SQL Server 2016 by default uses Sha-1 Certificate algorithm (or please correct or confirm me if true or not) one of being sounded from…

https://support.microsoft.com/en-us/topic/kb4053407-fix-sql-server-2017-cannot-decrypt-data-encrypted-by-earlier-versions-of-sql-server-by-using-the-same-symmetric-key-a33f8bc7-e01a-55c6-72db-b851334df3dd

“Cause

This issue occurs because SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure. ”

…so am thinking in combination with the following link also…

https://docs.vmware.com/en/VMware-Horizon/8-2206/rn/vmware-horizon-8-2206-release-notes/index.html

…which mentions within…

“Do not remove CA-signed certificates that were installed for production use, as recommended by VMware. CA-signed certificates will continue to work after you upgrade to this release.”

…is the concept the same in that I (DBA) can acquire another extra Certificate that has higher algorithm (higher than Sha-1) and install whereas end App users would still be able to connect from both Sha-1 and/or Sha-2 or higher without negatively affecting any other current established preconfigured App connections that exist?

Or something else?

Thanks.
Erland Sommarskog 119.9K Reputation points MVP

2024-12-03T21:32:23.6833333+00:00

This issue occurs because SQL Server 2017 uses the SHA2 hashing algorithm to hash the passphrase. SQL Server 2016 and earlier versions of SQL Server use the SHA1 algorithm that's no longer considered secure. ”

This is talking about encryption with symmetric keys, so it is not necessarily related to the certificate used for encryption of the network traffic.

“Do not remove CA-signed certificates that were installed for production use, as recommended by VMware. CA-signed certificates will continue to work after you upgrade to this release.”

Not sure what this has to do with SQL Server?

…is the concept the same in that I (DBA) can acquire another extra Certificate that has higher algorithm (higher than Sha-1) and install whereas end App users would still be able to connect from both Sha-1 and/or Sha-2 or higher without negatively affecting any other current established preconfigured App connections that exist?

My recommendation is definitely that you install a proper certificate, and make sure that it is trusted by the clients.

Here is an article on how to roll your own: https://codekabinett.com/rdumps.php?Lang=2&targetDoc=create-install-ssl-tls-certificate-sql-server

Answer 2

LiHongMSFT-4306 31,311

Hi @techresearch7777777

SQL Server 2016 (13.x) and earlier versions use the SHA1 algorithm. However, the SHA1 algorithm and many older algorithms are deprecated beginning with SQL Server 2016 (13.x). For more information, see Deprecated Database Engine features in SQL Server 2016 (13.x).

You may have the following options:

Create a new self-signed certificate or a third-party certificate that uses stronger encryption algorithms and configure SQL Server to use this new certificate.
Upgrade to SQL Server 2017 (14.x) or a later version that uses a stronger hash algorithm (SHA256) for self-signed certificates.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

techresearch7777777 1,961 Reputation points

2024-12-03T02:44:52.5033333+00:00

Thanks Cosmog for your reply also, much appreciated.

Brainstorming this through is that our SQL Server 2016 is housing numerous different DBs which different Application teams connect into their own specific DBs which have their own different connections methods/types/tools.

My initial above issue is coming from one particular Application team so if going with your first option:

"Create a new self-signed certificate or a third-party certificate that uses stronger encryption algorithms and configure SQL Server to use this new certificate."

I need to make sure that creating/adding extra new self-signed certificate won't affect any of all the other different Application team's connections (most likely will not use Force protocol encryption setting along those lines from my SQL Server end)...just so the App teams have the options to use either Sha1 or Sha2 (or higher) would be available (having both lower and higher versions separate Certificates on server existing side-by-side) rather than disabling/turning off Sha1 and/or Sha1 getting overridden which would affect existing Application connections that uses this lower version and would be safe?

Is my line of thinking/question here sound correct or I'm not understanding/off target?

Regards.

Share via

SSL error connection into SQL Server 2016 Standar on Windows Server 2016 Standard?

2 answers

Your answer