Connection Timeouts When Using Multisubnetfailover=True

sql-server alwayson
vesuvious picture vesuvious · May 1, 2014 · Viewed 8k times · Source

Recently I found that connections were failing from one of our webservers to an MSSQL AlwaysOn Listener. The listener has two IP addresses because it spans subnets so we are specifying Multisubnetfailover=true in our connection string.

When attempting to make a connection to the listener I get the following error:

System.Data.SqlClient.SqlException (0x80131904): 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=20991; handshake=0;  ---> System.ComponentModel.Win32Exception     
(0x80004005): The wait operation timed out at     
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32     
waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions     
userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 
retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, 
TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, 
DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, 
DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, 
DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at IRNXMLGateway.Controllers.IRNXMLGatewayController.GenericCall(String methodCall) in 
CODELINE:line 376
ClientConnectionId:92074b33-176a-4006-b7c7-892e01a3eea7

I have also tried to connect using SSMS and ran into the same timeout issue.

I am able to successfully make connections by:

  • Connecting directly to the host without the listener
  • Connecting directly to the current listener IP address
  • Increasing the connection timeout to 200 seconds
  • Removing the MultiSubnetFailover option in the connection string

I am not experiencing this issue when attempting to connect from other servers. There are no errors in either the SQL or windows event logs to help determine the cause of the timeouts. Network trace shows the proper connection handshake with the current listener IP address. Neither servers have firewall or antivirus enabled. Webserver is running server 2008 web edition, SQL server is running windows server 2012 and sql server 2012.

Answer

Russ960 picture Russ960 · May 1, 2014

Is the following true: You have an active Transport Driver Interface (TDI) filter driver that is installed on the client application workstation.

If so there is a KB article at MS on this issue.
http://support.microsoft.com/kb/2870437

Related questions

How to programmatically add a database to an existing Always On Availability Group (AOAG)?

I'd like to programmatically (T-SQL scripts) create a new database, and then add it to an existing Always On Availability Group (AOAG). So far, I'm ok with creating the database, creating a full back-up (which is a pre-requisite for adding …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More