Connection to SQL Server Works Sometimes

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?

you might be hitting issue mentioned in https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/ — pardeepk, Nov 29 '17 at 09:56

score 111 · Accepted Answer · edited Jun 04 '14 at 15:39

111

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.

edited Jun 04 '14 at 15:39

Matt

74,352
26
153
180

answered Mar 18 '13 at 23:34

Eric J.

147,927
63
340
553

Thanks, this resolved the error for me. Interestingly, all the IP addresses were set to disabled (previously they were not). It would be good to know what can cause these to become disabled as I don't think the config will have been changed manually in my case... – Matt Aug 05 '14 at 12:26
I never manually disabled my IPv6 addresses either. I wonder too how they ended up disabled. – Eric J. Aug 05 '14 at 16:22
I am not able to make the IPv6 enabled for some reason. It says that service need to be restarted for changes to take effect but it never retains it as "Yes". Any clues on how to go aboout it – Salman Feb 02 '15 at 14:58
@S.A: I have not had that happen. Do you have IPv6 enabled in your network settings? – Eric J. Feb 02 '15 at 17:28
@EricJ. Yes it is enabled. However, issue was not with IPv6. It was with the dynamic porrt mentioned in Network Configuration of instance (some 5 digit random number). After removing it, I was able to connect remotely as well. Thanks your your help! – Salman Feb 03 '15 at 09:35
5

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. https://msdn.microsoft.com/en-us/library/dd981060.aspx – ShaneH Jun 25 '15 at 15:10
1

Fixed the problem for me, i'm learning databases and that error popped out of nowhere in SMSS. Thank you ! – jiciftw May 19 '21 at 21:28
I never had such problems: In SQL Server Configuration Manager, when you have "Enabled" set to "Yes" and "Listen All" set to "Yes" for the "TCP/IP" protocol under "Protocols for [InstanceName]", it means that the SQL Server instance will listen on all available IP addresses and network interfaces for incoming TCP/IP connections. – Dejan Dozet Apr 21 '23 at 11:58

score 47 · Answer 2 · edited Oct 25 '17 at 12:58

47

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!

edited Oct 25 '17 at 12:58

Hakan Fıstık

16,800
14
110
131

answered Feb 12 '16 at 03:51

Shaun Keon

717
7
4

2

I had the same problem, thanks for the resolution. I was connecting using integrated security via a VPN and increasing the default connection timeout from the default of 15s to 30s resolved the issue for me. – Mark G Sep 17 '16 at 19:36
1

I had this problem with SQL 2014 LocalDB after setup.exe installed the new application and the startup process was trying to create the new database. This fix saved me from spitting the dummy - thanks Shaun! – Scott Oct 26 '16 at 18:46
1

This resolved the issue for me as well. In my case I was connecting via VPN and adding an entry in the hosts file. The problem only occurred when using the host name in SSMS and .NET applications. When using the IP address, the problem did not occur. – Dan Nov 03 '16 at 19:48
Happened for me using .NET 5 core console mode application. This fixed it. – dodgy_coder Oct 26 '21 at 02:14
1

I have set connection timeout in connection string however I still see the same issue :( – user989988 Apr 25 '22 at 20:03

score 20 · Answer 3 · edited Sep 10 '20 at 20:54

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

score 14 · Answer 4 · edited Sep 26 '18 at 10:05

14

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.

edited Sep 26 '18 at 10:05

Chris B

5,311
11
45
57

answered Jan 09 '16 at 13:22

maozx

319
4
5

3

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: https://blogs.msdn.microsoft.com/dataaccesstechnologies/2016/05/07/connection-timeout-issue-with-net-framework-4-6-1-transparentnetworkipresolution/ – Jorriss Aug 11 '16 at 15:40
Thank you. Hours of research on this problem. I need to favorite this response. The comment by @Jorriss was very helpful understanding why. You might update your answer to have the correct keyword though. Jorriss has the correct reference. – TravisWhidden Sep 25 '18 at 20:42

score 6 · Answer 5 · answered Sep 20 '13 at 10:33

6

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.

enter image description here

answered Sep 20 '13 at 10:33

Pomster

14,567
55
128
204

score 4 · Answer 6 · edited Jun 04 '14 at 15:37

4

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

edited Jun 04 '14 at 15:37

Matt

74,352
26
153
180

answered Jan 14 '14 at 06:52

smwikipedia

61,609
92
309
482

1

I think this can't be a correct answer because the question contains "only sometimes". A blocked port wont address the question regarding this. – Magier Feb 24 '16 at 11:30

Chris Catignani · Answer 7 · 2021-07-29T18:52:50.937

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

I will upvote this a million times over if i can. Thanks!!! – GidiBloke Aug 26 '21 at 17:33 — GidiBloke, Aug 26 '21 at 17:33

user270576 · Answer 8 · 2017-08-17T14:36:25.980

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.

score 2 · Answer 9 · answered Feb 19 '16 at 02:32

2

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

answered Feb 19 '16 at 02:32

Kym NT

670
9
28

score 2 · Answer 10 · answered Feb 28 '19 at 14:00

2

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.

answered Feb 28 '19 at 14:00

chainstair

681
8
18

Uriil · Answer 11 · 2019-04-01T15:18:32.293

2

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

edited Apr 01 '19 at 15:18

answered Apr 01 '19 at 15:12

Uriil

11,948
11
47
68

score 2 · Answer 12 · edited Dec 01 '22 at 07:06

I had the same issue, these rows:

sqlConnectionStringBuilder.MultiSubnetFailover = true;
sqlConnectionStringBuilder.TrustServerCertificate = true;

solved it.

Edit:

This was the fix for me as well when the SQL Servers are setup in a cluster across multiple Subnets. This is an easy option to test rather than convincing your DBA to open up the Server because you can simply change the connection string:

Persist Security Info=True;User ID=TheUser;Password=thePassword;Initial Catalog=DBName;Data Source=ServerName,1433;TrustServerCertificate=True; MultiSubnetFailover=True <- this last arg fixed it!

score 1 · Answer 13 · answered Apr 26 '17 at 08:10

1

In my case above all options were already there.

Solved it by increasing Connection Time-out = 30.

answered Apr 26 '17 at 08:10

Jignesh

59
1
10

score 1 · Answer 14 · answered Jul 06 '17 at 21:22

1

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

answered Jul 06 '17 at 21:22

user3424480

362
2
6
19

score 1 · Answer 15 · answered Apr 25 '19 at 02:27

1

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.

answered Apr 25 '19 at 02:27

Ricardo Fontana

4,583
1
21
32

score 1 · Answer 16 · answered Apr 30 '19 at 11:34

1

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

answered Apr 30 '19 at 11:34

Robert Benyi

1,623
1
14
10

score 1 · Answer 17 · answered Oct 25 '19 at 16:12

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.

score 1 · Answer 18 · answered Jul 06 '20 at 19:56

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.

score 0 · Answer 19 · answered May 04 '16 at 20:12

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.

score 0 · Answer 20 · answered Nov 29 '16 at 03:21

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.

Mohamed · Answer 21 · 2020-06-28T02:10:50.947

0

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

edited Jun 28 '20 at 02:10

answered Jan 30 '17 at 20:43

Mohamed

806
13
30

Which of these cases have to do with intermittent errors? – RonJohn Apr 06 '18 at 20:20
Please edit to disclose affiliation, [it's required](https://stackoverflow.com/help/promotion). Thanks. – Maximillian Laumeister May 31 '20 at 01:57

score 0 · Answer 22 · answered Sep 12 '18 at 22:14

0

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.

answered Sep 12 '18 at 22:14

Josué Zatarain

791
6
21

score 0 · Answer 23 · answered Dec 02 '19 at 11:39

0

I had the exact issue, tried several soultion didnt work , lastly restarted the system , it worked fine.

answered Dec 02 '19 at 11:39

NandhaGopalElangovan

209
1
5
14

score 0 · Answer 24 · answered Aug 03 '21 at 21:37

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.

Connection to SQL Server Works Sometimes

24 Answers24

Linked

Related