SQL Connections timing out with TMG 2010

We have been having issues with SQL Server connections timing out at work. I eventually found the solution with help from Microsoft (who were incredibly helpful) so I thought I would share the solution so no-one else feels my pain.

I'm not an infrastructure guy so this maybe obvious to other people more familiar to TMG!

The problem

My problem was that SQL Azure connections were being forcibly closed after about 30 seconds. This was when connecting to a SQL Azure instance hosted in the cloud from a workstation behind TMG 2010. Although I imagine this would probably have happened with a standard SQL instance as well.

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.

I'm assuming that the error in step 3 occurred because SSMS doesn't realise the existing connection has been closed and when we get to step 4, SSMS opens a new connection.

The TMG logs showed that the SQL connections were being closed with the following info:

Closed Connection
Log type: Firewall service
Status: Unspecified errorRule: 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

A few failed attempts

I read a few blog posts about TCP Chimney settings and flood protection configuration but neither of these options helped me.

The actual solution

Eventually Microsoft support solved the problem.

The solution was to tell TMG to forward keep alive packets. It appears that TMG doesn't forward these on by default and so while my workstation was sending a keep-alives to TMG,  TMG never forwarded them on to SQL Azure. This meant SQL was closing the connection while Management Studio thought is was still open.

To forward the keep-alives I needed to run a vb script which I've included below. Please don't blame me if anything goes wrong, you use it at your own risk. It's worth noting that there maybe a way to do this in the UI, I just don't know how.

The start of Fennel

Fennel will be a lesson planning web application written for teachers. The project serves two purposes: the first is to make lesson planning easier for teachers and the second is to give me a chance to play around with technologies and techniques that I haven’t had a chance of using yet.

I’m going to include my technology choices on a separate page so I can keep a log of my decisions as I move along. I’ll probably add this the BitBucket wiki for my mercurial repo. I’ll include links on the Projects Page of this blog.

The first step will be to develop a complete slice through the application so I can get the surrounding infrastructure working. So I have chosen to create some of the Settings functionality which includes changing your name, adding subjects and adding classes. A nice easy place to start with but it should get a lot of boiler plate code in-place.

This first slice should include:

• OpenID Login
• User creation
• Backbone framework
• Data access via a http web service
• Caching static data in local storage (both subjects and classes seem like good candidates)
• Persist into RavenDB

Let the coding and Ritz cracker eating commence.