Connection Failures with Microsoft.Data.SqlClient 4 and later
So I just ran into an annoying issue while upgrading to the Microsoft.Data.SqlClient version 4.0.0. I've been running version 3.0.1 and everything has been fine, but when I switched to 4.0.0 I started getting immediate connection failures.
Switch back to 3.0.1 everything's fine: Connections work as expected. Back to 4.0.0 and no go... immediate connection failures. What the heck happened in this upgrade?
After a bit of digging into my logs and actually stepping through the code, the full SqlClient Connection error I got is:
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 gives a clue that this has something to do with security and certificates. Which is odd, because I'm not specifying any security on the connection...
Encryption is now On by Default
It turns out that Microsoft has changed the connection defaults in Microsoft.Data.SqlClient in version 4.0.0 and it now sets the equivalent of Encrypt=true by default.
This means:
If your database is not using encryption, any connection will now fail by default.
Nice Microsoft!
The reasoning behind this is the age old 'secure by default' adage, and while I can see the point of that, I'd argue that a lot, if not most applications - including your typical local developer setup or even containerized applications - are not using encryption.
Luckily the fix is pretty simple - once you know what the problem is - as you can just specify Encrypt=False on the connection string like this:
server=.;database=LicenseManager;integrated security=True;Encrypt=False"
Et voila - now the connection works correctly again with 4.0.0.0.
Le Sigh
Seriously this is a head scratcher. I get the secure by default thinking, but setting up SQL Server for Encryption is not one of those features that you just enable flipping a configuration switch. You have to create and install a certificate and then propagate that certificate out to clients and configure SQL clients. In short, this is far from something that 'just works out of the box'. There's a bunch of set up that needs to happen for a server to run with encryption enabled and for a client to use the certificate the server is set up with.
So making this decidedly non-default behavior in the server, the default behavior in the client feels just very, very wrong. But what you're gonna do? 🤷
Summary
Bottom line is that this was not on my list of things I wanted to track down today. If you're like me when you run into this and see SQL connections fail, you're probably not thinking of your SQL Connection string that has worked for the last 10 years no longer working for you because you changed a .NET Framework library. 😏
It took me a while of figuring out that the problem was the Microsoft.Data.SqlClient 4.0.0.0 package I had updated a few days ago and then that the connection string was at fault. In fact, it was a Tweet that led me to the solution.
And hence this post: As I often do, I'm leaving this here as a note to self along with a blog title that's searchable, as I am almost certain to forget that Encrypt=True connection string flag in the future.
Hopefully this will help a few other souls to avoid the hour of back and forth I've wasted...
Resources
this post created and published with the
Markdown Monster Editor
Other Posts you might also like
- Adding minimal OWIN Identity Authentication to an Existing ASP.NET MVC Application
- Map Physical Paths with an HttpContext.MapPath() Extension Method in ASP.NET
- Getting the Client IP Address in ASP.NET Core
- Resolving Paths To Server Relative Paths in .NET Code
- Getting the ASP.NET Core Server Hosting Urls at Startup and in Requests
The Voices of Reason
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
this change is documented in Breaking Changes section in the release notes:
https://github.com/dotnet/SqlClient/blob/main/release-notes/4.0/4.0.0.md
you've always to expect breaking changes when switching major number of a package/library or product.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Of course, encryption is on by default for Azure SQL Server, and that's what Microsoft really want you to use these days...
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
@Mark - sure but then again, when do you ever not copy the crazy connection string from Azure directly out of the portal? It's not like they can't put the flag in there. Nobody in their right mind would type that connection string manually.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
I appreciate this change - secure defaults are the way to go!
There are so many issues and breaches out there just because insecure defaults!
I your case:
- Always read release notes for a major version (there are breaking changes all the time) 😉
- I'd recommend using
TrustServerCertificate=true;instead of disabling the encryption by default
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
In regards to Daniel's point about expecting breaking changes in major version updates.
The Connection strings in my Apps haven't changed in 20 years, this is the first occurrence of a "Breaking Change" going back to .Net Framework 1.0
It's kind of a big thing, thanks for pointing it out Rick!
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
I'm going to add "Encrypt=False" to all my connection strings in all my projects, since by the time I upgrade them I'll probably forget that I read this here and go nuts trying to figure it out 😯
Cheers
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Thanks Rick!
I just bumped into this issue. Knew I had seen this blogpost and just had to find it. Took me 2 minutes to 'fix' it.
Using this option also works for like, as another reader pointed out.
TrustServerCertificate=true;
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Thanks Rick! I spend a few days debugging this and I could not find any related resources on the web.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Thanks again Rick for your continued sharing of excellent tips and helpful advice. Luckily I found your post relatively quickly, so I only spent about a half hour on this.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Awesome, many thanks for this - just hit the same issue migrating from Core 3.1 to .Net 6 where upgraded the Microsoft.Data.SqlClient package at the same time!
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Just bumped into this problem after migrating from System.Data.SqlClient to Microsoft.Data.SqlClient. Thanks Rick!
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
that made all my dependent tests suddenly to fail, when everything was building... doh... thanks for the post, a lifesaver 😉
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
I hadn't come across your posts in a while, but this issue totally hit me today and my first reaction was "OH THAT GUY!" - thanks as always.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Hey Rick! Been wondering for past 30 minutes what might be wrong with my connection. I even tested my previous projects and connection worked all fine. Despite setting up everything as per my previous projects the connection open method always failed until I found your solution.
Thanks for this!
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Just got hit by this one while migrating a project from System.Data.SqlClient to Microsoft.Data.SqlClient. I'm fine with the change to the default but that error message could be a lot better. Instead of throwing a certificate exception, SqlClient should be aware that since the server returned to certificate and the connection string expects encryption, that the mismatch is the issue, not the (non-existent) remote certificate.
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
Year 2023: I upgraded a project from ".NET Core 3.1" to ".NET 6". Some nuget packages had to be updated too; one of them was "Microsoft.Data.SqlClient". I got the above error, another gray hair, and gladly found your solution.
Year 2024: I am upgrading a project from ".NET Core 3.1" to ".NET 8". Some nuget packages had to be updated too; one of them was "Microsoft.Data.SqlClient". I get the above error, another gray hair, and gladly found your solution, again.
Thank you for your blog post! And see you next year in 2025. xD
# re: Connection Failures with Microsoft.Data.SqlClient 4 and later
O M G - thanks for the heads up! imho that is going to cause no end of tickets for them