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?

It turned out that TCP/IP was enabled for the IPv4 address, but not for the IPv6 address, of THESERVER.

Apparently some connection attempts ended up using IPv4 and others used IPv6.

Enabling TCP/IP for both IP versions resolved the issue.

The fact that SSMS worked turned out to be coincidental (the first few attempts presumably used IPv4). Some later attempts to connect through SSMS resulted in the same error message.

To enable TCP/IP for additional IP addresses:

• 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.

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

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.

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.

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

This is another solution to the error in the OP...there are many solutions as there are many causes.

I had installed the Developer Edition of MSSql 2019. By default, its installation is locked down...it will run fine if its just on your development machine. If you install it on a machine other then your dev box, you will need to update the fire wall rule.

> By default...the Firewall Profile for "MS SQL Server" is Private

You may need to enable the Public and/or Domain profiles. Use Domain only if your authenticing on a Domain.
Windows FireWall Profiles

Also...to enable all the ip addresss(like in the accepted answer) all you need to do is

> Set "Listen All" to yes on TCP/IP properties

TCP/IP Properties Options

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 problem, manage to solve it by opening/enabling the port 1433 and tcp/ip in SQL Server Configuration Manager and then Restarted the Server

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.

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.

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).

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

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.

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.

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.

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.

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

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.

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

In C# this was driving me crazy, it was working in parts of my code and failing in one specific area using the same singleton. Turns out, I was using impersonation to read files and I was calling the SQL connection using the "trusted connection" which was using the elevated permissions. That elevated permission user didn't have access to the SQL DB so it was failing on that method only. I moved the call outside of the impersonation and it worked great after that.