Home / server / Questions / 361531
Accepted
Dan Rowlands
Asked: 2012-02-19 14:58:18 +0800 CST

Threat Management Gateway 2010 is killing SQL Connections

  • 772

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?

sql-server microsoft-ftmg-2010

1 Answers

  1. Best Answer

    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

    • 1