Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

Connection Failures with Microsoft.Data.SqlClient 4 and later


:P
On this page:
Edit this Post

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
Posted in .NET  Sql Server  

The Voices of Reason


 

John
December 07, 2021

# 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


daniel
December 08, 2021

# 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.


Mark
December 08, 2021

# 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...


Rick Strahl
December 08, 2021

# 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.


kapsiR
December 09, 2021

# 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:

  1. Always read release notes for a major version (there are breaking changes all the time) 😉
  2. I'd recommend using TrustServerCertificate=true; instead of disabling the encryption by default

David W Elyk
December 09, 2021

# 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!


Adin
December 12, 2021

# 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


David De Sloovere
December 16, 2021

# 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;


Iuliu Sandu
January 05, 2022

# 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.


Alan Farkas
January 06, 2022

# 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.


Ritchie Whytock
January 12, 2022

# 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!


Alex
January 25, 2022

# 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!


Jose
March 03, 2022

# 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 😉


Todd Davis
April 01, 2022

# 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.


Pawel
April 20, 2022

# 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!


Felipe
November 01, 2022

# 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.


Manuel Frog
April 25, 2024

# 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


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024