The certificate chain was issued by an authority that is not trusted

By Mirek on (tags: Code First, ef core, Entity Framework, SqlServer, SSL, categories: code, security)

Recently I got strange exception when trying to start my EF 6 based application. The problem started to happen after I updated some of Microsoft nuget packages.

The exception was thrown when EF context tried to establish a sql server connection:

Microsoft.Data.SqlClient.SqlException: '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.)'

The last part about the untrusted certificate authority was exactly what was the case. I was using local Sql Server Express edition and the development self-signed certificate was used to secure the connection. But why it didn’t complain before?

Well the problem turned out to be the Microsoft.Data.SqlClient in version 4.0.0 package and one of its release breaking change described here.
The long story short: they change the default setting of the sql connection that is responsible to force the connection encryption. The default was change from Encrypt=False to Encrypt=True. So obviously my connection was not encrypted before, thus no complains about untrusted certificate authority was raised.
Of course, this is a good change to force the SSL encryption by default and allow it to be disabled eventually. So the responsibility of the security is on the application owner.

But let’s see how we can handle the exception now. There are three ways we can go:

  1. Most secure solution is to install a real trusted SSL certificate on SQL Server, so the connection is truly secure. Here is the description on how to do it. That’s the recommended way, and I would say the obligatory one on the production environment.
  2. If you want to stick to the self-signed developer certificate you can tell the EF to trust it by adding a connection string parameter: TrustServerCertificate=True 
    Data Source=(local);Initial Catalog=Mydb;Integrated Security=True;TrustServerCertificate=True
  3. Last, not secure at all and not recommended in production environment option, is to disable the connection encryption at all. You can do it by modyfing the connection string by adding Encrypt=False option. 
    Data Source=(local);Initial Catalog=Mydb;Integrated Security=True;Encrypt=False

That's it.

Cheers