Visual Studio Debug SQL Server Connection String - "SSL Provider, error 0 - The certificate chain was issued by an authority that is not trusted"

Question

I recently wiped my laptop and reinstalled everything, including Visual Studio. Since doing so, my connection string for an intranet web app (connecting to a local SQL Server DB) no longer works in debug...

<add name="Company Database" connectionString="Data Source=Server\Instance;Initial Catalog=&quot;DBName&quot;;Persist Security Info=True;User ID=User;Password=Pass" providerName="System.Data.SqlClient" />

It works fine when deployed to a server, but when I try to run the app in debug I get the error "SSL Provider, error 0 - The certificate chain was issued by an authority that is not trusted". The posts I've found matching this error typically suggest to use (with caution for an external facing app) encrypt=false or TrustServerCertificate=true. When I try these, the text is underlined in green and tooltip displays "the encrypt attribute is not allowed". Have tried updating "System.Data.SQLClient" to "Microsoft.Data.SQLClient" but this doesn't appear to make any difference. Can anyone assist please? Thanks

Answer 1

The issue you're experiencing is likely because the SSL certificate for your SQL Server is not trusted by your machine or Visual Studio. Here are several potential solutions:

Solution 1: Considering "TrustServerCertificate=true" did not solve the problem, you may want to try adding "encrypt=false" to your connection string. This tells SQL Server to not encrypt the communication, which can circumnavigate SSL misconfigurations.

Solution 2: It's possible that the SQL Server that you're trying to connect to is using a self-signed SSL certificate. If it's acceptable in the context of your project, consider having the SQL Server use a certificate from a well-known, trustworthy certificate authority.

Solution 3: Consider importing the SQL Server's SSL certificate into your machine's trusted root certificate store. This should make your machine trust the SSL certificate.

Solution 4: Unchecked Encrypt connection under tab Connection Properties

Please remember to exercise caution and ensure that any changes to your security settings are consistent with your security requirements.