How to make SqlConnection timeout more quickly

I am using an SQL connection string with SqlClient.SqlConnection and specifying Connection Timeout=5 in the string, but it still waits 30 seconds before returning failure. How do I make it give up and return faster? I'm on a fast local network and don't want to wait 30 seconds. The servers that are not turned on take 30 seconds to fail. 开发者_JAVA技巧This is just a quick utility program that's going to always run just on this local network.

Edit: Sorry if I was unclear. I want the SqlConnection.Open to fail more quickly. Hopefully that could be deduced from the fact that the servers I want to fail more quickly are turned off.

Edit: It seems that the setting only fails sometimes. Like it knows the IP address of the server, and is using TCP/IP to talk to it (not local) but can't contact SQL Server at that address? I'm not sure what the pattern is, but I don't see the problem when connecting locally with SQL Server stopped, and I don't see it when attempting to connect to a non-existent server. I have seen it when attempting to contact a server where the Windows 2008 firewall is blocking SQL Server, though.

It looks like all the cases that were causing long delays could be resolved much more quickly by attempting a direct socket connection like this:

foreach (string svrName in args)
{
   try
   {
      System.Net.Sockets.TcpClient tcp = new System.Net.Sockets.TcpClient(svrName, 1433);
      if (tcp.Connected)
         Console.WriteLine("Opened connection to {0}", svrName);
      else
         Console.WriteLine("{0} not connected", svrName);
      tcp.Close();
   }
   catch (Exception ex)
   {
      Console.WriteLine("Error connecting to {0}: {1}", svrName, ex.Message);
   }
}

I'm going to use this code to check if the server responds on the SQL Server port, and only attempt to open a connection if it does. I thought (based on others' experience) that there would be a 30 second delay even at this level, but I get a message that the machine "actively refused the connection" on these right away.

Edit: And if the machine doesn't exist, it tells me that right away too. No 30-second delays that I can find.

Edit: Machines that were on the network but are not turned off still take 30 seconds to fail I guess. The firewalled machines fail faster, though.

Edit: Here's the updated code. I feel like it's cleaner to close a socket than abort a thread:

static void TestConn(string server)
{
   try
   {
      using (System.Net.Sockets.TcpClient tcpSocket = new System.Net.Sockets.TcpClient())
      {
         IAsyncResult async = tcpSocket.BeginConnect(server, 1433, ConnectCallback, null);
         DateTime startTime = DateTime.Now;
         do
         {
            System.Threading.Thread.Sleep(500);
            if (async.IsCompleted) break;
         } while (DateTime.Now.Subtract(startTime).TotalSeconds < 5);
         if (async.IsCompleted)
         {
            tcpSocket.EndConnect(async);
            Console.WriteLine("Connection succeeded");
         }
         tcpSocket.Close();
         if (!async.IsCompleted)
         {
            Console.WriteLine("Server did not respond");
            return;
         }
      }
   }
   catch(System.Net.Sockets.SocketException ex)
   {
      Console.WriteLine(ex.Message);
   }
}

Update 2 I suggest rolling your own timeout. Something like this:

internal static class Program
{
    private static void Main(string[] args)
    {
        Console.WriteLine(SqlServerIsRunning("Server=foobar; Database=tempdb; Integrated Security=true", 5));
        Console.WriteLine(SqlServerIsRunning("Server=localhost; Database=tempdb; Integrated Security=true", 5));
    }

    private static bool SqlServerIsRunning(string baseConnectionString, int timeoutInSeconds)
    {
        bool result;

        using (SqlConnection sqlConnection = new SqlConnection(baseConnectionString + ";Connection Timeout=" + timeoutInSeconds))
        {
            Thread thread = new Thread(TryOpen);
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);
            thread.Start(new Tuple<SqlConnection, ManualResetEvent>(sqlConnection, manualResetEvent));
            result = manualResetEvent.WaitOne(timeoutInSeconds*1000);

            if (!result)
            {
                thread.Abort();
            }

            sqlConnection.Close();
        }

        return result;
    }

    private static void TryOpen(object input)
    {
        Tuple<SqlConnection, ManualResetEvent> parameters = (Tuple<SqlConnection, ManualResetEvent>)input;

        try
        {
            parameters.Item1.Open();
            parameters.Item1.Close();
            parameters.Item2.Set();
        }
        catch
        {
            // Eat any exception, we're not interested in it
        }
    }
}

Update 1

I've just tested this on my own computer using this code:

internal static class Program
{
    private static void Main(string[] args)
    {
        SqlConnection con = new SqlConnection("Server=localhost; Database=tempdb; Integrated Security=true;Connection Timeout=5");
        Console.WriteLine("Attempting to open connection with {0} second timeout, starting at {1}.", con.ConnectionTimeout, DateTime.Now.ToLongTimeString());

        try
        {
            con.Open();
            Console.WriteLine("Successfully opened connection at {0}.", DateTime.Now.ToLongTimeString());
        }
        catch (SqlException)
        {
            Console.WriteLine("SqlException raised at {0}.", DateTime.Now.ToLongTimeString());
        }
    }
}

and it obeys the Connection Timeout value in the connection string. This was with .NET 4 against SQL Server 2008 R2. Admittedly, it's a localhost connection which may give different results but it means I can't replicate the problem.

I can only suggest trying a similar chunk of code in your network environment and seeing if you continue to see long timeouts.

Old (incorrect) answer I incorrectly thought the ConnectionTimeout property was settable, but it isn't.

Try setting SqlConnection.ConnectionTimeout instead of using the connection string.

The Command Timeout and the Connection Timeout are two different things.

SqlConnection.ConnectionTimeout is "the time (in seconds) to wait for a connection to open. The default value is 15 seconds." Thats only used when you call SqlConnection.Open().

The SqlCommand.CommandTimeout does what you want to do.