Skip to content

Microsoft.Data.SqlClient 2.0.0 A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - ) #609

Closed
Closed
Microsoft.Data.SqlClient 2.0.0 A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )#609

Description

@hovek
hovek
opened on Jun 17, 2020

After a NuGet update of Microsoft.Data.SqlClient from 1.1.3 to 2.0.0 I am no longer able to connect to SQL Server 2017 14.0.3294.2 (latest update).
I get an error: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: HTTP Provider, error: 0 - )

Code:

using (var cnn = new SqlConnection(CreateConnectionString(server, port, LoginDatabaseName, Username, Password)))
            {
                var cmd = new SqlCommand("uspGetDatabasesByLogin", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@LoginName", login));

                try
                {
                    cnn.Open();
                }
                catch (SqlException ex)
                {
                    //it goes here
                    greska = ex;
                    return ret;
                }
...

Activity

  1. ErikEJ commented on Jun 17, 2020

    @ErikEJ
    ErikEJ
    on Jun 17, 2020
    Contributor

    What OS are you using?
    Maybe this is relevant: #567 (comment) ?

  2. hovek commented on Jun 17, 2020

    @hovek
    hovek
    on Jun 17, 2020 via email
    Author

  3. cheenamalhotra commented on Jun 17, 2020

    @cheenamalhotra
    cheenamalhotra
    on Jun 17, 2020
    Member

    Hi @hovek

    Please verify if your SQL Server forces encryption:

    image

    If yes, the driver now will validate server certificate on client systems.

    You can try below options:

    • Set Trust Server Certificate = true on connection string.
    • Export SSL Certificate used by SQL Server and install it on Client machine.
    • Do not force encryption from server (Set the Force Encryption to No)

    This change has been introduced with v2.0.0-preview2 as mentioned in release notes (breaking changes).

  4. hovek commented on Jun 17, 2020

    @hovek
    hovek
    on Jun 17, 2020 via email
    Author

  5. ErikEJ commented on Jun 17, 2020

    @ErikEJ
    ErikEJ
    on Jun 17, 2020
    Contributor

    Maybe it would be helpful to have all breaking changes listed in on place?

  6. cheenamalhotra commented on Jun 17, 2020

    @cheenamalhotra
    cheenamalhotra
    on Jun 17, 2020
    Member

    That's been worked upon and will be published on Microsoft Docs soon.

  7. ErikEJ commented on Jun 17, 2020

    @ErikEJ
    ErikEJ
    on Jun 17, 2020
    Contributor

    I did a PR in the meantime!

  8. hovek commented on Jun 17, 2020

    @hovek
    hovek
    on Jun 17, 2020 via email
    Author

  9. cheenamalhotra commented on Jun 17, 2020

    @cheenamalhotra
    cheenamalhotra
    on Jun 17, 2020
    Member

    Hi @hovek

    SSL Handshake/Pre-Login errors can occur due to many reasons and if trust was established, we'd receive error code from SQL Server, which is provided in error message. If Error code is 0, that would mean we did not receive response from server as trust could not be established and we are not aware of the reasons in the scope of driver.

    Could you confirm if the above options worked for you and we are good to close this issue?

  10. hovek commented on Jun 18, 2020

    @hovek
    hovek
    on Jun 18, 2020 via email
    Author

  11. cheenamalhotra commented on Jun 19, 2020

    @cheenamalhotra
    cheenamalhotra
    on Jun 19, 2020
    Member

    Could you confirm if the above options worked for you and we are good to close this issue?

  12. hovek commented on Jun 20, 2020

    @hovek
    hovek
    on Jun 20, 2020 via email
    Author

  13. CumpsD commented on Jun 22, 2020

    @CumpsD
    CumpsD
    on Jun 22, 2020

    I am having the same issue with Microsoft.Data.SqlClient 2.0.0 using AspNetCore.HealthChecks.SqlServer.

    Our application is running in mcr.microsoft.com/dotnet/core/runtime-deps:3.1.5 and connects to a standard Azure Sql Server.

    It stopped working after upgrading SqClient.

    Trust Server Certificate = true; solved it too for us. Maybe add it to the release notes?

  14. CumpsD commented on Jun 22, 2020

    @CumpsD
    CumpsD
    on Jun 22, 2020

    @ErikEJ it was Server=Xxxx.database.windows.net;Database=Xxxx;User=Xxx@xxx.database.windows.net;Password=Xxxx;

    By adding Server=Xxxx.database.windows.net;Database=Xxxx;User=Xxx@xxx.database.windows.net;Password=Xxxx;Trust Server Certificate=true; it works.

  15. ErikEJ commented on Jun 22, 2020

    @ErikEJ
    ErikEJ
    on Jun 22, 2020
    Contributor

    Thanks, missed your update to your original post

  16. cheenamalhotra commented on Jun 22, 2020

    @cheenamalhotra
    cheenamalhotra
    on Jun 22, 2020
    Member

    Trust Server Certificate = true; solved it too for us. Maybe add it to the release notes?

    This breaking change is mentioned in release notes, please view them in 2.0.0.md. Since the solution to server certificate validation is respective to customer application requirements, it's in the applications hands which option they would like to choose.

    However the solutions are not new when connecting with forced encryption on server, and are same as documented here: Microsoft Docs: Using Encryption without validation), it was an incorrect driver behavior to not validate server certificate which has been fixed in this release.

    We will close the issue as resolved.

  17. cheenamalhotra
    closed this as completedon Jun 22, 2020

  18. shaojun commented on Mar 6, 2023

    @shaojun
    shaojun
    on Mar 6, 2023

    could you guys help to take look at this similar issue: microsoft/azuredatastudio#11249 (comment)

  19. ErikEJ commented on Mar 6, 2023

    @ErikEJ
    ErikEJ
    on Mar 6, 2023
    Contributor

    @shaojun That is not similar, this repo is not for the no longer supported System.Data.SqlClient but for Microsoft.Data.SqlClient

  20. shaojun commented on Mar 9, 2023

    @shaojun
    shaojun
    on Mar 9, 2023

    @ErikEJ I've updated to use Microsoft.Data.SqlClient and still got the issue for connecting to MS sql server 2008, the details are in the post, could you help to take a look

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions