[sql-server] 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?




I had the same handshake issue when connection to a hosted server.

I opened my Network and sharing center and enabled IPv6 on my wireless network connection.




The "Connection Timeout expired" error commonly occurs in the following cases

  • An instance of the SQL Server Database Engine is not running.
  • The SQL Server Browser service is not running.
  • The TCP/IP is disabled.
  • The server name was typed incorrectly.
  • There are network problems.
  • The TCP/IP port for the Database Engine instance is blocked by a firewall.
  • The client and server are not configured to use the same network protocol.

To check how to trace this error based on the above reasons check Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment




I fixed this error on Windows Server 2012 and SQL Server 2012 by enabling IPv6 and unblocking the inbound port 1433.




I had this problem when I did a SharePoint 2010 to 2013 migration. I suspected that because the database server is on the other side of a firewall which does not route IP6 that it was trying then to use IP6 and failing when connecting to the database.

I think the issue is now solved. The errors seem to have stopped. What I did was I simply disabled IP6 (by unchecking it) for the network adapter on the SharePoint Servers.




In my case above all options were already there.

Solved it by increasing Connection Time-out = 30.




Related