[sql-server] Connection to SQL Server Works Sometimes

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 exact issue, tried several soultion didnt work , lastly restarted the system , it worked fine.

I had the same problem, manage to solve it by opening/enabling the port 1433 and tcp/ip in SQL Server Configuration Manager and then Restarted the Server

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!

I solved the problem like Eric but with some other changes:

• Start Sql Server Configuration Manager
• Open the node SQL Server Network Configuration
• Left-click Protocols for MYSQLINSTANCE
• In the right-hand pane, right-click TCP/IP
• Click Properties
• Select the IP Addresses tab
• For each listed IP address, ensure Active and Enabled are both Yes.

AND

• For each listed IP address, ensure TCP Dynamic Ports is empty and TCP Port = 1433 (or some other port)
• Open windows firewall and check that the port is Opened in Incoming connections

Solved this problem by blocking/ blacklisting IP address that were trying to brute force user accounts. Check your SQL access logs for large numbers of failed login attempts (usually for the 'sa' account).

Adding a response here, despite previously accepted answer. As my scenario was confirmed to be DNS. More specifically, a dns timeout during the pre-login handshake. By changing from a DNS name to an IP Address (or using Hosts file entry), you bypass the problem. Albeit at the cost of losing automatic ip resolution.

For example, even with a Connection String's timeout value set to 60 for a full minute, it still would happen within a couple seconds of the attempt. Which leads one to question why would it timeout before the specified timeout period? DNS.

For me, it turns out that the firewall in windows server was blocking the port 1433 which is the default sql server port. So adding an inbound rule to accept those connections made the trick for me.

Before you lose more time solving the problem, like me, try just to restart your windows machine. Worked for me after applying all the other solutions.

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.

My executable that was built using .NET Framework 3.5 started reporting these connection issues in about half of the times after some Windows Updates got installed recently (week of Aug 7, 2017).

Connection failures were caused by .NET Framework 4.7 that got installed on target computer (Windows Updates auto-install was on) - https://support.microsoft.com/?kbid=3186539

Uninstalling .NET Framework 4.7 solved connection issues.

Apparently, there is a breaking change in .Net Framework 4.6.1 - TransparentNetworkIPResolution Updating connection string as per article also solved the issue without the need to roll back the framework version.

I had the same issue that automatically resolve after last Microsoft windows update, does anyone experience the same?

Unfortunately, I had problem with Local SQL Server installed within Visual Studio and here many solutions didn't work out for me. All I have to do is to reset my Visual Studio, by going to:

Control Panel > Program & Features > Visual Studio Setup Launcher

and click on More button and choose Repair

After that I was able to access my Local SQL Server and work with local SQL Databases.

In my case, the parameter Persist Security Info=true with the user and password in connection string is causing the problem. Removing the parameter or set to false solve the problem.

I had the same problem, trying to connect to a server in a local network (through VPN) from Visual Studio, while setting up an Entity Data Model.
Managed to solve only by setting TransparentNetworkIPResolution=false in the connection string. In VS Add Connection Wizard, you can find it in the Advanced tab.

Try a simple SQL Server restart first before doing anything drastic. Might fix it. It did for me

I had this same issue, but I was connecting to a remote db using a static IP address. So none of the above solutions solved my issue.

I had failed to add the proper User Mapping for the security Login I was using, so the solution for me was simply to ensure the User Mapping setting was set to access my database.

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.

To trace the "Connection Timeout expired" error, Please, make sure that:

• The instance of the SQL Server Database Engine is up and running.
• The SQL Server Browser service is running.
• The TCP/IP is enabled.
• The server name was typed correctly.
• There are no network problems as mentioned at How to Check SQL Server Instance Connectivity from the application server to database server
• The TCP/IP port for the Database Engine instance is not blocked by a firewall.
• The client and server are configured to use the same network protocol.

For more details, Please check Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment

In our case problem occured due to availability cluster configuration. To solve this issue we had to set MultiSubnetFailover to True in the connection string.

More details on MSDN

In my case above all options were already there.

Solved it by increasing Connection Time-out = 30.

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