Error occurred during the pre-login handshake

Question

Please read in the entirety before marking this as duplicate.

In a project that I am debugging I receive a SqlException saying the following:

Additional information: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)

This occurred during a debugging session where the previous session executed only seconds before without problem. Since the initial exception, I am unable to connect to the database server in this project. The exception is thrown on the SqlConnection.Open() method call.

The Background

This is not the first time that I have received this. Previously I struggled with it for two weeks eventually initiating a Microsoft support ticket for it. In that instance it turned out the ApplicationName property on the connection string was too long (we were using the fully qualified assembly name) and shortening it alleviated the problem.

This time around, there is

• No ApplicationName value supplied
• WinSocks is in its default state
• Antivirus (ESET) was disabled and was not the issue.
• Nothing was installed between a working and non-working debug session

Finally, on a whim, I created a new project whose sole purpose was to connect to this same SQL server. I copied the connection string from the non-working project, into the new project and it connects. Is there some kind of per-project connection caching going on? Something that survives a Clean>Rebuild and a restart of Visual Studio and Windows too?

Relevant Code

    public SqlConnection OpenSqlConnection(string connectionString)
    {
        var conn = new SqlConnection(connectionString);
        conn.Open();
        _connectionString = connectionString;
        var sb = new SqlConnectionStringBuilder(_connectionString);
        _server = sb.DataSource;
        _database = sb.InitialCatalog;
        return conn;
    }

The connection string that is being passed in is output from a SqlConnectionStringBuilder elsewhere in the application. The connection string is similar to: "Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=True;Connect Timeout=60"

Solution 1

So this issue continued to plague me and it appears that it was a function of lag from my home network (I am a remote developer) and the work network accessed over a VPN. I have a 100ms average ping time to servers on the work network.

The real oddity is that the connection string worked without problem for months, then suddenly stopped. At the time, the Application Name value was something like Application Name = "MyProgram.DAL. Culture=en, PublicKeyToken=1a1a1a1a1a1a1a1a, Version=1.0.0.0". In other words a fully qualified assembly name. Eventually I changed this to the shortened "MyProgram.DAL" type name and it worked again.

Some months later, again I was beset by it. And I happened to find that if I just ate the exception and waited a few ticks, everything was fine. The application would happily use the connection even though it reported that it failed. Thus, I changed the method to the below:

    public SqlConnection OpenSqlConnection(string connectionString)
    {
        var conn = new SqlConnection(connectionString);
        var retries = 10;
        while (conn.State != ConnectionState.Open && retries > 0)
        {
            try
            {
                conn.Open();
            }
            catch (Exception)
            {

            }
            Thread.Sleep(500);
            retries--;
        }

        _connectionString = connectionString;
        var sb = new SqlConnectionStringBuilder(_connectionString);
        _server = sb.DataSource;
        _database = sb.InitialCatalog;
        return conn;
    }

Solution 2

What did the trick for me is increasing the timeout on the connection string, since when connecting by vpn it took to long to establish the connection. You can do this by adding ;connection timeout = value

I got the same error when connecting an application tried to connect to sql server while I was on a vpn.

By default the timeout is set to 15 seconds.

Seems you already have 60secs, maybe you just need more...

Hope it helps!

Solution 3

Try explicitly adding Application Name=MyAppName; to the connection string. Auto-generated value from the assembly name might exceed some limit.

Check network settings for things like explicitly limited frame size. Reboot router if SQL Server is running on another machine.

Try adding Pooling=False; to the connection string and checking whether this solves the problem with repeated connections on application restart.

Solution 4

This problem can be related to a firewall in the middle that is doing SSL inspection.

I Suggest you either try again using another connection not doing SSL inspection, or ask your firewall admin to create an exemption for the source and/or destination you are connecting to,

Cheers!

View more solutions

Comments

[CodeWarrior]

Addition of Application Name property had no effect. Network settings have not changed (recall, this happened between one debug session and the next, a timespan of a minute at most during which all I was doing was hitting the stop button in VS and the start/debug button. Adding Pooling=false had no effect.

[Y.B.]

OK. Can you try connecting with login and password instead of Integrated Security? Is Data Source=SERVER identified by IP or Name? If it's Name is it in local hosts file?

[CodeWarrior]

Connecting via SQL Authentication also exhibits the problem. The Data Source is by hostname and the hostname is in HOSTS. Connecting via IP address also exhibits the problem. Keep in mind that the error message states that it connected to the database, and that an error occurred during the pre-login handshake. Network connectivity to the server should not be a problem in these cases as it says it has successfully connected to the server.

[Y.B.]

E-e-er... Thanks, but I'd rather not receive reputation points for the answer that did not solve the problem. Have you given up or found the solution? Judging by the question votes, quite a few members (me included) would appreciate if you could share it.

[Y.B.]

One more thing to check came to my mind: does the project that exhibit the problem have "SQL Server Debugging" activated in "Configuration Properties" -> "Debugging"? Does the problem persist if run in other Project Configurations?

[Zach Mierzejewski]

This just fixed it for me, in a completely unrelated app/context. When I left the field blank, it would error after 15 secs. When I changed it to 60 secs (well, 1 min), it connected instantly!