Connection to SQL Server Works Sometimes


Answers

Ive had the same error just come up which aligned suspiciously with the latest round of Microsoft updates (09/02/2016). I found that SSMS connected without issue while my ASP.NET application returned the "timeout period elapsed while attempting to consume the pre-login handshake acknowledgement" error

The solution for me was to add a connection timeout of 30 seconds into the connection string eg:

ConnectionString="Data Source=xyz;Initial Catalog=xyz;Integrated Security=True;Connection Timeout=30;"

In my situation the only affected connection was one that was using integrated Security and I was impersonating a user before connecting, other connections to the same server using SQL Authentication worked fine!

2 test systems (separate clients and Sql servers) were affected at the same time leading me to suspect a microsoft update!

Question

An ADO.Net application is only sometimes able to connect to another server on the local network. It seems random whether a given connection attempt succeeds or fails. The connection is using a connection string in the form:

Server=THESERVER\TheInstance;Database=TheDatabase;User Id=TheUser; Password=ThePassword;

the error returned is:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.
This could be because the pre-login handshake failed or the server was unable to respond back in time.
The duration spent while attempting to connect to this server was - [Pre-Login] initialization=42030; handshake=0;

The .NET application is a small test app that executes the following code:

using (SqlConnection conn = new SqlConnection(cs))
using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM TheTable", conn))
{
    conn.Open();
    int rowCount = (int)cmd.ExecuteScalar();
}

TheTable is small, just 78 rows.

However, on the same machine where the .NET application receives this error, I am able to connect to THESERVER using SSMS and the User Id/Password named in the connection string.

Why might the connection fail from an ADO.Net app, but succeed with identical credentials from SSMS?




SQL Server Pre-Login Handshake Acknowledgement Error

I ran into an issue similar to this myself a few months ago.

For me, enabling TCP/IP for both IPV4 and IPV6 in SQL Server Configuration manager fixed the issue. I have also seen this occur even when IPV6 is not installed. Oddly enough, I've even experienced this behavior even when I only specify IPV4 addresses?!




Had the same problem/error msg. For me, it's the firewall settings on server. Make sure TCP port 1433 is open for incoming traffic.




SQL Server Pre-Login Handshake

According MSDN Doc .

ClearPool clears the connection pool that is associated with the connection.If additional connections associated with connection are in use at the time of the call, they are marked appropriately and are discarded (instead of being returned to the pool) when Close is called on them.

you should use ClearPool method before connection.close

refer : https://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlconnection.clearpool(v=vs.110).aspx

And If you use Using syntax to manage Object you should't use connection.close method

because it will call when they run to end . just open your connection and execute your command

 using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlConnection.ClearPool(connection);
        /* my commands here */
        SqlCommand cmd = new SqlCommand("your command",conn);
        cmd.ExecuteReader()


    }

refer example : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

last ensure SQL Server Network Config is right







Tags