Unable to connect to SQL Server with encryption enabled with 4.0 #1402
Comments
|
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. |
|
Yes, I noticed that |
|
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. |
|
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. |
I think the idea here is to encourage users to actually enable encryption at the server, rather than disable encryption on the client. |
|
@alansingfield we do not recommend setting that value to false on production applications. Our recommended solution is to use encryption. For testing and non production applications you can set the value to false and also can leave that value set to true and add another connection string property TrustServerCertificate and set it to true. In this case at least you can keep your connection encrypted. |
|
Does the SqlClient support a CustomCertifacteValidator like HttpClient or GrpcClient ? |
|
@JRahnama Thx for answer. I could only find this static validator : It does even try to allow hostnames with different lengths The problem is that it is all internal and static and nothing to cache the validator external without recompiling the project |
|
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 had the same issue with the same error and to add the |
|
I get that, but that setting essentially says trust whatever certificate is being presented by the server, which isn't very secure imho. |
|
@jmezach From your You need to copy your ca-crt into a different directory (build time, do not mount) first and then run Option A: Option B: |
|
@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. |
|
I'm facing the same situation. |
|
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 |
|
@CoolDadTx , we hear your comment and appreciate that you brought it up here. Your comment might be true in your specific use case, but we have so many other users to use the driver in different use cases. There are two issues being addressed here: Default Encrypt to True. This is for security. Similar to http/https, if the client starts with allowing non-encrypted connections, it will always be susceptible to MITM attacks. Even if the server is configured to require encryption, there can be a MITM altering the server's response to say it doesn't require encryption. The MITM can then proxy the connection. client <-plain text-> MITM <-encrypted-> server = the connection is compromised. Security has been encouraging us for years to change the default behavior of client drivers to be secure by default and we have resisted, knowing that it is a breaking change for most users. It's easy enough for devs to add Encrypt = false to all their connection strings, if they need to. We just want to make sure they understand the choice they are making and they are making it deliberately. With cloud computing becoming more and more common, it's not safe to leave the default value of Encrypt equal to false. The less-breaking, but still important, fix here is to ensure connections fail if the client does not have any encryption libraries available and either Encrypt = true or the server requires encryption. SqlClient + native SNI is the only MS driver we've found that will successfully connect in that scenario. plus, |
Unless, like me, you manage over 80 different applications across multiple servers. In which case it'll take a reasonable amount of time to update them assuming the developer updating the library even knows to do so. |
|
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. |
The difference is. With https you can install a self signed cert and the connection will be established, with SqlClient 4.0 that doesnt work (?). With https there even isnt need for TrustServerCertificate or Encrypt params in header. |
|
@jmezach we are trying to repro the issue. Can you walk us through the issue? On a container (Linux) when on MDS v4 and net5 or 6 and you can see the issue. Is that correct? ( MDS 4.0 and net 5 is more of our interest to see if that is related to net 6 or driver) |
|
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: using Microsoft.Data.SqlClient;
Console.WriteLine("Connecting...");
string connectionString = "Data Source=ZEUSSQLAPOLLO1.zeus.corp;Initial Catalog=ConfigurationDatabase;Encrypt=true;User Id=someusername;Password=somepassword";
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Console.WriteLine("Connected!");Running this on my MacBook Pro with a 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 FROM mcr.microsoft.com/dotnet/sdk:6.0-alpine
WORKDIR /app
COPY Program.cs /app
COPY SqlTls.csproj /app
COPY root.pem /etc/ssl/certs
RUN dotnet build
ENTRYPOINT [ "dotnet", "run" ]And then run that with the package reference at Again, updating the package reference to Doing a |
|
@jmezach thanks for prompt and detailed response. I will test it shortly and get back to you soon. |
|
@jmezach pls try and replace in your docker file: with following You wrote that you tested it, but I still believe that it can fix the issue Your root certificate should then be in the output |
|
@Zetanova Just tried that, but I'm still receiving the same error message. And again it works fine with 3.0.1. |
|
Any updates on this issue? We've added |
|
We have the similar issue, EWT says But servername is matched with certificate and root cert placed into right place. |
I agree I do not like this breaking change. Can someone explain what Encrypt = True actually does? Does this mean I have to encrypt my connectionstrings when I store in System Environment Variables? If yes, how do I encrypt them, what password code or algorithm. I don't get what this solves? The people I see that set Encrypt=true, I can still read the connection string in plain text. Thank you, Microsoft, you just broke another opensource project. I have spent the last 7 weeks upgrading from 5.0 to 6.0, and open source doesn't pay very well. |
|
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 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. 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 |
|
@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). |
|
@JRahnama we had the identical experience as @jmezach. We connect to AWS RDS and MDS v4 works fine after you install the certificates for the region your DB is in but only in Windows. When running in Docker Linux we get the same exception. I tried to use v5 preview3 which also works in Windows but in Docker you get the following exception: |
|
@MonocleKelso try preview 2 instead. |
|
@ErikEJ preview 2 works. Thanks! |
|
I would like to ask all users who encountered this issue to test with the latest release of M.D.SqlClient to see if the problem is solved. |
|
We are using version 5.0.1 now which seems to work just fine on our development and test environments, but doesn't yet work in production. For production we are connecting to a SQL Server cluster through a listener. The listener has a certificate which has the common name of one of the servers of the cluster, but with subject alternative names of the listener. So for example: Our connection string then uses: But with that setup we are still getting the same error. |
|
Have you tried: |
|
We did, unfortunately there is a firewall blocking that traffic currently so we'll have to involve another team to open that up. We are expecting that to work though, as that is exactly the same as all other environments. However, we were expecting the subject alternative names to work as well? |
|
If this is going to be the new default, could we make it easier to find documentation on how to configure it? I just migrated to EF 7, and so far the only way I can successfully connect to a db is by setting Encrypt=False. But without it set to false, I just get exceptions trying to connect. I'm assuming that I need to install a cert on my SQL server, and then also somewhere in my client app, but not finding any docs online on how to proceed. (note: this is just running on my development machine -- win 10/.Net 7/SQL server Developer Edition |
|
I'm having this issue when upgrading a .Net 6 AspNetCore application to .Net 7. The application works fine with
This fails when upgrading everything to use |
I believe when you upgraded from EF Core 6 to EF Core 7, there was a breaking change (from Microsoft.Data.SqlClient |
|
We are having the same issue when upgrading the app from .net3.1 to .net6 Encrypt=False solved the issue.. i assume something is not correctly setup for encryption I am sure that the servers communicate through ssl as we use integrated security and we solved the ca issue with .net3 For the record we used mcr.microsoft.com/dotnet/core/sdk:3.1-buster and nowe we use mcr.microsoft.com/dotnet/aspnet:6.0-focal as base images to deploy apps |
|
@scholtz Encrypt was always false before, did you have it set to true previously? |
|
No, we had it without the value inserted.. With integrated security I assume it would not work as well without setting up the CA correctly. We also use connection between services encrypted with https, so i am sure that CA certificate within docker container works correctly. Perhaps the CA does not have correct CRL or something, and .net6 has more strict verification.. However the connection between services still works, so it should be something mssql related. |
|
@scholtz the default value was false before, now the default value is true. |
|
.NET 5+ now respects Open SSL ciphers, could you try recommendations from here? Default Cipher suites for TLS on linux For additional reference: SqlClient Troubleshooting Guide If you didn't set Encrypt=True before SqlClient 4.0, your app was not encrypting server connection, and now encryption is set to True by default. Recommended action is to set "Trust Server Certificate = True" instead of setting "Encrypt = False" until proper server certificates are installed on Client. And making sure TLS 1.2 is supported by server/client for secure communication. If your server doesn't support TLS 1.2, we recommend upgrading or alternatively you may configure OpenSSL on Linux platforms as needed. You may need to enable Cipher suites for old SQL servers. |
|
In the appsettings,json file I put : }, after placing the the sentence "TrustServerCertificate=true" the connection was made. |
Added NEtDateTimeKind to address TZ/noTZ ambiguity Address https://www.npgsql.org/doc/release-notes/6.0.html#major-changes-to-timestamp-mapping incopatibility Address dotnet/SqlClient#1402 new SQL Server encryption defaults
|
Hello, how can I disable this thing if I don't have access to connection string. It is generated hard coded in the program and I'm stuck now. |
|
@jmezach Could you test this with the latest preview? |
|
Closing as by design. |
and others
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-alpineimage. Connecting to the SQL Server gives us the following exception: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=Falsein 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 runningupdate-ca-certificates) we are still seeing the same issue.The text was updated successfully, but these errors were encountered: