Entity Framework throw Exception: The connection could have timed out while waiting for server to complete the login process and respond

Question

Application Framework: .NET Framework 4.7.2
Application Type: Windows Application
ORM: Entity Framework 6.4.0
Database: Microsoft SQL Server 2016

The application will process multiple tasks concurrently, sometimes the application throws an exception when it tries to connect the database. But it is normal for another thread to connect the database at the same time. The thread that throws the exception waits for a few seconds, and then it is normal to connect the database again.

SQL Server and the application are on different servers, and application through server name connect database. As follows:

  <connectionStrings>  
    <add name="Test" connectionString="Data Source=sqlservertest;Initial Catalog=TESTDB;Integrated Security=True;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />       
  </connectionStrings>  

----------

Exception:

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. --> System.Data.SqlClient.SqlException: Connection Timeout Expired. The timeout period elapsed during the post-login phase. The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=37; handshake=19; [Login] initialization=0; authentication=5; [Post-Login] complete=14737; --> System.ComponentModel.Win32Exception: The wait operation timed out
--- End of inner exception stack trace —
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
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, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource1 retry) at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.<>c.<Open>b__13_0(DbConnection t, DbConnectionInterceptionContext c)
at System.Data.Entity.Infrastructure.Interception.InternalDispatcher1.Dispatch[TTarget,TInterceptionContext](TTarget target, Action2 operation, TInterceptionContext interceptionContext, Action3 executing, Action3 executed)
at System.Data.Entity.Infrastructure.Interception.DbConnectionDispatcher.Open(DbConnection connection, DbInterceptionContext interceptionContext)
at System.Data.Entity.Core.EntityClient.EntityConnection.<Open>b__55_0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<>c_DisplayClass2_0.<Execute>b_0()
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.ExecuteTResult
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation)
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
--- End of inner exception stack trace —
at System.Data.Entity.Core.EntityClient.EntityConnection.Open()
at System.Data.Entity.Core.Objects.ObjectContext.EnsureConnection(Boolean shouldMonitorTransactions)
at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransactionT
at System.Data.Entity.Core.Objects.ObjectQuery1.<>c_DisplayClass41_0.<GetResults>b_0() at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func1 operation)
at System.Data.Entity.Core.Objects.ObjectQuery1.GetResults(Nullable1 forMergeOption)
at System.Data.Entity.Core.Objects.ObjectQuery1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__31_0() at System.Data.Entity.Internal.LazyEnumerator1.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToListTSource

Answer 1

Just a thought about your earlier post/comments above...

I am a little concerned about the combination TDS, NamedPipes, and those messages about TCP ACK and TCP FIN.

Is TCP protocol enabled on your SQL Server?
Is the local firewall open for those SQL Server ports?
Is SQL Server running on a fixed or dynamic port?
Is there a firewall between those two servers which might (temporarily) block that traffic?
Is SQL browser service running (correctly)?

As the application and SQL are running on two different servers, the whole communication is based on TCP. Maybe try to disable NamedPipes (at least to identify the problem)

Answer 2

Since you are getting the error intermittently, it is likely a network error of some kind or in rare cases the server can be too busy to respond in time. The error message is a generic error and does not help diagnose the actual issue in anyway.

You would first need to look at the performance counters for the server during the time the error occurred. If you do not find anything there, I would suggest you start looking at your network under load.