Sometimes connect to SQL Server

Question

ADO.Net can sometimes connect to another server on the local network. It seems random whether this connection attempt is successful or unsuccessful. A connection uses a connection string in the form:

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

returned error:

Connection timed out. The timeout period has expired while trying to use an acknowledgment confirmation before entering the system.
This may be due to the fact that the handshake before entering the system failed or the server was unable to respond on time.
The duration spent trying to connect to this server was - [Pre-Login] initialization = 42030; acknowledgment = 0;

The .NET application is a small test application that runs 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, only 78 lines.

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

Why can the connection be disconnected from the ADO.Net application, but using the same credentials from SSMS?

Answer 1

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

Apparently, some connection attempts were using IPv4, while others were using IPv6.

Enabling TCP / IP for both versions of IP resolves the issue.

The fact that SSMS worked turned out to be random (the first few attempts were supposedly used by IPv4). Some subsequent attempts to connect via SSMS led to the same error message.

To enable TCP / IP for additional IP addresses:

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

Answer 2

I got the same error that suspiciously aligned with the last round of Microsoft updates (02/09/2016). I found that SSMS connected without problems while my ASP.NET application returned a "timeout period when trying to use acknowledgment acknowledgment confirmation"

The solution for me was to add a wait time of 30 seconds to the connection string, for example:

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

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

At the same time, 2 test systems (individual clients and Sql servers) were affected, which led me to suspect a microsoft update!

Answer 3

I solved a problem like Eric, but with some other changes:

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

AND

• For each IP address you specify, verify that the TCP dynamic ports are empty and TCP port = 1433 (or some other port)
• Open the Windows firewall and make sure the port is open in incoming connections

Answer 4

I had the same problem when I tried to connect to the server on the local network (via VPN) from Visual Studio when setting up the entity data model.
Only successfully resolved by setting TransparentNetworkIPResolution=false in the connection string. In the VS Add Connection Wizard, you can find it on the Advanced tab.

Answer 5

I had the same problem with a handshake when connecting to a hosted server.

I opened my network and exchange center and enabled IPv6 in my wireless network connection.

Answer 6

My executable file, which was created using the .NET Framework 3.5, started reporting these connection problems about half the time after some Windows updates were recently installed (week of August 7, 2017).

Connection errors were caused by the .NET Framework 4.7, which were installed on the target computer (automatic installation of Windows Updates is enabled) - https://support.microsoft.com/?kbid=3186539

Uninstalling the .NET Framework 4.7 fixes connectivity issues.

There seems to be a breaking change .Net Framework 4.6.1 - TransparentNetworkIPResolution Updating the connection string according to the article also solved the problem without having to roll back the framework version.

Answer 7

I fixed this error in Windows Server 2012 and SQL Server 2012 by enabling IPv6 and unlocking incoming port 1433.

Answer 8

I had the same problem, I was able to solve it by opening / enabling port 1433 and tcp / ip in the SQL Server configuration manager and then Restarted server p>

Answer 9

In my case, there are already all the parameters above.

Solved it by increasing the connection timeout = 30.

Answer 10

Before you lose more time solving a problem like me, just try restarting Windows . Worked for me after applying all the other solutions.

Answer 11

I had this problem when I migrated SharePoint 2010 to 2013. I suspected that because the database server is on the other side of the firewall, which does not route IP6, and then tries to use IP6 and cannot connect to the database.

I think now the problem is resolved. The errors seem to have stopped. What I did was simply disable IP6 (disable it) for the network adapter on the SharePoint servers.

Answer 12

I had the same problem, but I was connecting to a remote db using a static IP address. Therefore, none of the above solutions have solved my problem.

I was unable to add the correct user mapping for the security login that I used, so for me it was just to make sure that the User Mapping parameter was set to access my database.

Answer 13

The "Connection timed out" error usually occurs in the following cases:

• The instance of SQL Server Database Engine is not running.
• SQL Server Browser Service is not running.
• TCP / IP is disabled.
• The server name was entered incorrectly.
• There are network problems.
• The TCP / IP port for the Database Engine instance is blocked by the 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 timed out. The wait period has expired while trying to use an authorization confirmation confirmation before entering

Answer 14

This problem was resolved by blocking / blacklisting the IP address that is trying to reset user accounts. Check your SQL access logs for a large number of failed login attempts (usually for the "sa" account).

Answer 15

For me, it turns out that the firewall in Windows Server blocked port 1433, which is the default SQL server port. So adding an inbound rule to accept these connections helped me.

Answer 16

In our case, the problem arose due to the availability of the cluster. To solve this problem, we had to set MultiSubnetFailover to True in the connection string.

MSDN Details

Answer 17

In my case, the Persist Security Info=true parameter with the user and password in the connection string causes the problem. Deleting a parameter or false solves the problem.

Answer 18

Try a simple restart of SQL Server before doing anything radical. Maybe fix it. It did for me