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.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Copyright (c) Microsoft Corporation. All rights reserved.
' THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE
' RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE
' USER. USE AND REDISTRIBUTION OF THIS CODE, WITH OR WITHOUT MODIFICATION, IS
' HEREBY PERMITTED.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This script adds a KeepAlive value to a given protocol definition.
' The value is in seconds.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub AddKA2Protocol()

    ' Create the root object.
    Dim root  ' The FPCLib.FPC root object
    Set root = CreateObject("FPC.Root")

    'Declare the other objects needed.
    Dim array       ' An FPCArray object
    Dim RuleElements  ' an FPCRuleElements objects.
    Dim ProtocolDefinitions ' an FPCProtocolDefinitions collection.
    Dim ProtocolDefinition  ' an FPCProtocolDefinition object.
    Dim VendorSets  ' An FPCVendorParametersSets collection
    Dim VendorSet   ' An FPCVendorParametersSet object

    ' Get references to the array object
    ' and the protocols collection.
    Set array = root.GetContainingArray

    On Error Resume Next
    Set RuleElements = array.RuleElements
    CheckError

    Set ProtocolDefinitions = RuleElements.ProtocolDefinitions
    CheckError

    Wscript.Echo "Number of protocols- " & ProtocolDefinitions.Count

    Set ProtocolDefinition = ProtocolDefinitions.Item("Microsoft SQL (TCP)")

    Set VendorSets = ProtocolDefinition.VendorParametersSets
    Set VendorSet = VendorSets.Item( "{80ad2d9c-725e-4fcd-a1d4-32c8042c774f}" )

    If Err.Number <> 0 Then
        Err.Clear

        ' Add the item
        Set VendorSet = VendorSets.Add( "{80ad2d9c-725e-4fcd-a1d4-32c8042c774f}")
        CheckError
        WScript.Echo "New VendorSet added... " & VendorSet.Name

    Else
        WScript.Echo "Existing VendorSet found..."
    End If

    VendorSet.Value("KeepAliveTimeout") = 30 ' value in seconds.

    VendorSets.Save false, true

    WScript.Echo "Done..."

End Sub

Sub CheckError()

    If Err.Number <> 0 Then
        WScript.Echo "An error occurred: 0x" & Hex(Err.Number) & " " & Err.Description
        Err.Clear
    End If

End Sub

AddKA2Protocol

If it does go all wrong you can try this script:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Copyright (c) Microsoft Corporation. All rights reserved.
' THIS CODE IS MADE AVAILABLE AS IS, WITHOUT WARRANTY OF ANY KIND. THE ENTIRE
' RISK OF THE USE OR THE RESULTS FROM THE USE OF THIS CODE REMAINS WITH THE
' USER. USE AND REDISTRIBUTION OF THIS CODE, WITH OR WITHOUT MODIFICATION, IS
' HEREBY PERMITTED.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This script Remove a KeepAlive value from a given protocol definition.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub RemoveKAFromProtocol()

    ' Create the root object.
    Dim root  ' The FPCLib.FPC root object
    Set root = CreateObject("FPC.Root")

    'Declare the other objects needed.
    Dim array       ' An FPCArray object
    Dim RuleElements  ' an FPCRuleElements objects.
    Dim ProtocolDefinitions ' an FPCProtocolDefinitions collection.
    Dim ProtocolDefinition  ' an FPCProtocolDefinition object.
    Dim VendorSets  ' An FPCVendorParametersSets collection
    Dim VendorSet   ' An FPCVendorParametersSet object

    ' Get references to the array object
    ' and the protocols collection.
    Set array = root.GetContainingArray

    On Error Resume Next
    Set RuleElements = array.RuleElements
    CheckError

    Set ProtocolDefinitions = RuleElements.ProtocolDefinitions
    CheckError

    Wscript.Echo "Number of protocols- " & ProtocolDefinitions.Count

    Set ProtocolDefinition = ProtocolDefinitions.Item("Microsoft SQL (TCP)")

    Set VendorSets = ProtocolDefinition.VendorParametersSets
    Set VendorSet = VendorSets.Item( "{80ad2d9c-725e-4fcd-a1d4-32c8042c774f}" )

    If Err.Number <> 0 Then
        CheckError
        WScript.Echo "VendorSet does not exit."

    Else
        WScript.Echo "Existing VendorSet found..."
        VendorSets.Remove( "{80ad2d9c-725e-4fcd-a1d4-32c8042c774f}" )

            If Err.Number <> 0 Then
                CheckError
                WScript.Echo "Cannot remove VendorSet"
            Else
                WScript.Echo "VendorSet removed..."
            End If
    End If

    VendorSets.Save false, true
    WScript.Echo "Done..."

End Sub

Sub CheckError()

    If Err.Number <> 0 Then
        WScript.Echo "An error occurred: 0x" & Hex(Err.Number) & " " & Err.Description
        Err.Clear
    End If

End Sub

RemoveKAFromProtocol