question

AmitPotdar-4976 avatar image
1 Vote"
AmitPotdar-4976 asked CristinaRaicovici-4352 commented

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

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

sql-server-generaldotnet-aspnet-core-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

pituach avatar image
2 Votes"
pituach answered pituach edited

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://docs.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.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,

Thanks for your suggestion and info.

As regards to TLS, is there a way not to choose TLS in Microsoft.Data.SqlClient?

We dont want to use any encrypted request to database.

Thanks,
Amit

0 Votes 0 ·

Hi,

We dont want to use any encrypted request to database.

I think that I was not clear :-)

When I said "Probably the TLS 1.2 is not enabled." , I meant that this happen when your client uses TLS 1.2 (block for lower encryption for example), while the server does not support it. For this case, you need to upgrade the server (Install the latest updates).

You do not have to use encrypted connection when using on-premises SQL Server. It is a mistake probably but it can be done.

By the way, using Azure SQL Database, you must use encrypted connection.

Note! If you do want to use encrypted connection then you cannot use SSL and you must use TLS since starting with SQL Server 2016 (13.x) Secure Sockets Layer (SSL) has been discontinued.

Check the following document which explain how to enable encrypted connection and do the opposite if you want to allow un-encrypted connection

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-ver15

In the link which I gave you in previous answer, you can see a few reasons for this error. Notice that one of the solution is to use Insecure connection:

Insecure solution: Disable the "Force Encryption" setting on SQL Server.

0 Votes 0 ·
AmitPotdar-4976 avatar image
3 Votes"
AmitPotdar-4976 answered vikram-shaw commented

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

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Glad to see that the issue was solved

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

Yes.... As I quoted from the document in my response :-)

Insecure solution: Disable the "Force Encryption" setting on SQL Server.

Just remember that this is the "Insecure solution" which is not recommended for most cases

0 Votes 0 ·

Adding Encrypt = False also helped me!

0 Votes 0 ·

Work for me, thank you

0 Votes 0 ·

Starts working after adding Encrypt=False to the connection string.

Thank you.

0 Votes 0 ·
NaveedAhmed-3967 avatar image
3 Votes"
NaveedAhmed-3967 answered CristinaRaicovici-4352 commented

Try adding TrustServerCertificate=True; to your connection string.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Adding TrustServerCertificate=True; to the connection string worked for me, thank you..!!!

0 Votes 0 ·

This worked for me too!
Just add it at the end of your connection string

   "ConnectionString": "Data Source=.;Initial Catalog=dbLocal;Integrated Security=True;MultipleActiveResultSets=True;TrustServerCertificate=True",
0 Votes 0 ·