SQL Server Pre-Login Handshake Acknowledgement Error [duplicate]

System.Data.SqlClient.SqlException: 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=36; handshake=14997; ---> System.ComponentModel.Win32Exception: The wait operation timed out

Connection to SQL Server Works Sometimes

回答1

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.

评论：

I'm not sure that the individual entries being disabled is a problem as the Protocol tab has an override 'listen all' which tells SQL to listen on all IP's. See the following link for documentation. msdn.microsoft.com/en-us/library/dd981060.aspx

– ShaneH

Jun 25 '15 at 15:10

回答2

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!

回答3

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

回答4

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.

The setting is TransparentNetworkIPResolution=False. It's a new feature as of .NET 4.6.1 and is on by default. Setting this to false will remove the 500ms timeout that this feature creates. For more information: blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/…

– Jorriss

Aug 11 '16 at 15:40

回答5

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