A connection was successfully established with the server, but then an error occurred during the login process Error from .Net core WebAPI

Amit Potdar 131 Reputation points
2021-12-14T12:23:52.077+00:00

Hi,

I've an application that uses WebAPI in .Net core 6 and uses Kestrel server. When calling HttpPost request, the code tries to inject data in DB but fails with error:

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

Inner exception: A connection was successfully established with the server, but then an error occurred during the login process

The obvious resolution is to inject TrustServerCertificate=True in the connection string. But this is a security risk as it can trust invalid certificate.

I'm using Dapper package to communicate with Sql server database.

After investigation, I found that this happens when I refer Microsoft.Data.SqlClient package. If I replace with System.Data.SqlClient package, above error occurs no more and I don't have to inject TrustServerCertificate flag.

1) Can anyone let me know why this error is raised when I refer Microsoft.Data.SqlClient?
2) Which package (System.Data.SqlClient or Microsoft.Data.SqlClient) is recommended for the new development (i.e. .Net 6 and Kestrel)

Thanks,
Amit

ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,059 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,400 questions
{count} votes

10 answers

Sort by: Most helpful
  1. Naveed Ahmed 341 Reputation points
    2022-04-15T22:05:07.607+00:00

    Try adding TrustServerCertificate=True; to your connection string.

    68 people found this answer helpful.

  2. Amit Potdar 131 Reputation points
    2021-12-15T12:42:27.133+00:00

    After adding Encrypt=False to the connection string, it worked!!!

    18 people found this answer helpful.

  3. Ronen Ariely 15,081 Reputation points
    2021-12-15T05:46:50.15+00:00

    Hi,

    I found that this happens when I refer Microsoft.Data.SqlClient package. If I replace with System.Data.SqlClient package, above error occurs no more

    System.Data.SqlClient is the ADO.NET old provider used by .NET Framework. The Microsoft.Data.SqlClient package was released in 2019 and it is the new package which supports both .NET Core and .NET Framework. You should prefer using the new package, which support new features in SQL Server.

    https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

    Please check the following document for Possible reasons and solutions for this error:

    https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver15#login-phase-errors

    1) Can anyone let me know why this error is raised when I refer Microsoft.Data.SqlClient?

    Probably the TLS 1.2 is not enabled. You might need to update the server to the last version.

    Use the following document to determine whether your current version of SQL Server already has support for TLS 1.2

    https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe

    If you still do not succeed, then please execute: SELECT @@VERSION and provide the exact information of the version.

    4 people found this answer helpful.

  4. Rijwan Ansari 741 Reputation points MVP
    2023-01-11T13:12:45.9433333+00:00

    Hi @Amit Potdar

    I faced similar issues a few months back.

    Here the solution [https://rijsat.com/2022/12/12/entity-framework-update-database-error-the-certificate-chain-was-issued-by-an-authority-that-is-not-trusted/

    Update Your connection String as shown below.

    
    
    

    We need to add additional parameters trusted_connection=true;encrypt=false; in the connection string.

    This is because of SQL server update.

    The default value of the Encrypt connection setting has been changed from false to true. With the growing use of cloud databases and the need to ensure those connections are secure, it’s time for this backwards-compatibility-breaking change.

    4 people found this answer helpful.

  5. Darren Lawrence 6 Reputation points
    2022-11-09T17:04:53.937+00:00

    Had this issue today

    Excel connection to SQL Data source, failed with this exact same message.

    Turns out we only needed to install the SQL Client Tools v11 x64 and re-open the workbook and all worked again.

    1 person found this answer helpful.