Why is ConnectTimeout Ignored In This Case?

Question

When running this code:

static void Main(string[] args)
{
    SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
    csb.DataSource = @"8.8.8.8"; // some inaccessible ip address
    csb.InitialCatalog = "Tempdb";
    csb.IntegratedSecurity = true;
    csb.ConnectTimeout = 1;
    DateTime start = DateTime.Now;
    try
    {
        new SqlConnection(csb.ToString()).Open();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
    finally
    {
        Console.Write(string.Format("{0} seconds", DateTime.Now.Subtract(start).TotalSeconds));
    }
}

I get this result:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
47.6097605 seconds

I expected the ConnectTimeout property to have an effect. So why is the ConnectTimeout property ignored in this case? (I'm also curious about what times other people see).

Update: I noticed that the following extra line shortens the time span to 26 seconds???

csb.FailoverPartner=@"9.9.9.9";

Answer 1

EDIT: As strange as this may seem, I put a break point deep in the decompiled code and set the timeout to 1 with a -->VALID<-- server name, I let my breakpoint sit there and then continued, and it gave the timeout expired exception as expected So it seems that the ConnectTimeout applies ONLY when it is able to resolve the server and waits for a connection. It DOES NOT impact resolving the server to connect to. I think the time being experienced is for server resolution and not to the actual act of "connecting". At least that is my current hypothesis.

I used reflector to see what was going on under the covers. Maybe someone from Microsoft can help us out here, because I have also found that ConnectTimeout seems to have no effect on the initial connection.

Anyway internally to establish the connection, the following methods get 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 Failover 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 am not sure how this all pieces together, but infiniteTimeout seems to be true.

Not sure if this helps any, but I figured it was worth digging through