5

My company has just started using TMG 2010 as our Firewall / Web Proxy on a Windows 2008 R2 server. Since moving, all connections to SQL Server are closed after around 30 seconds.

For example:

  1. Run a query from SSMS which will run successfully
  2. Wait 30+ seconds

  3. Run the query again and it will fail with the following error

    A transport-level error has occurred when sending the request to the server.
(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.
  4. Run the query again and it will work correctly.

The same problem also occurs with queries that take over 30 seconds to run.

I'm assuming that the error in step 3 is because SSMS doesn't realise the existing connection has been closed.

Looking in the TMG logs I can see the SQL connections being closed with the following info:

Closed Connection
Log type: Firewall service
Status: Unspecified error
Rule: Internal Internet Access
Source: Internal (xx.xx.xx.xx)
Destination: External (xx.xx.xx.xx:1433)
Protocol: Microsoft SQL (TCP)
The result code is: 0x80004005

For background info I am trying to connect to SQL Azure. However, I have connected my laptop directly to the switch (bypassing TMG) and I can connect to the same SQL Azure instance without any problem. Based on this I don't believe the problem is with SQL Azure or anything upstream from TMG.

I’ve found some articles blaming TCP Chimney settings but this seems to apply to Windows Server 2003 and in any case I tried disabling it with no success.

Any help with this would be greatly appreciated.

Edit: It turns out that TMG was way out of date, I've upgraded it to SP2 but the problem still persists.

Edit: I have found a maximum connection timeout under the web proxy settings, which is currently set to 1800 seconds. I'm not even sure if the web proxy is relevant to sql connections over 1433 but as I'm running out of ideas, I disabled the proxy entirely. Unfortunately this didn't help.

Is there another location for idle tcp timeouts that I'm missing?

Dan Rowlands
  • 161
  • 5
  • Some firewalls close connections after a while if no data was transferred. I am sure there is a setting for that. – usr Feb 20 '12 at 20:22
  • Thanks for the tip, do you have any idea where that setting might be? I will update the question with what I have found so far. – Dan Rowlands Feb 20 '12 at 23:11
  • Unfortunately, I don't know anything at all about "Threat Management Gateway". I only understand that it is a firewall/filtering kind of product. That is why I didn't dare to write an answer ;-) – usr Feb 20 '12 at 23:13
  • This may be of some help http://www.techninja.com.au/?p=85 – aduljr Mar 01 '12 at 02:59
  • Thanks for the suggestion aduljr, I've looked at the flood detection settings before but unfortunately it didn't help. – Dan Rowlands Mar 09 '12 at 22:10

1 Answers1

1

I finally managed to solve the issue with the help of Microsoft support. It appears that while the TMG server was receiving keep-alive packets from SSMS, TMG wasn't forwarding those packets onto SQL Azure.

I have written a blog post about the problem in full with the vb scripts that Microsoft gave me to run.

http://www.danrowlands.co.uk/2012/03/sql-connections-timing-out-with-tmg.html

Dan Rowlands
  • 161
  • 5