none
Default Connection Timeout

    Question

  • We've had terrible connection issues lately using integrated authentication to a SQL Server 2005 (with a Windows 2008 Server domain).  About 20% of connections get a timeout expired error.  I've tried every solution on the web I could find... the ONLY thing that has worked has been changing the connection timeout for the SqlClient from it's default of 15 seconds to 45 seconds.

    Now, that solution works great for custom code (well, anything over 15 seconds is really too long to wait to connect... but.. it works).  However, we have vended applications that use integrated authentication, both 3rd party and Microsoft.  The applications where I can change the timeout will start to work (with slow conneciton times though)... however, things like our source control software (SourceGear Vault) still fail connecting about 20% of the time because there is no setting to change the default connection timeout.

    My question:

    Is there a way to force a global change to the default connection timeout (so those apps that don't give you an option will fall into line with the default changed)?  I'm hoping there is a registry key or some other mechanism to do this and it's not hard coded into the provider.

    Wednesday, April 29, 2009 2:41 PM

Answers

  • Ouch, does not sound good.

    ADO.NET uses a default timeout from an internal constant in System.data.dll, there is not some system wide means of altering the default timeout.  That would certainly be a nice feature.

    Note the constant is System.Data.Common.ADP.DefaultConnectionTimeout you can see it using .NET reflector.

    Usually 3rd party apps have some means of altering the connection string this would be one route to solve this one.
    Sunday, May 03, 2009 11:52 PM

All replies

  • Hello,

    How are you setting the connection timeout?  Are you setting this in the connection string or are you setting this as part of the SqlConnection.ConnectionTimeout property?  You can try to change to use a global connection string for each of your generated connection object  (eg. "Data Source=(local);Database=AdventureWorks; Integrated Security=SSPI;Connection Timeout=30" )

    I hope that helps.
    Wednesday, April 29, 2009 5:03 PM
  • In my code, I set it via the connection string like in your example, and that works perfect.  My problem is, the vended software that uses SQL Server doesn't expose the connection timeout property, I can only assume it uses the default timeout (the root of the problem is being worked on by Microsoft currently, I've heard they've recreated it and it has to do with our 2008 domain controllers and that the SQL timeouts are a symptom of the domain issue).  Now, I'm trying to band-aid connections until the root of the problem is fixed.

    So I guess my question still is... is there a way to force the default timeout for database connections to SQL Server to be greater than 15 second.  For instance, SQL Server Management Studio exposes the Connection Timeout property which can be toggled... other 3rd party software doesn't expose that and I assume they're using the default connectin timeout.. therefore I'd like to make a global change that increases that timeout...  is there a way to do that?
    Wednesday, April 29, 2009 5:33 PM
  • Even if you find some way to do it globally, it would not solve the problem. You are trying to "hide" the problem setting connection timeout value for you connection. I would suggest to troubleshoot network and/or server to see what is the root of this problem. As you mentined in your post it should not take so much time to establish connection and most like this is not the issue with the application. I know this is not easy to do, but think from other side - what if tomorrow network is so bad that you will be forsed to set connection timeout to 120 seconds? Would users be happy with their experience?
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, April 30, 2009 9:48 AM
  • You are correct, I am trying to hide the problem because apparantly the issue is being resolved (which I have no control over).  I have no options _but_ to try to hide the problem until it's fixed.  That's not ideal, I realize that, but it's reality.  Here's why:

    1.) The organization I work for has a ticket open with Microsoft on this issue (we're fairly large, there's one group working with the ticket but there are many groups having this problem... I'm not directly involved in the support ticket).  I have been told though that Microsoft has recreated the problem and that it's an issue with the 2008 domain controllers (which makes sense, because these issues cropped up when our DC went from 2003 to 2008... I believe there are 8 controllers all together and they staggered putting them online.. the more they put online, the more prevelent this problem became).  So, at this point, since MS apparantly has acknowledged and found the issue I can only assume there is going to be a hotfix or it will come out in a patch/service pack.  Until then though, I have run out of things to try (we've looked at connection pooling, proper disposal of connections, number of connections open/closing on client/server, network traffic, protocols used, name resolution, router acl's, firewalls, WireShark captures, etc). 

    2.) I have NO control over the domain, there's very little I can do to "fix" a problem on the DC.  Our network team has monitored the routers, individual data jacks and found no issues with the network.  Since I've been told the problem has been found and Microsoft is working on it I'm sitting in sit and wait mode... and the only option I see is to attempt to mask the problem while it's being worked on.  Obviously, I wouldn't do this if I didn't have to. 

    3.) Further, these problems don't occur when I use SQL authentication and bypass integrated authentication all together (so, I can completely isolate the problem with this in _some_ cases).  The problem with that is we have vended software where that is not an option.  I still have to point out, I'm waiting on fixes I'm told are coming that are out of my control.  That's why I'm looking for a setting where I can change the connection timeout default.
    • Edited by bpell Thursday, April 30, 2009 8:48 PM
    Thursday, April 30, 2009 8:41 PM
  • Ouch, does not sound good.

    ADO.NET uses a default timeout from an internal constant in System.data.dll, there is not some system wide means of altering the default timeout.  That would certainly be a nice feature.

    Note the constant is System.Data.Common.ADP.DefaultConnectionTimeout you can see it using .NET reflector.

    Usually 3rd party apps have some means of altering the connection string this would be one route to solve this one.
    Sunday, May 03, 2009 11:52 PM
  • Ouch, does not sound good.

    ADO.NET uses a default timeout from an internal constant in System.data.dll, there is not some system wide means of altering the default timeout.  That would certainly be a nice feature.

    Note the constant is System.Data.Common.ADP.DefaultConnectionTimeout you can see it using .NET reflector.

    Usually 3rd party apps have some means of altering the connection string this would be one route to solve this one.

    Thanks for the response, your info is helpful (I was crossing my fingers that it wasn't a constant :).  I just went out and got a copy of reflector also, good advice.  Unfortunately, we have 2 3rd party software packages that don't expose any means of changing the connection timeout at this point.  We've passed along a request to allow that to be changed as a feature request.

    It was worth a shot!  Again, thanks for your response.
    Monday, May 04, 2009 6:16 PM
  • Back in the old days when I worked with Java you could decompile the jar file and change contants and repack the jar file and go about your business but in .NET world this is not as easy.
    Monday, May 04, 2009 6:26 PM