locked
Problem with publishing of SQL 2008 R2 via Forefront TMG 2010 RRS feed

  • Question

  • Hello all,

    I have a very strange problem and will appreciate any idea how to debug the problem (or any idea what problem can be).

    I need to publish clustered SQL 2008 R2 server on two almost identically TMG 2010 servers.
    Both servers are online for couple of months and publishing a lot of stuff (e.g. Exchange, IIS, FTP etc).
    Until now we never observe any difference between behavior of both TMG servers.

    On both TMG servers we created idential rule to publish non-web server as specified in MS article
    "Configuring SQL Server Publishing" (http://207.46.16.252/en-us/library/cc441596.aspx).

    One of both servers works as expected - I can connect to the SQL server using the SQL Management Studio from the client computer via TMG.

    To my surprise, the second server doesn't work as expected and SQL connection fails. After a day of testing I confirmed that:
        - failing TMG server itself can telnet port 1433 on clustered SQL, but
        - an external client is not able telnet port 1433 on the failing TMG.

     Any idea how to solve such kind of problem?

    With best regards,

    Al

     

    Friday, July 16, 2010 10:24 AM

Answers

  • Since you are using Two TMG Servers for testing . When you test with each of these TMG serrvers do you point your SQL Server's Default gateway to the respective TMG server in the test?

    If SQL default gateway IP address always points to the first TMG's internal NIC IP , when external client tries to connect via second TMG the response from SQL might attempt to go via first TMG and will fail

    So to resolve this you can use the option "Requests appear to come from the Forefront TMG" in the second server's Publishing rule and in the first server keep it as "Requests appear to come from the the original client"

     


    Bala Natarajan [MSFT]| Sr. Support Escalation Engineer | CSS Security
    Friday, July 23, 2010 12:53 PM
    Answerer

All replies

  • When you say: "an external client is not able telnet port 1433 on the failing TMG". What do you see on the live logging?

    Also, on the "To" tab of your publishing rule, which option do you have selected:

    a) Requests appear to come from the Forefront TMG.
    b) Requests appear to come from the the original client.

    If B is selected, try to change the option to A and test again. If A is already selected then we need to better understand the error that you are facing on the live logging when external client is trying to connect.

    HTH,


    Yuri Diogenes [MSFT] - http://blogs.technet.com/yuridiogenes
    Tuesday, July 20, 2010 5:55 PM
    Moderator
  • Hello Yuri,

    Thank you for your reply and thank you for helping us.

    >> When you say: "an external client is not able telnet port 1433 on the failing TMG". What do you see on the live logging?

    Shame on me, I do not know what is "live Logging". I also found no such topic in TMG help. 

    >> Also, on the "To" tab of your publishing rule, which option do you have selected:

    >> a) Requests appear to come from the Forefront TMG.
    >> b) Requests appear to come from the the original client

    I tried both settings with no difference. I also do not think that I made any mistake in rules, because I have second identical TMG server that works perfectly.

    I also did few more tests restoring the "known good" System Image of the TMG server. I found, that as soon as SQL publishing rule is added (only one operation), not only SQL publishing rule diesn't work, but also FTP publishing rule doesn't work as well. However 3 other publishing rules (2 HTTPS and one on non-standard port) continue to work fine. Deleting of the new SQL publishing rule (second operation on restored image) do not restore functionality of FTP publishing. FTP remains dead until new system restore.

    I will appreciate any instructions how to debug/log the situation.

    With best regards,

    Al

     

    Wednesday, July 21, 2010 7:38 AM
  • No Problem Al,

    Try to get more info doing the following:

    1. Open TMG Console and go to Logs & Reports.
    2. Click Edit Filter.
    3. On the "Filter by" field select "Client IP".
    4. On the "Condition" field select "Equals".
    5. On the "Value" field type the IP address of the external client that you are testing the access.
    6. Click "Add to List" button.
    7. Click "Start Query" button.
    8. Repro the issue and see if you get any useful log about what is going on.

    Use the option "Copy All Results to Clipboard" on the task pane to copy the log to a notepad file. Paste it here just the relevant information for this issue and let's see how can we move forward on this.

    HTH,


    Yuri Diogenes [MSFT] - http://blogs.technet.com/yuridiogenes
    Wednesday, July 21, 2010 1:57 PM
    Moderator
  • Hello Yuri,

    Thank you for helping us. I followed your instructions. When I telnet SQL server, TMG log (on failing machine) shows me:

    Initiated Connection SCILLA 23-Jul-10 09:16:27
    Log type: Firewall service
    Status: The operation completed successfully. 
    Rule: SQL Server
    Source: External (88.133.1.230:49814)
    Destination: Internal (192.168.13.4:1433)
    Protocol: Microsoft SQL Server
     Additional information
    Number of bytes sent: 0 Number of bytes received: 0
    Processing time: 0ms Original Client IP: 88.133.1.230

    However telnet reports that connection failed. 

    Similar way, when I try to connect via SQL Management tool, TMG reports connection as initiated, but then SQL Management tool reports failure.

    When I telnet the second (working) TMG server - connection is established OK:

    Initiated Connection HARIBDA 23-Jul-10 09:41:12
    Log type: Firewall service
    Status: The operation completed successfully. 
    Rule: SQL Server
    Source: External (88.133.1.230:49892)
    Destination: Internal (192.168.13.4:1433)
    Protocol: Microsoft SQL Server
     Additional information
    Number of bytes sent: 0 Number of bytes received: 0
    Processing time: 0ms Original Client IP: 88.133.1.230

    The rest of the log on both servers is quite similar: couple of denied NetBios requests on port 137 and denied DHCP request on port 67.

    What is next step?

    With best regards,

    Al

     

    Friday, July 23, 2010 7:56 AM
  • Based on the logs that you just pasted both TMG are behaving similar, because the log "Initiated Connection" is exactly the same, the difference is on the reply, which leads me to belive that the package  pass through TMG and is handled by the SQL Server that doesn't answer properly. In order to confirm that what you can do is to:

    1. Get a Netmon trace on the external client while trying to perform the test.
    2. Get a Netmon trace on the internal interface of TMG while peforming the test.

    This way you will see if the request from the client is sent to the internal SQL Server and how SQL replies back.

    HTH,


    Yuri Diogenes [MSFT] - http://blogs.technet.com/yuridiogenes
    Friday, July 23, 2010 11:17 AM
    Moderator
  • Hi Yuri,

    I'm not sure that I'm able analyze NetMon output.

    There is 2 points that I do not understand.

    First point:

    1. In internal network SQL server replies to any client.

    2. I'm contacting the same SQL server (192.168.13.4) from the same client (88.133.1.230) via 2 TMG servers. One connection works as expected, second - not.

    It will be logical to assume that reason is TMG server.

    Second point:

    In my understanding: (a) Client establish connection with TMG, (b) TMG establish connection with published server and (c) pass requests through. In this logic, at least Telnet should be able to establish connection with TMG. Do I understand something wrong?

    Can I somehow trace what happen between TMG and published server?

    With best regards,

    Al

    Friday, July 23, 2010 12:29 PM
  • Since you are using Two TMG Servers for testing . When you test with each of these TMG serrvers do you point your SQL Server's Default gateway to the respective TMG server in the test?

    If SQL default gateway IP address always points to the first TMG's internal NIC IP , when external client tries to connect via second TMG the response from SQL might attempt to go via first TMG and will fail

    So to resolve this you can use the option "Requests appear to come from the Forefront TMG" in the second server's Publishing rule and in the first server keep it as "Requests appear to come from the the original client"

     


    Bala Natarajan [MSFT]| Sr. Support Escalation Engineer | CSS Security
    Friday, July 23, 2010 12:53 PM
    Answerer
  • The point that Bala brought is the reason why netmon traces will be important, because the package needs to go and come back. Do we have route to came back? Is TMG sending the package and SQL is replying to a different path? You need to analyze that side as Bala also mention.


    Yuri Diogenes [MSFT] - http://blogs.technet.com/yuridiogenes
    Friday, July 23, 2010 2:07 PM
    Moderator
  • Hello Bala and Yuri,

    First of all I would like to thank Bala for a brilliant suggestion. In fact my problem is resolved. Thank you so much!

    Yuri is certainly correct about the NeyMon usage. However I'm configuring network in rare situations and Netmon is not a simple utility for an occasional user. Sorry anout that.

    The solution proposed by Bala brought an additional question. For this reason I did not mark the present question as answerred yet (even if in fact the problem is solved). If any of you will consider that my additional question should be discussed in frame of the different thread or even different forum, please feel free to close this thread.

    Question: We have 2 Clustered servers: e.g. (Cluster1 and Cluster2) and 2 TMG Servers (TMG1 and TMG2). The Cluster1 server have as default Gateway TMG1 and as second gateway - TMG2. Cluster2 server have as default gateway TMG2 and as second gateway TMG1. The SQL server is a clustered service.

    Since I found no way to configure default gateway per clustered service, I would expect that default gateway for SQL would be default gateway of the server where SQL is currently running. Therefore I tried to move SQL between Cluster1 and Cluster2 nodes. Regardless on which node SQL is running, TMG1 requires "Requests appear to come from the Forefront TMG" and TMG2 works in both modes. Should I understand that default gateway for clustered service remains the same? If so, it should be a way to configure it, isn't? Or do I understand something wrong?

    With best regards,

    Al

    Saturday, July 24, 2010 6:47 PM
  • Hi Al,

    I'm glad that your issue is resolved, I'm marking this thread as resolved since we were able to achieve the main goal and to better track the real issue addressed on this thread.

    About your question: are TMG1 and TMG2 part of a NLB? Because if it is then your SQL Server should point to the Virtual IP of the NLB as default gateway, this will address the issues.

     


    Yuri Diogenes [MSFT] - http://blogs.technet.com/yuridiogenes
    Sunday, July 25, 2010 12:16 AM
    Moderator
  • Hi Yuri,

    No, TMG1 and TMG2 are 2 separate TMG servers. They are not forming NLB.

    With best regards,

    Al

     

    Sunday, July 25, 2010 10:37 AM