This is more of a “Today-I-Learned” post and not a “full-blown How-To article.” If something is completely wrong, please let me know - thanks!

In our product, we store all data in an MS SQL database. One of our clients had issues with the SQL connection, and I want to share what I learned about SQL Encryption and how (some) properties of the Connection String affect the behavior.

Basic SQL Connection String

In general, we have a pretty easy setup:

Our application reads a typical connection string that looks like this Data Source=your-sql-server.yourcorp.local;Initial Catalog=database_x;User ID=username;Password=password_here;MultipleActiveResultSets=True;Encrypt=False or (for Windows Authentication) Integrated Security=true instead of User ID=username;Password=password_here, and uses the (new) Microsoft.Data.SqlClient to connect to the database.

Let’s look at all applied properties:

• Data Source points to the actual network address of the SQL Server instance.
• Initial Catalog points to the actual database.
• MultipleActiveResultSets is a good topic for another blogpost. I always enabled it in the past because of some Entity Framework issues, but it seems MARS is only needed and useful in certain scenarios.
• The auth part is handled via IntegratedSecurity or User-Id

Since Version 4.0 of the Microsoft.Data.SqlClient the Encrypt property defaults to true instead of false, and now we are entering the field of encryption…

Encryption

We usally use Encrypt=False, because in most cases, there is no proper certificate installed on the SQL Server - at least this is our experience with our clients. If a client has a proper setup, we recommend using it, of course, but most of the time there is none.

With Encrypt=True, the data between the client and the server is TLS encrypted (and this is a good thing, and the breaking change therefore had a good intention).

If you are interested how to set it up, this might be a good starting point for you: Configure SQL Server Database Engine for encrypting connections

In some cases, your client might not be able to trust the server certificate (e.g. there is just a self-signed cert installed on the SQL server). Then you can disable the certification validation via TrustServerCertification, but this shouldn’t be used (at least in production) or handled with care. If the certificate doesn’t match the name of the Data Source, then you can use HostNameInCertificate.

What have I learned?

I already knew about Encrypt=True or Encrypt=False, but the behavior of TrustServerCertification (and when to use it) was new for me. This Stackoverflow-question helped me a lot to discover it.

Hope this helps!