"Timeout expired" error when you try to connect to SQL Server 2012 through an AlwaysOn availability group listener

Article ID: 2879704 - View products that this article applies to.
Expand all | Collapse all

On This Page

Symptoms

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.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
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:
  • Your availability group listener is in a single or multiple subnet.
  • The application uses the .NET Framework Data Provider for SQL Server that is installed together with one of the following:
    • The Microsoft .NET Framework 3.5 Service Pack 1 (SP1)
    • The Microsoft .NET Framework 4
    • The Microsoft .NET Framework 4.5
  • You specify the parameter multisubnetfailover=yes or multisubnetfailover=true in the SQL Server connection string.

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.

Cause

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:
  • Waiting time (time slice) for the first connection attempt is 15 * 0.08 ~ 1.2 seconds.
  • Waiting time (time slice) for the second connection attempt is 15 * 0.16 ~ 2.4 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.

Resolution

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.

Workaround

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 2

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


More information

The following example displays a network capture between the application server and SQL Server at the time that the issue occurred in a test scenario:

0.0000000 50435 AppServer SqlServer TCP TCP: [Bad CheckSum]Flags=......S., SrcPort=29536, DstPort=1433, PayloadLen=0, Seq=2555469547, Ack=0, Win=8192 ( Negotiating scale factor 0x8 ) = 8192 {TCP:3158, IPv4:3157}

0.0023100 50439 SqlServer AppServer TCP TCP:Flags=...A..S., SrcPort=1433, DstPort=29536, PayloadLen=0, Seq=1513779847, Ack=2555469548, Win=8192 ( Negotiated scale factor 0x8 ) = 2097152 {TCP:3158, IPv4:3157}

0.0000450 50440 AppServer SqlServer TCP TCP: [Bad CheckSum]Flags=...A...., SrcPort=29536, DstPort=1433, PayloadLen=0, Seq=2555469548, Ack=1513779848, Win=256 (scale factor 0x8) = 65536 {TCP:3158, IPv4:3157}

2.9903500 50884 AppServer SqlServer TDS TDS:Prelogin, Version = 7.300000(No version information available, using the default version), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=29536, DstPort=1433, PayloadLen=88, Seq=2555469548 - 2555469636, Ack=1513779848, Win=65536 {TDS:3197, TCP:3158, IPv4:3157}

0.0005170 50885 SqlServer AppServer TDS TDS:Response, Version = 7.300000(No version information available, using the default version), SPID = 0, PacketID = 1, Flags=...AP..., SrcPort=1433, DstPort=29536, PayloadLen=48, Seq=1513779848 - 1513779896, Ack=2555469636, Win=131328 {TDS:3197, TCP:3158, IPv4:3157}

0.2077540 50909 AppServer SqlServer TCP TCP: [Bad CheckSum]Flags=...A...., SrcPort=29536, DstPort=1433, PayloadLen=0, Seq=2555469636, Ack=1513779896, Win=256 (scale factor 0x8) = 65536 {TCP:3158, IPv4:3157}

0.1385570 50924 AppServer SqlServer TCP TCP: [Bad CheckSum]Flags=...A...F, SrcPort=29536, DstPort=1433, PayloadLen=0, Seq=2555469636, Ack=1513779896, Win=256 (scale factor 0x8) = 65536 {TCP:3158, IPv4:3157}

0.0003270 50925 SqlServer AppServer TCP TCP:Flags=...A...., SrcPort=1433, DstPort=29536, PayloadLen=0, Seq=1513779896, Ack=2555469637, Win=513 (scale factor 0x8) = 131328 {TCP:3158, IPv4:3157}

0.0006730 50926 SqlServer AppServer TCP TCP:Flags=...A...F, SrcPort=1433, DstPort=29536, PayloadLen=0, Seq=1513779896, Ack=2555469637, Win=513 (scale factor 0x8) = 131328 {TCP:3158, IPv4:3157}

0.0000150 50927 AppServer SqlServer TCP TCP: [Bad CheckSum]Flags=...A...., SrcPort=29536, DstPort=1433, PayloadLen=0, Seq=2555469637, Ack=1513779897, Win=256 (scale factor 0x8) = 65536 {TCP:3158, IPv4:3157}

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:
2855417 Connection times out when you use AlwaysOn availability group listener with MultiSubnetFailover parameter

2792139 Time-out error and you cannot connect to a SQL Server 2012 AlwaysOn availability group listener in a multi-subnet environment

Properties

Article ID: 2879704 - Last Review: September 4, 2013 - Revision: 1.0
Applies to
  • Microsoft SQL Server 2012 Developer
  • Microsoft SQL Server 2012 Enterprise
  • Microsoft SQL Server 2012 Standard
  • Microsoft SQL Server 2012 Web
  • SQL Server 2012 Enterprise Core
Keywords: 
kbsurveynew kbtshoot kbexpertiseadvanced KB2879704

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com