.NET Core SqlConnection ConnectTimout 15 less than not work?

[OdaShinsuke]

.NET Core SqlConnection ConnectTimout 15 less than not work?

Microsoft.Data.SqlConnection ConnectTimeout behaves differently between .NET Framework and .NET Core.

Environment:

• Windows 10
• SQL Server 2019 Developer Edition (service is stopped)
• .NET Core 5 / 3.1、.NET Framework 4.8
• Microsoft.Data.SqlClient 3.0.0

To reproduce

Prerequisites
Stop the SQL Server service.

.NET Core 5 or 3.1

using Microsoft.Data.SqlClient;
using System;
using System.Diagnostics;

namespace ConnectTimeoutNotWorkCore
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(typeof(Program).FullName);
            OpenError(1);
            OpenError(60);
            Console.ReadKey();
            /*
ConnectTimeoutNotWorkCore.Program
Start
Error Number:10061
16672
Done
Start
Error Number:10061
65981
Done
             */
        }
        static void OpenError(int connectTimeout)
        {
            var builder = new SqlConnectionStringBuilder()
            {
                DataSource = "localhost\\MSSQL2019",
                InitialCatalog = "master",
                IntegratedSecurity = true,
                ConnectTimeout = connectTimeout
            };
            var stopwatch = new Stopwatch();
            try
            {
                using (var conn = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("Start");
                    stopwatch.Start();
                    conn.Open();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error Number:{e.Number}");
            }
            finally
            {
                stopwatch.Stop();
                Console.WriteLine(stopwatch.ElapsedMilliseconds);
                Console.WriteLine("Done");
            }
        }
    }
}

.NET Framework 4.8

using Microsoft.Data.SqlClient;
using System;
using System.Diagnostics;

namespace ConnectTimeoutNotWorkFx48
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(typeof(Program).FullName);
            OpenError(1);
            OpenError(60);
            Console.ReadKey();
            /*
ConnectTimeoutNotWorkFx48.Program
Start
Error Number:258
1768
Done
Start
Error Number:258
60019
Done
             */
        }
        static void OpenError(int connectTimeout)
        {
            var builder = new SqlConnectionStringBuilder()
            {
                DataSource = "localhost\\MSSQL2019",
                InitialCatalog = "master",
                IntegratedSecurity = true,
                ConnectTimeout = connectTimeout
            };
            var stopwatch = new Stopwatch();
            try
            {
                using (var conn = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("Start");
                    stopwatch.Start();
                    conn.Open();
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine($"Error Number:{e.Number}");
            }
            finally
            {
                stopwatch.Stop();
                Console.WriteLine(stopwatch.ElapsedMilliseconds);
                Console.WriteLine("Done");
            }
        }
    }
}

In .NET Core, even if I set Connect Timeout = 1, it still seems to work with the default value of 15.

SqlException.Number is different.

• .NET Core : 10061
• .NET Framework : 258

Expected behavior

If I set Connect Timeout to less than 15 in .NET Core, I want it to work for less than 15 seconds.

[JRahnama]

@OdaShinsuke
In general, when you open a connection and kill the connection on the server side (by stopping SQL Server service as you mentioned or other ways), driver would not be aware of that till it does a transaction with the server. Server wont notify the driver that the connection is doomed.
The best way to check if time out is working is to open your connection wait for Connection time out (Task.Delay) and try to do a transaction.

instead of error number please try to write the message or print the stack please and share them with us as well if possible.

Thank you.

[v-chojas]

10061 = connection refused
258 = wait timed out

[OdaShinsuke]

Thanks.

1. SQL Server service stopped.
2. Run reproduce code
3. Connection open failed.

Stacktrace.

.NET Core 5

   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, SqlAuthenticationMethod authType)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at ConnectTimeoutNotWorkCore5.Program.OpenError(Int32 connectTimeout) in C:\Users\shinsuke-oda\source\repos\ConnectTimeoutNotWork\ConnectTimeoutNotWork\Program.cs:line 43

.NET Framework

   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover, Boolean isFirstTransparentAttempt, SqlAuthenticationMethod authType, String certificate, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at ConnectTimeoutNotWorkFx48.Program.OpenError(Int32 connectTimeout) (C:\Users\shinsuke-oda\source\repos\ConnectTimeoutNotWork\ConnectTimeoutNotWorkFx48\Program.cs):行 43

[JRahnama]

@OdaShinsuke the workaround for this issue is to add MultiSubnetFailover=true to your connection string.

[zhenlei520]

@OdaShinsuke此问题的解决方法是添加MultiSubnetFailover=true到您的连接字符串。

确实如此，目前使用 5.0.0版本的也是一样，加过配置后可以了