Article ID: 2879704 - View products that this article applies to.
When you try to connect to Microsoft SQL Server 2012 through an AlwaysOn availability group listener from a client application that uses the Microsoft .NET Framework Data Provider for SQL Server (System.Data.SqlClient), you may receive the following time-out error message:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
You receive this message when the following conditions are true:
Note The time-out error typically occurs within several seconds of the connection attempt from the application. Frequently, the time within which the error occurs is less than 5 seconds. If you increase the Connection Timeout property from the default value of 15 seconds to 20 or 30 seconds, the error still occurs. However, if you increase the Connection Timeout property to a much larger value such as 150 or 200 seconds, the connection usually succeeds.
The issue occurs because of an issue in the .NET Framework Data Provider for SQL Server.
When you use the parameter multisubnetfailover=yes or multisubnetfailover=true, the provider retries the TCP connections. To implement the retry algorithm, the provider divides the actual Connection Timeout value into small chunks or slices in order to speed up the number of retries. For example, the following are the waiting times when the value of Connection Timeout is set to its default of 15 seconds:
However, if a response is slow and the connection is not completely established before the waiting time expires, the data provider incorrectly sets the connection to a doomed state. Additionally, the provider incorrectly throws a time-out error after the expiration of the waiting time (time slice) instead of the actual or full Connection Timeout value.
Note The slow response in establishing a connection may in this case be triggered either by the server or by network latency.
To resolve this issue, install one of the following updates for the .NET Framework 4.5:
Note To apply the hotfix, you must have the .NET Framework 4.5 installed.
To work around this issue, use one of the following methods.
Method 1 (recommended)Set the Connection Timeout or Connect Timeout property to a large value such as 200. When you do this, the data provider sets the waiting time (time slice) for the first connection attempt to 200 * 0.08 ~ 16 seconds.
Method 2If you use a SQL Server 2012 AlwaysOn availability group listener in a single subnet scenario, you can either remove the parameter multisubnetfailover=true from the connection string or use the parameter multisubnetfailover=false or multisubnetfailover=no.
The following example displays a network capture
(http://www.microsoft.com/downloads/en/details.aspx?FamilyID=983b941d-06cb-4658-b7f6-3088333d062f&displaylang=en)between the application server and SQL Server at the time that the issue occurred in a test scenario:
In this example, in approximately 3.5 seconds, the client sends an ACK-FIN flag in order to close the connection prematurely. The client does this immediately after the pre-login handshake is complete (Frame 50924 AF packet).
For more information about other connection time-out errors in the SQL Server AlwaysOn availability group listener, click the following article numbers to view the articles in the Microsoft Knowledge Base:
(https://support.microsoft.com/kb/2855417/ )Connection times out when you use AlwaysOn availability group listener with MultiSubnetFailover parameter
(https://support.microsoft.com/kb/2792139/ )Time-out error and you cannot connect to a SQL Server 2012 AlwaysOn availability group listener in a multi-subnet environment
Article ID: 2879704 - Last Review: September 4, 2013 - Revision: 1.0