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?

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?

• sql-server
• iis
• connection-string

Share Improve this question Follow edited Nov 20, 2024 at 22:28 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Nov 20, 2024 at 22:27 ZacharyZachary 14111 silver badge99 bronze badges 2

• If you know/search for .NET debugging tips, you might notice that exception call stack can tell very much about where the problem originated and from there you can know what might be the culprit. This question in its current form is not likely to be discussed further, as you don't even show the actual call stack. – Lex Li Commented Nov 21, 2024 at 4:38
• trust server certificate should work, maybe you didn't specify it correctly – siggemannen Commented Nov 21, 2024 at 9:03

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

If you only get this error on a SQL command then;

• Enable CAPI2 event logs on the machine where you run the SQL command and the IIS & SQL Server(s) that it tries to connect.

• Enable Schannel logging on the machine where you run the SQL command and the IIS and SQL Server(s) that it tries to connect (You may set 7 = Log all information value in the registry)

Ref: "https://learn.microsoft/en-us/troubleshoot/developer/webapps/iis/health-diagnostic-performance/enable-schannel-event-logging"

• Run the SQL command check both CAPI2 logs and schannel logs (Which should exist in System Logs) on both the machine you have run the command and the server(s).

You probably will find the necessary process id and the details of the certificate trust issue, and the details of the certificate that is in question in CAPI2 and Schannel logs. As soon as you find the certificate, find the server where the certificate was retrieved from. On the server that has this certificate, resolve any problems regarding expired, invalid, revoked conditions then make sure that the root CA of this certificate is trusted by the client PCs that you run the sql command (and probably on the IIS servers as well depending on your application)

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.