Search code examples
.netsql-serveriisconnection-string

"The certificate chain was issued by an authority that is not trusted." Occurring without explanation

I have four identical environments containing Windows Server 2019 servers running IIS and Microsoft SQL Server 2019. ASP.NET 4.7.2 applications on the IIS servers access databases on our SQL servers regularly by various means (Entity Framework, ADO.NET). We call these environments DEV, UAT, STAGE, and PROD. None of the SQL servers are configured to use a SSL certificate for the SQL SERVER process, and each IIS server has been issued a certificate by our own CA.

On our DEV environment alone we have a recurring issue with ADO.NET connections where we receive an exception with the error message:

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

when executing a SQLCommand. Another application on the same IIS server accessing the same SQL server using Entity Framework is unaffected somehow. SQL Server Management Studio is unaffected as well.

I've tried various proposed solutions in the client application without success:

  • Updating the connection string with TrustServerCertificate=true
  • Updating the connection string with Encrypt=false
  • Updating from System.Data.SqlClient to Microsoft.Data.SqlClient

I have been unable to discern any meaningful configuration differences between our environments.

Are there any possible IIS or SQL Server configuration issues I should check for that may fix this?

Solution

  • I found my explanation.

    Originally the error message surfaced in an ASP.NET error page on the application I was working on. Naturally I thought this had to do with the connection between the IIS Server and the SQL Server. However, every remedy that modified the Connection String to Trust the Server Certificate or turn off Encryption would fail to solve the issue.

    I observed a different application connecting to the same server without issue and without any connection string modifications. Curious I launched SQLCMD with the same parameters as my connection string and ran the following:

    use myDatabase;
exec myProcedure; --called by my ASP.NET page
go;

    I found that in addition to the untrusted certificate chain message, another message appeared that did not filter down to ASP.NET. The procedure being called was dependent on a Linked Server Connection and that connection from one SQL server to another SQL server was failing - not the connection from IIS to SQL server.

    So in the end we ended up enrolling our SQL servers in our company's public key infrastructure and setting up the SQL servers to use certificates.