SqlClient: Unable to connect to SQL Server with encryption enabled with 4.0
Describe the bug
We’ve recently upgraded one of our applications from .NET 5 to 6 and also updated Microsoft.Data.SqlClient from 3.0.1 to 4.0.0. After this upgrade we are unable to connect to our SQL Server running on Windows from a Linux container running on Kubernetes based on the dotnet/aspnet:6.0-alpine image. Connecting to the SQL Server gives us the following exception:
Microsoft.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 35 - An internal exception was caught)
---> System.Security.Authentication.AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
at System.Net.Security.SslStream.SendAuthResetSignal(ProtocolToken message, ExceptionDispatchInfo exception)
at System.Net.Security.SslStream.CompleteHandshake(SslAuthenticationOptions sslAuthenticationOptions)
at System.Net.Security.SslStream.ForceAuthenticationAsync[TIOAdapter](TIOAdapter adapter, Boolean receiveFirst, Byte[] reAuthenticationData, Boolean isApm)
at System.Net.Security.SslStream.AuthenticateAsClient(SslClientAuthenticationOptions sslClientAuthenticationOptions)
at System.Net.Security.SslStream.AuthenticateAsClient(String targetHost, X509CertificateCollection clientCertificates, SslProtocols enabledSslProtocols, Boolean checkCertificateRevocation)
at Microsoft.Data.SqlClient.SNI.SNITCPHandle.EnableSsl(UInt32 options)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.InternalOpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at HealthChecks.SqlServer.SqlServerHealthCheck.CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken)
ClientConnectionId:32fb614f-d95f-4cb9-9c4b-d54ce172d57d
Error Number:-2146893019,State:0,Class:20
Going back to .NET 5 and Microsoft.Data.SqlClient 3.0.1 fixes the issue. Even after disabling encryption on the server we still receive the same error. Only after explicitly setting Encrypt=False in the connection string the error goes away.
To reproduce
The issue shows up as soon as we try to make a connection to the SQL Server from the container, so it should be fairly simple to reproduce.
Expected behavior
I would expect the connection to be established since the root CA’s certificate has been added to the trusted root within the container.
Further technical details
Microsoft.Data.SqlClient version: 4.0 .NET target: .NET 6 SQL Server version: SQL Server 2019 Operating system: Docker container for the app, Windows for the SQL Server
Additional context
The SQL Server is using a certificate that is signed by an Active Directory certificate authority. At first we thought the problem lied in the way we injected the public key certificate of that CA into the container (by mounting it into /etc/ssl/certs), but even after building a new image with the CA certificate build into it (and running update-ca-certificates) we are still seeing the same issue.
About this issue
- Original URL
- State: open
- Created 3 years ago
- Reactions: 14
- Comments: 67 (18 by maintainers)
It may be a reasonable idea to encourage the use of this setting, but the “discovery pathway” is terrible! The error message should state upfront that Encrypt=False will fix the problem.
I had the same issue with the same error and to add the
TrustServerCertificate=true;into the connection string did resolve it.I feel like this issue is going a bit of topic. What we’re seeing is a regression issue between 3.0.1 and 4.0 where the older version is capable of establishing a connection to our SQL Server with an encrypted connection, while the newer version does not.
I still feel like we are not addressing the actual issue here that version 4.0 is unable to connect, while 3.0.1 connects just fine in an otherwise same setup. Unfortunately downgrading introduces its own set of problems for us as well which makes this particularly problematic for us.
Downgrading Microsoft.Data.SqlClient to 3.0.1 but staying on .NET 6 solves the issue on my Mac, so it looks like there’s definitely something going on with 4.0.
@DataJuggler, what the
Encryptparameter does is it tells SQL that communication to and from the database will be encrypted. It is basically SSL for the SQL calls. Without this parameter your queries are sent to the database server unencrypted and any data coming back is as well. Hence a network sniffer could see the data coming across the wire. With this parameter set to true then the data is encrypted.But for this to work the client needs to validate the certificate being used. Unfortunately SQL Server auto-generates a local one when it installs and that is the one it’ll use. Such a certificate cannot be verified as valid so setting
Encryptwill then change the error to indicate the certificate is not trusted. Thus you also have to follow the KB article on how to get and install a valid certificate on the server AND configure SQL Server to use the certificate AND you must ensure you keep it updated like you do any other SSL cert. My understanding is that SSL certs are now only good for 1 year so this becomes yet another maintenance issue you have to follow.As a workaround to the “not trusted” error you can also add
TrustServerCertificate=True. This tells the client to encrypt the data but use any cert provided by the SQL Server. Technically this is just replacing one vulnerability for another as a MITM attack could intercept calls to your SQL database and drop in their own cert which then allows them to decrypt the data. But if you have this risk on your network then you have bigger problems. Of course cloud providers are a different story entirely.@premradhakrishnan thanks for the summary, indeed most people searching for this error message may have an issue with their certificates and have to fix this as you described.
But let me add that this exact issue is caused by a code change in version 4.0 of Microsoft.Data.SqlClient, which to my understanding lets the authentication of the connection to SQL Server fail, if the CRL (certificate revocation list) can’t be provided by the environment at a certain point during authenticaion. It seems like PR #1559 will fix this issue with the upcoming 5.0 version (please correct me where I’m wrong, I’m not involved in SqlClient, just observing this issue).
From what I’ve seen so far I don’t think .NET 5 or 6 has any bearing on the issue, it is just the version MDS. I’ve written a small .NET 6 console app with the following code:
Running this on my MacBook Pro with a
<PackageReference>forMicrosoft.Data.SqlClientversion3.0.1I get the following output:When I change the version number of the package reference to 4.0.0 and run it again I get the following output:
If I build a container using the following Dockerfile and build it using
docker build -t sqltls:local .:And then run that with the package reference at
3.0.1I get the following output:Again, updating the package reference to
4.0.0I get the following output:Doing a
dotnet publish -r win-x64and copying the resulting output to a Windows machine and running it there works just fine, regardless of whether we’re using3.0.1or4.0.0.I’m facing the same situation. Only happens when running on Linux. Adding
TrustServerCertificate=trueor reverting to 3.0.1 seems to fix it, although addingTrustServerCertificateis not acceptable in my use case.I just tried doing the same on my MacBook and I get the exact same error message, but it works just fine on Windows. So it seems that the behaviour is different on Windows than it is on Mac and Linux.
In the appsettings,json file I put :
}, “AllowedHosts”: “*”, “ConnectionStrings”: { “MVCDemoConnectionsString”: “server=LAPTOP-VLGA59J2\SQLEXPRESS;database=PacientesDb;Trusted_connection=true; TrustServerCertificate=true” }
after placing the the sentence “TrustServerCertificate=true” the connection was made.
@scholtz the default value was false before, now the default value is true.
I believe when you upgraded from EF Core 6 to EF Core 7, there was a breaking change (from Microsoft.Data.SqlClient
2.1.4to5.0.1) mentioned in the release notes re: the default value of Encrypt where it use to be set tofalse, but was changed totrue. You can find the details in another closed issue found here .@Toqe PR #1559 will address the CRL issue. When the preview package is released we recommend users to test with the package to see if the issue is solved.
Just came across this issue and commenting as its not closed yet.
If the default encrypt setting becomes true, then theoretically we have 2 options. Option 1 (recommended) : Assign a valid certificate to the SQL Server Instance, a certificate that will be trusted by all clients. Option 2 : Add trustservercertificate=true in the connection string
Option 2 is not recommended as it is not secure because it leaves the connection vulnerable to MITM attacks.
Therefore, the only viable and recommended option is to have a valid and trusted certificate assigned to the SQL Server instance. The errors listed e.g.
The remote certificate was rejected by the provided RemoteCertificateValidationCallback.andThe remote certificate is invalid according to the validation procedureare due to the server certificate not being trusted by the client machine. The only way to fix these errors is to update the certificate to have all the required parameters and then reassign it. (Or follow Option 2 and addtrustservercertificate=truein the connection string which will then ignore the validation checks on the certificate and trust whatever certificate is presented. But then this is obviously insecure and not recommended as mentioned earlier.)This article lists all the parameters you need for the certificate.
The options available for a trusted certificate are many depending on your environment. Based on the server name you use to connect to the SQL Server, you could get a DNS certificate from GoDaddy or Digicert and use this. If your server and client are hosted within a trusted AD environment, your IS team might already have trusted root certficates enabled and then you can just assign this to SQL Server. In dev you can always create a self-signed certificate and import this into your trusted store.
Hope the above helps at least a few people if not all.
Thanks
Hi @DavoudEshtehari, didn’t have quite much time to dig deep, but seems that your version is working in our case. Looking forward to some rc to test it properly!
@DavoudEshtehari I gave it a short spin and it looks good for my use case. Everything works as expected.
I think the original comment was that a developer, in their local development environment, where they do not have a valid certificate, they can use Encrypt=false to bypass security checks.
On 80 [production] applications deployed across multiple servers, you should have valid certificates, and should have encryption enabled.
If your current applications do not have encryption, i.e. everything is sent in plain text, then that is a security risk that you should probably be considering looking at fixing.
In that case, either don’t upgrade to the new libraries (that have default = true) and stay on your old, insecure apps; or when upgrading explicitly change to encrypt = false, keeping them insecure but with the new library.
Hi Everyone, any update on this issue?
Tried to update Microsoft.Data.SqlClient from 3.0.0 to 4.0.1 and our K8s deployment fails with exactly stated error (
The remote certificate was rejected by the provided RemoteCertificateValidationCallback). However, even both related parameters to connection string doesn’t help us:Server=tcp:***,1433;Initial Catalog=***;User ID=***;Password=***;Encrypt=False;TrustServerCertificate=True;Connection Timeout=30;.This package is a dependency for Quartz by the way, for now it is okay to use 3.0.1 version of Microsoft.Data.SqlClient, however in some future it could become a blocking dependency for using some of their package versions I assume.
@Zetanova Just tried that, but I’m still receiving the same error message. And again it works fine with 3.0.1.
@jmezach thanks for prompt and detailed response. I will test it shortly and get back to you soon.
I’m facing this issue as well but my code works just fine in a regular .NET 5 console app. However a unit test (running against .NET 5) with the exact same block of code fails with this error.
I’m sorry but turning on a feature that could break existing, working code is just bad form, irrelevant of opinions on best practice. Why didn’t the team provide an opt-in feature until this breaking change was better announced? This entire library has been replete with issues from the getgo (locked files, extra dependencies, etc) and I really struggle trying to justify why we should be using it over the already working, no issues
System.Data.SqlClient. Changes like this really damage the trust we should have in MS libraries.@Zetanova We tried option A, but unfortunately we still ran into the same issue.
And again, we’ve downgraded our application to use version 3.0.1 of Microsoft.Data.SqlClient and we have confirmed that all connections coming from our container(s) are using an encrypted connection using the following query:
Additionally HTTPS connections are working correctly from the container to other machines in our network that have certificates signed with the same CA root.
Thus so far the only combination that doesn’t seem to work is one where we’re using version 4.0 of Microsoft.Data.SqlClient.
Does the SqlClient support a CustomCertifacteValidator like HttpClient or GrpcClient ? The hostname of a mssql server can vary especial for internal and external access. A validation of the certificate thump like in ssh would be
nicesomething likeTrustServerCertificateThump=b7d6c2997179bfbe172ea76ef51f05b7cbdeacd4;would be optimal@jmezach the default value for connection string property Encrypt has set to true, which previously was false. You can read more here.