ado.net - 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=THESERVERTheInstance;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?

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