2

I upgraded a library from .NET 4.7 to .NET 8.0.

In doing so, I had to update my System.Data.SqlClient references to Microsoft.Data.SqlClient.

I have both projects open, in different VS sessions, side by side.

Both are passing the exact same connection string to the Open method on their respective versions of SqlConnection:

Data Source=MyDbServer;Initial Catalog=MyDatabase;Application Name=MyApplication;Pooling='true';Connection Lifetime=500;Integrated Security=SSPI;Persist Security Info=True;

The System.Data.SqlClient version of SqlConnection opens without trouble.

The Microsoft.Data.SqlClient version of SqlConnection raises this exception on Open():

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.)'

Two questions:

  1. Why does the System.* version succeed, when the Microsoft.* version doesn't?

  2. What do I need to change about my connection string to make this work? (i.e. - I don't want to change anything about the db server or db itself to get this to work... I only want to change the connection string)

Thanks

Improve this question
1
  • ok.. so regarding Q2, I was able to fix it by adding "TrustServerCertificate=True;", but for my own edification, would still like to know why this was necessary for the .Net Core object, but not the .Net Framework one. Commented Apr 9 at 13:37

3 Answers 3

Reset to default
3

For Question 1,

Microsoft.Data.SqlClient enforces stricter security defaults compared to System.Data.SqlClient.

System.Data.SqlClient silently skips some SSL/TLS validation scenarios while Microsoft.Data.SqlClient requires trusted certificates by default, and if your SQL Server is using a self-signed or internal certificate that isn’t trusted by your client machine, it throws exactly this error.

For Question 2, Add TrustServerCertificate=true; in your connection string.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

There are several great answers. This one came in first and answers both questions, so marking it as "the" accepted answer. For anyone else that comes in with the same question as me, consider taking a look at the answers by Lajos & Nick for additional context.
3

There is quite a nice cheat sheet on migrating from System.Data.SqlClient to Microsoft.Data.SqlClient
here that hopefully explains the change :
https://github.com/dotnet/SqlClient/blob/main/porting-cheat-sheet.md#functionality-changes

Relevant part is below:
enter image description here

Improve this answer

1 Comment

Thanks for the answer and cheat sheet link. Def worth bookmarking!
2

The earlier version was more lenient on security, the latter version is stricter. So the answer can be found in the documentation of the latter. In the TDS 8 enhanced security section we can see this:

To use TDS 8, specify Encrypt=Strict in the connection string. Strict mode disables TrustServerCertificate (always treated as False in Strict mode). HostNameInCertificate has been added to help some Strict mode scenarios. TDS 8 begins and continues all server communication inside a secure, encrypted TLS connection.

New Encrypt values have been added to clarify connection encryption behavior. Encrypt=Mandatory is equivalent to Encrypt=True and encrypts connections during the TDS connection negotiation. Encrypt=Optional is equivalent to Encrypt=False and only encrypts the connection if the server tells the client that encryption is required during the TDS connection negotiation.

For more information on encrypting connections to the server, see Encryption and certificate validation.

HostNameInCertificate can be specified in the connection string when using aliases to connect with encryption to a server that has a server certificate with a different name or alternate subject name than the name used by the client to identify the server (DNS aliases, for example). Example usage: HostNameInCertificate=MyDnsAliasName

The Encrypt default value set to true section explains that Encrypt is true by default:

The default value of the Encrypt connection setting has been changed from false to true. With the growing use of cloud databases and the need to ensure those connections are secure, it's time for this backwards-compatibility-breaking change.

In short:

  • with the growth of cloud servers and increasing security concerns, encryption became true by default
  • if encryption is true, then your old connection string will not work in lack of an encryption
  • TrustServerCertificate disables strict more and trusts server certificate, so bypasses the security measure
  • hence, when you have set it to false, it started to be working
  • but the security issue is not handled. In order to handle the security issue you will need to properly encrypt, which is luckily well-documented
Improve this answer

2 Comments

Thanks for the great and through explanation.
@Mike happy to help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.