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

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

RPBG 25 Reputation points

2023-11-02T16:02:52.41+00:00

I resolved this by clicking on Options>Connection Properties>checking Trust server certificate.
Precious Ofoyekpene 0 Reputation points

2024-03-29T11:01:41.21+00:00

Thanks man that really helped

10 answers

Ronen Ariely 15,096 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.
Please sign in to rate this answer.

5 people found this answer helpful.
Amit Potdar 131 Reputation points

2021-12-15T12:15:42.39+00:00

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

Ronen Ariely 15,096 Reputation points

2021-12-16T06:43:20.37+00:00

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://learn.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.
Sign in to comment
Amit Potdar 131 Reputation points

2021-12-15T12:42:27.133+00:00

After adding Encrypt=False to the connection string, it worked!!!
Please sign in to rate this answer.

18 people found this answer helpful.
Ronen Ariely 15,096 Reputation points

2021-12-16T23:39:47.18+00:00

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

Aamir 6 Reputation points

2022-03-23T16:20:26.183+00:00

Adding Encrypt = False also helped me!

muqofa alfarizy 1 Reputation point

2022-04-09T16:33:47.92+00:00

Work for me, thank you

vikram_shaw 31 Reputation points

2022-07-08T07:17:50.62+00:00

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

Thank you.

Md. Solaiman Shohag 1 Reputation point

2022-11-09T16:07:45.543+00:00

Thanks man. Your solution is worked for me.

TaylorGANG557 1 Reputation point

2022-11-12T17:37:58.943+00:00

Thanks I stayed up so late trying to figure this out last night, and this helped me

Davi Veronez 1 Reputation point

2022-12-04T13:53:56.087+00:00

It worked for me too!

I put the

using System.Data.SqlClient;

Before Encrypt=False in the connection string and it worked.

Adam 119184 1 Reputation point

2022-12-24T13:41:45.313+00:00

Thanks, adding Encrypt=False worked for me too

David Lester 1 Reputation point

2022-12-29T08:46:02.33+00:00

Thanks I thought I was screwed until I read this. Same database and SqlClient works entityframework 6 and 7 failed. Added this and everything is as it should be. It does appear to be an issue only with using EF core 6 and 7.

Max 0 Reputation points

2023-02-12T10:58:14.7633333+00:00

Worked for me as well thank you :)

Gabriel Vinicius 0 Reputation points

2023-07-19T19:03:29.71+00:00

work for me, thank you

DAUDA 0 Reputation points

2023-10-30T15:56:33.53+00:00

Thanks. This solution perfectly resolved the issue.
Sign in to comment
Naveed Ahmed 351 Reputation points

2022-04-15T22:05:07.607+00:00

Try adding TrustServerCertificate=True; to your connection string.
Please sign in to rate this answer.

71 people found this answer helpful.
Ledesma, Eduardo 16 Reputation points

2022-05-04T17:04:44.787+00:00

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

Cristina Raicovici 26 Reputation points

2022-09-07T12:39:35.507+00:00

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",

Abdellah GRIB 1 Reputation point

2022-11-18T19:40:41.45+00:00

Worked for me !

abdulkader najib 0 Reputation points

2023-01-11T12:05:52.6933333+00:00

This worked..

Стас Кухар 0 Reputation points

2023-01-15T17:14:50.85+00:00

Hi, i get this problem when i have generate auto models for my db. it is work, but don't forget you need add this parametters in command for generate models, if you do tha same that i am do. Thanks!

ehalfonso 5 Reputation points

2023-01-19T05:07:51.3766667+00:00

Me funciono, mil gracias

orlando martinez 5 Reputation points

2023-02-02T03:45:53.9533333+00:00

Stupid question, how do you that?

Erik Zimmermann 0 Reputation points

2023-02-15T13:35:17.2166667+00:00

THANK YOU! VERY HELPFUL FOR ME!!!! BY ERIK

Matias Araneda Araneda 0 Reputation points

2023-02-19T02:06:01.97+00:00

Now it works, thank you very much.

Saeed Pourali 0 Reputation points

2023-03-23T12:08:31.7433333+00:00

I faced with same error, this is helped to me to generate my models on visual studio

Blasco73 0 Reputation points

2023-03-24T15:24:57.1466667+00:00

Great. It works for me. TKS A LOT

ahmed besar 0 Reputation points

2023-05-02T02:12:17.2933333+00:00

thanks bro

Hernandez, Henry [EMR/ENT/IT/HSTN] 0 Reputation points

2023-05-09T18:23:46.8633333+00:00

Thanks! it worked.

Jwaldock 0 Reputation points

2023-05-11T19:48:16.0266667+00:00

But where/how do I update the connection string??

Juancho 0 Reputation points

2023-05-25T22:36:00.8866667+00:00

Exactly, this worked for me.

Anastasiia 0 Reputation points

2023-05-27T07:31:04.22+00:00

I've had the same problem, and your advice would be helpful. Thanks very much!

mahdi almasi 0 Reputation points

2023-08-07T07:23:04.8866667+00:00

its worked tnks a lot

just add it in the end of connection string

Diya 0 Reputation points

2023-08-24T08:44:30.19+00:00

that worked, thank you!!

Mayores, Marlowe James F 0 Reputation points

2023-09-04T09:27:26.94+00:00

Very Helpful. Thank you.

Smart Alfred 0 Reputation points

2023-09-20T22:48:30.67+00:00

Its work perfectly 👍

Vladimir Silvestre Felix 0 Reputation points

2023-09-26T18:08:32.0333333+00:00

Awesome!

gabriel silva 0 Reputation points

2023-10-16T20:21:55.9266667+00:00

It still work in october 2023.

C_LA_ROSA 0 Reputation points

2023-10-23T13:58:15.4366667+00:00

Funcionó :)

Varinder Guraya 0 Reputation points

2024-03-07T18:28:29.63+00:00

Thanks Naveed, this helped.
Sign in to comment
kosmos 246 Reputation points

2022-10-12T14:38:57.963+00:00

Hi,
The proposed solutions did not work for me. Any ideas ?

Please see screenshot.

I am trying to connect to a Synapse SQL Dedicated Pool with visual code studio
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Francisco Javier Martínez Sánchez 1 Reputation point

2022-11-08T10:53:32.82+00:00

I has the same problem, with this solution !now work!. Thanks
Please sign in to rate this answer.

0 comments No comments
Sign in to comment