Itermittent "The certificate received from the remote server was issued by an untrusted certificate authority"

[buckleyGI]

technical details

Microsoft.Data.SqlClient version: 5.1.0
.NET target: Framework 4.8.1
SQL Server version: SQL Server 2019

We are seeing seeing the error below as of 12/03.

The problem is intermittent:

• it starts as soon as the server boots up
• it is resolved by rebooting as well and 9/10 the problem is gone

We are aware that the encryption is nowadays the default and we can "mitigate" the problem by adding TrustServerCertificate=True to the connection string.
We don't want to do this obviously as the server's identity isn't verified and we could as well be talking securely with a man in the middle.

The intermittency of the problem sets it apart from all the other github issues that have the same error signature.
Sometimes the certificate is trusted, sometimes not.

We think the client comes up and is initialized in a bad state and not trusting the sql server self singed certificate that is signed by a authority that part of the Trusted Root Certification Authorities on the client.
In fact, when we open the .crt file that corresponds with the sql certificatie on the client while it is in the bad state, and thus show the below error, the .crt is said to be ok/trusted (?).

We have also tried restarting a number of windows services that could be related to certificate checking thinking that restarting them would get the client out of the bad state.

The client VM's are located in Azure.
We decoded the certificate that is attached binary in the event logged by System as it says in "The attached data contains the server certificate". This certificate is the one we expect: valid and if opened it is said to be ok as well.

How would we go about further troubleshooting this issue?

Thank you

Log Name: System
Source: Schannel
Date: 3/17/2023 1:53:42 PM
Event ID: 36882
Task Category: None
Level: Error
Keywords:
User: redacted
Computer: redacted
Description:
The certificate received from the remote server was issued by an untrusted certificate authority. Because of this, none of the data contained in the certificate can be validated. The TLS connection request has failed. The attached data contains the server certificate.

void Main()
{
    string str = "Data Source=x;Encrypt=true";
    string qs = "SELECT 1";
    CreateCommand(qs, str);
}
private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

[lcheunglci]

Hi @buckleyGI, for bringing up this issue to our attention. From what I understand with information provided, the certificate is not self-signed and works 9/10 times; however, when it fails, the exception message indicates that the certificate is issued by an untrusted CA. I don't think we've encounter this before, but from my understanding the FQDN name in the subject CN must match the server's name specified in the connection string. If not, you may need to specify the HostNameInCertificate in the connection string. Could you also provide the connection string that you're using?

[buckleyGI]

"the certificate is not self-signed"

We have generated the certificate our self and signed it with our company CA (so self signed=?self-generated) which is part of the Trusted Root Certification Authorities on the client. This should satisfy the the condition AFAIK to have encryption with the identity of the server being verified.
We don't use a global (commercial) CA to sign it but this is not a requirement right.

These are the connection string that we used and want to use and fail

connection stdring "Data Source=inception.cpg.db;user=x;password=y";
connection stdring "Data Source=inception.cpg.db;user=x;password=y;Encrypt=true";
connection stdring "Data Source=inception.cpg.db;Integrated Security=true;";

Of course this works but is unwanted as explained above
connection stdring "Data Source=inception.cpg.db;user=x;password=y;TrustServerCertificate=True";

I have already read about the requirement that the CN or SAN should match the host that is knocking on the door and AFAIK the host is part of the SAN (not the CN).
W have a long list with wildcards in the SAN to cover all the possible hosts that come knocking.
So the HostNameInCertificate should not be required but it is something that we will investigate.

[buckleyGI]

Thank you for the suggestion about using HostNameInCertificate in the connection string.

Data Source=project.abc.db;user=x;password=y;HostNameInCertificate=project.abc.db;

What are we doing extra here? Isn't the Data Source presented when it checks the certificate's SAN and CN?
project.abc.db is part of the SAN albeit with a wildcard *.abc.db

This seems an improvement to TrustServerCertificate=True => Man in the middle attack possible
With HostNameInCertificate we enforce the server to be trusted. => Man in the middle attack NOT possible

We still don't fully understand the issue. Do we have to simply repeat the Data Source in the HostNameInCertificate?

[JRahnama]

@buckleyGI If the HNIC is not provided the validation goes with FQDN in Data Source and CN of the certificate. Otherwise, the Data Source could be the IP address or FQDN of the machine and the driver validates the provided HNIC with other names provided in the certificate's extensions for DNS. It is really a certificate setup fact and driver only checks the value.
for example if you create a self signed certificate it could be like this:

New-SelfSignedCertificate -Subject "CN=<FQDN>" -KeyAlgorithm RSA -KeyLength 2048 -CertStoreLocation "cert:\LocalMachine\My" -TextExtension @("2.5.29.17={text}DNS=localhost&IPAddress=127.0.0.1&IPAddress=::1") -HashAlgorithm “SHA256” -Type SSLServerAuthentication -Provider “Microsoft RSA SChannel Cryptographic Provider”

The above command creates a certificate that could be used for your machine with FQDN, but also accepts localhost or IP address.

[buckleyGI]

Hi @JRahnama Thanks for the response
We have a valid certificate AFAIK and it works 9 out of 10 times. That flakiness is our problem.
The problem starts when the clients are rebooted every night and come up again. 1 out of 10 has the problem.
When we reboot that faulty client the problem is 9 out of 10 gone.

The Data Source in the connection string is part of the SAN, SAN, or Subject Alternative Name, an extension that is part of the X. 509 TLS certificate specification.
That is the what you pass with the TextExtension in your command I believe.
True, the CN, part of the Subject, is different but this should not be a problem, as it is 9 out of 10 times.

And as stated, this connection string will always work:
Data Source=project.abc.db;user=x;password=y;HostNameInCertificate=project.abc.db;

We should have not to repeat the Data Source in the HostNameInCertificate right?
Or we should repeat which could mean that

• There is a bug in the driver when it resolves the Data Source
• There is non deterministic behavior outside the driver when the request travels the wire

Do these last 2 options makes sense? Is there any extra logging we could enable at our side to find out when and why it goes wrong 1 out of 10 times?

[buckleyGI]

We have this issue about once a day and have at the moment added TrustServerCertificate=true to mitigate.

Today we also had the issue and we did a test where we set the Data Source to the IP address instead of the hostname:

Data Source=10.100.10.100;Integrated Security = True;Encrypt=true

Unhandled Exception: Microsoft.Data.SqlClient.SqlException:
A connection was successfully established with the server, but then an error occurred during the login process.
(provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

This works:
Data Source=10.100.10.100;Integrated Security = True;Encrypt=true; HostNameInCertificate=10.100.10.100

Note that we just repeat the Data Source in the HostNameInCertificate
Rebooting the server fixes the problem.

This test shows that the problem is even present when we take the name resolution out of the loop.

We are currently clueless on how to proceed. Any help would be greatly appreciated.

This is our SAN list of the certificate and the ip is part of it.

"DNS=x.domain1.net&"
"DNS=x.domain2.net&"
"IPAddress=10.100.10.10"
"IPAddress=10.100.10.100&"
"DNS=*.domain4.net&"

All the other clients work without issue during this time. It was only this one client that was bad and needed a reboot.

Thank you.

[buckleyGI]

Hi @JRahnama Can you provide a possible next step in troubleshooting this? Should we start considering using something like Wireshark? Thank you

[JRahnama]

@buckleyGI we need to do some more investigation. Seems like native SNI has some problem with caching the SChannel, but not confirmed yet. We are working on this request to come up with an answer and check if this is by design or a bug, but it will take some time.

[buckleyGI]

@JRahnama Do let us know if we can help out by enabling some instrumentation/logging or even to validate a new build.

We can reproduce it after a few restarts of the client. Thanks for keeping us in the loop.

[buckleyGI]

@JRahnama We are instructing our engineers for now to add TrustServerCertificate=True to their connection strings if they encounter the problem.
(We know that we allow MITM attacks with this so have to revert all these changes once we have this fixed at the root cause.)
The issue is observed randomly each day on 1 to 2 servers.

Is there anything we can do to help diagnosing this issue or have a better mitigation than adjusting all our connection strings (there are quiet a lot)?
Is there something we can do server side? This would help us a lot at this point so we have control on the issue on 1 place.

Please keep us in the loop as we have it only for the moment in our dev and staging environment but want to be prepared if it leaks to production. Thank you 👍

[JRahnama]

@buckleyGI would you be able to test with a test nugets (unsigned) if I provide it to you? It cannot go to production and it needs to be tested in debug environment. Nugets will be for SNI and M.D.SqlClient

[buckleyGI]

@JRahnama We would be happy to validate the 2 nugets. Can you send them here via GitHub?

This is the plan I have in mind to validate

• prepare 2 small console application. One with the old nugets, one with the new nugets
• reboot servers until we have the TLS issue
• run the 2 console applications. If the issue if fixed in the console app with the new nugets we can conclude it is fixed.

Is it as "simple" as that and can we test another scenario?
This only works under the premise that we can embed the new SNI as part of the console app or is there a system wide change necessary? I would try to avoid the latter.