13

I'm connecting to MSSQL database through my ASP .NET application, but sometimes I got this error while opening connection.

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=3; handshake=14996;

To solve it temporarily I've to restart IIS. I'm using this code snippet to connect to MSSQL:

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            /* my commands here */

            connection.Close();
            connection.Dispose();
            SqlConnection.ClearPool(connection);
        }

I allowed the port 1433 in the inbound and outbound rules, but no changes. As I follow the instructions there:

but nothing changed.

Community
  • 1
  • 1
Ahmed Negm
  • 795
  • 1
  • 6
  • 28

2 Answers2

4

According MSDN Doc .

ClearPool clears the connection pool that is associated with the connection.If additional connections associated with connection are in use at the time of the call, they are marked appropriately and are discarded (instead of being returned to the pool) when Close is called on them.

you should use ClearPool method before connection.close

refer : https://msdn.microsoft.com/zh-tw/library/system.data.sqlclient.sqlconnection.clearpool(v=vs.110).aspx

And If you use Using syntax to manage Object you should't use connection.close method

because it will call when they run to end . just open your connection and execute your command 

 using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlConnection.ClearPool(connection);
        /* my commands here */
        SqlCommand cmd = new SqlCommand("your command",conn);
        cmd.ExecuteReader()


    }

refer example : https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

last ensure SQL Server Network Config is right

enter image description here

enter image description here

King Jk
  • 961
  • 11
  • 21
  • Was there ever a solution found for this? I am getting the similar error but from SSMS (Management Studios) when try to connect to a SQL server from a remote m/c. – Aamir Nov 28 '16 at 19:36
  • @Aamir did you checking your firewall setting ? if you from remote you should open 1433 (default) port for SQL Server – King Jk Nov 29 '16 at 04:20
  • @KingJk original question was about sporadic events of unavailability. Firewall would either work always or break always no? Also I have same issue. Once in a while long handshake in Management Studio. (Like once per week) – przemo_li Feb 28 '17 at 10:57
  • @przemo_li When this problem occur I always shutdown Firewall.To prevent Firewall affect handshake package sending. And you can trace SQL server log :) ref : https://msdn.microsoft.com/en-us/library/ms187109.aspx – King Jk Mar 02 '17 at 03:09
1

What did the trick for me is increasing the timeout on the connection string, since when connecting by vpn it took to long to establish the connection. You can do this by adding ;connection timeout = value

I got the same error when connecting an application tried to connect to sql server while I was on a vpn.

By default the timeout is set to 15 seconds.

Hope it helps!

StefanE
  • 93
  • 7