search

dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
842 stars 280 forks source link

Different behaviors between System.Data.SqlClient and Microsoft.Data.SqlClient #2368

Closed qizu0302 closed 7 months ago

qizu0302 commented 7 months ago

I force SQL Server to encrypt connection, I use SSMS 18 and SSMS 19 to connect to database, both are configured as below, that is "Encrypt=False;TrustServerCertificate=False" image

I don't import SQL Server self-signed certificate into trust CA store, the result is SSMS 18 can connect to database while SSMS 19 fails to connect to database.

I know SSMS 18 uses the legacy library, and SSMS 19 uses the new one, I wonder is this discrepancy by design?

David-Engel commented 7 months ago

I know SSMS 18 uses the legacy library, and SSMS 19 uses the new one, I wonder is this discrepancy by design?

Yes.

In System.Data.SqlClient, Encrypt defaults to false. When Encrypt = false in SDS, the server certificate is never validated, regardless of what TrustServerCertificate is set to.

Security has been tightening in Microsoft.Data.SqlClient. Starting with MDS 2.0 (SSMS 19 uses MDS 3.1), the server certificate will be validated based on the value of TrustServerCertificate if the client (Encrypt = true) or the server (Force Encryption on the server config) requires encryption. Most likely, your server is configured with Force encryption. Since your self-signed certificate is not part of a trusted chain on your client, the certificate validation fails. (As a furthering of the security tightening, starting with MDS 4.0, Encrypt defaults to true. So, in the next major version of SSMS, you'll probably see that Encrypt connection checkbox start defaulting to checked.)

kf-gonzalez commented 7 months ago

Closing issue as this behavior is by design