Reputation: 150108

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?

Upvotes: 132

Answers (24)

micha redler

Reputation: 67

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!

Upvotes: 3

Gratzy

Reputation: 2898

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.

Upvotes: 0

Chris Catignani

Reputation: 5306

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

Upvotes: 4

Renzo Ciot

Reputation: 3846

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

Upvotes: 20

Barry

Reputation: 372

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.

Upvotes: 1

Mohamed

Reputation: 822

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

Upvotes: 0

NandhaGopalElangovan

Reputation: 229

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

Upvotes: 0

muhammad tayyab

Reputation: 810

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.

Upvotes: 1

Robert Benyi

Reputation: 1747

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

Upvotes: 1

Ricardo Fontana

Reputation: 4813

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.

Upvotes: 1

Uriil

Reputation: 12618

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

Upvotes: 2

chainstair

Reputation: 817

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.

Upvotes: 2

maozx

Reputation: 339

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.

Upvotes: 14

Josué Zatarain

Reputation: 868

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.

Upvotes: 0

Shaun Keon

Reputation: 745

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!

Upvotes: 48

user270576

Reputation: 997

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.

Upvotes: 3

user3424480

Reputation: 357

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

Upvotes: 1

Jignesh

Reputation: 69

In my case above all options were already there.

Solved it by increasing Connection Time-out = 30.

Upvotes: 2

John Livermore

Reputation: 31313

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.

Upvotes: 0

Chuck Herrington

Reputation: 155

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.

Upvotes: 0

Kym NT

Reputation: 790

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

Upvotes: 2

Eric J.

Reputation: 150108

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: