EDIT : oddly enough, I set the breakpoint in the decompiled code and set the timeout to 1 using → VALID - the server name, I let my breakpoint sit there and then continue, and this threw an exception with the timeout as expected . Therefore, it seems that ConnectTimeout is applied ONLY when it is able to resolve the server and is waiting for communication. This MUST NOT affect the server’s connection decision. I think that the time spent is the resolution of the server, not the actual action of the “connection”. At least this is my current hypothesis.
I used a reflector to see what happens under the covers. Maybe someone from Microsoft can help us here because I also found that ConnectTimeout does not seem to affect the original connection.
In any case, to establish a connection, the following methods are called: in this sequence, I think:
internal DbConnectionInternal CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) { DbConnectionPoolGroupProviderInfo providerInfo = pool.PoolGroup.ProviderInfo; DbConnectionInternal internal2 = this.CreateConnection(options, providerInfo, pool, owningConnection); if (internal2 != null) { this.PerformanceCounters.HardConnectsPerSecond.Increment(); internal2.MakePooledConnection(pool); } Bid.Trace("<prov.DbConnectionFactory.CreatePooledConnection|RES|CPOOL> %d#, Pooled database connection created.\n", this.ObjectID); return internal2; }
And then:
protected override DbConnectionInternal CreateConnection(DbConnectionOptions options, object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) { string instanceName; SqlConnectionString str = (SqlConnectionString) options; if (str.ContextConnection) { return this.GetContextConnection(str, poolGroupProviderInfo, owningConnection); } bool redirectedUserInstance = false; DbConnectionPoolIdentity current = null; if (str.IntegratedSecurity) { if (pool != null) { current = pool.Identity; } else { current = DbConnectionPoolIdentity.GetCurrent(); } } if (!str.UserInstance) { goto Label_00F1; } redirectedUserInstance = true; if ((pool == null) || ((pool != null) && (pool.Count <= 0))) { using (SqlInternalConnectionTds tds = null) { SqlConnectionString connectionOptions = new SqlConnectionString(str, str.DataSource, true, false); tds = new SqlInternalConnectionTds(current, connectionOptions, null, "", null, false); instanceName = tds.InstanceName; if (!instanceName.StartsWith(@"\\.\", StringComparison.Ordinal)) { throw SQL.NonLocalSSEInstance(); } if (pool != null) { SqlConnectionPoolProviderInfo info2 = (SqlConnectionPoolProviderInfo) pool.ProviderInfo; info2.InstanceName = instanceName; } goto Label_00DB; } } SqlConnectionPoolProviderInfo providerInfo = (SqlConnectionPoolProviderInfo) pool.ProviderInfo; instanceName = providerInfo.InstanceName; Label_00DB: str = new SqlConnectionString(str, instanceName, false, null); poolGroupProviderInfo = null; Label_00F1: return new SqlInternalConnectionTds(current, str, poolGroupProviderInfo, "", (SqlConnection) owningConnection, redirectedUserInstance); }
and then:
internal SqlInternalConnectionTds(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, object providerInfo, string newPassword, SqlConnection owningObject, bool redirectedUserInstance) : base(connectionOptions) { this._instanceName = string.Empty; if (connectionOptions.UserInstance && InOutOfProcHelper.InProc) { throw SQL.UserInstanceNotAvailableInProc(); } this._identity = identity; this._poolGroupProviderInfo = (SqlConnectionPoolGroupProviderInfo) providerInfo; this._fResetConnection = connectionOptions.ConnectionReset; if (this._fResetConnection) { this._originalDatabase = connectionOptions.InitialCatalog; this._originalLanguage = connectionOptions.CurrentLanguage; } RuntimeHelpers.PrepareConstrainedRegions(); try { TimeoutTimer timeout = TimeoutTimer.StartSecondsTimeout(connectionOptions.ConnectTimeout); this.OpenLoginEnlist(owningObject, timeout, connectionOptions, newPassword, redirectedUserInstance); } catch (OutOfMemoryException) { base.DoomThisConnection(); throw; } catch (StackOverflowException) { base.DoomThisConnection(); throw; } catch (ThreadAbortException) { base.DoomThisConnection(); throw; } if (Bid.AdvancedOn) { Bid.Trace("<sc.SqlInternalConnectionTds.ctor|ADV> %d#, constructed new TDS internal connection\n", base.ObjectID); } }
and then by default (without fault tolerance partner):
private void LoginNoFailover(ServerInfo serverInfo, string newPassword, bool redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) { if (Bid.AdvancedOn) { Bid.Trace("<sc.SqlInternalConnectionTds.LoginNoFailover|ADV> %d#, host=%ls\n", base.ObjectID, serverInfo.UserServerName); } int num = 100; this.ResolveExtendedServerName(serverInfo, !redirectedUserInstance, owningObject); while (true) { if (this._parser != null) { this._parser.Disconnect(); } this._parser = new TdsParser(base.ConnectionOptions.MARS, base.ConnectionOptions.Asynchronous); try { this.AttemptOneLogin(serverInfo, newPassword, true, timeout, owningObject); break; } catch (SqlException exception) { if (((this._parser == null) || (this._parser.State != TdsParserState.Closed)) || (this.IsDoNotRetryConnectError(exception.Number) || timeout.IsExpired)) { throw; } if (timeout.MillisecondsRemaining <= num) { throw; } } if (this.ServerProvidedFailOverPartner != null) { this.LoginWithFailover(true, serverInfo, this.ServerProvidedFailOverPartner, newPassword, redirectedUserInstance, owningObject, connectionOptions, timeout); return; } if (Bid.AdvancedOn) { Bid.Trace("<sc.SqlInternalConnectionTds.LoginNoFailover|ADV> %d#, sleeping %d{milisec}\n", base.ObjectID, num); } Thread.Sleep(num); num = (num < 500) ? (num * 2) : 0x3e8; } if (this.PoolGroupProviderInfo != null) { this.PoolGroupProviderInfo.FailoverCheck(this, false, connectionOptions, this.ServerProvidedFailOverPartner); } base.CurrentDataSource = serverInfo.UserServerName; }
and then:
internal void Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, bool ignoreSniOpenTimeout, long timerExpire, bool encrypt, bool trustServerCert, bool integratedSecurity) { if (this._state == TdsParserState.Closed) { this._connHandler = connHandler; if (SNILoadHandle.SingletonInstance.SNIStatus != 0) { this.Errors.Add(this.ProcessSNIError(this._physicalStateObj)); this._physicalStateObj.Dispose(); this.ThrowExceptionAndWarning(); } if (integratedSecurity) { this.LoadSSPILibrary(); this._sniServerUserName = new byte[s_maxSSPILength]; Bid.Trace("<sc.TdsParser.Connect|SEC> SSPI authentication\n"); } else { Bid.Trace("<sc.TdsParser.Connect|SEC> SQL authentication\n"); } byte[] instanceName = null; this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, false, this._fAsync); if (this._physicalStateObj.Status != 0) { this.Errors.Add(this.ProcessSNIError(this._physicalStateObj)); this._physicalStateObj.Dispose(); Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n"); this.ThrowExceptionAndWarning(); } this._server = serverInfo.ResolvedServerName; if (connHandler.PoolGroupProviderInfo != null) { connHandler.PoolGroupProviderInfo.AliasCheck(serverInfo.ResolvedServerName); } this._state = TdsParserState.OpenNotLoggedIn; this._physicalStateObj.SniContext = SniContext.Snix_PreLoginBeforeSuccessfullWrite; this._physicalStateObj.TimeoutTime = timerExpire; bool marsCapable = false; this.SendPreLoginHandshake(instanceName, encrypt); this._physicalStateObj.SniContext = SniContext.Snix_PreLogin; switch (this.ConsumePreLoginHandshake(encrypt, trustServerCert, out marsCapable)) { case PreLoginHandshakeStatus.SphinxFailure: this._fMARS = false; this._physicalStateObj._sniPacket = null; this._physicalStateObj.SniContext = SniContext.Snix_Connect; this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, false, this._fAsync); if (this._physicalStateObj.Status != 0) { this.Errors.Add(this.ProcessSNIError(this._physicalStateObj)); Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n"); this.ThrowExceptionAndWarning(); } break; case PreLoginHandshakeStatus.InstanceFailure: this._physicalStateObj.Dispose(); this._physicalStateObj.SniContext = SniContext.Snix_Connect; this._physicalStateObj.CreatePhysicalSNIHandle(serverInfo.ExtendedServerName, ignoreSniOpenTimeout, timerExpire, out instanceName, integratedSecurity, this._sniServerUserName, true, this._fAsync); if (this._physicalStateObj.Status != 0) { this.Errors.Add(this.ProcessSNIError(this._physicalStateObj)); Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n"); this.ThrowExceptionAndWarning(); } this.SendPreLoginHandshake(instanceName, encrypt); if (this.ConsumePreLoginHandshake(encrypt, trustServerCert, out marsCapable) == PreLoginHandshakeStatus.InstanceFailure) { Bid.Trace("<sc.TdsParser.Connect|ERR|SEC> Login failure\n"); throw SQL.InstanceFailure(); } break; } if (this._fMARS && marsCapable) { this._sessionPool = new TdsParserSessionPool(this); } else { this._fMARS = false; } } }
I'm not sure how all of these parts come together, but infinTimeout seems to be true.
Not sure if this will help anyone, but I thought it was worth digging through
