Connection Failures with Microsoft.Data.SqlClient 4 and later

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