MSDTC through a firewall to an SQL cluster with RPC

It’s not a pretty title and it’s not a pretty subject, that much I can tell you. Strap in, this one’s a biggy….

The company I work for are busy developing an application that will make use of DTC from a DMZ, firing queries back to SQL using MSDTC hosted on an active-passive Microsoft Cluster running SQL 2005 all through a firewall. All of which I might add have been configured by me, so I know them like the back of my hand. On Wednesday this week I was given the above problem with a requirement to make it work on said highly available SQL 2005 Cluster by the following Tuesday. The obvious problems of ensuring uptime meant the work could only really be done out of hours and so it came down to doing it on a Sunday.

I obviously spent a fair amount of time researching the problem before Sunday and admittedly, lots of people have had the same issue so finding several KB article explaining how to achieve each task wasn’t too difficult but none of them really covered every aspect of what I was doing. Either they were about getting DTC working over a firewall or they were about configuring DTC on a cluster.

When you configure SQL Server 2005 on a Microsoft Cluster Server, the DTC resource is, as far as I can remember, added to the SQL cluster resource group. In one KB article I found, Microsoft recommends moving DTC to another resource group. I concluded, perhaps incorrectly, that while DTC is only being used for SQL, there is little sense or benefit in separating the resource from the SQL group. After all, if the SQL resource group is down, DTC won’t be used anyway. That’s not to say that if you use DTC for another purpose, other than SQL queries, that Microsoft’s recommendations shouldn’t be followed – they should.

Right, down to it then….

MSDTC uses the RPC end point mapper to obtain a listening port to serve requests on. When DTC starts, it communicates with the RPC end point  mapper to request a port to listen on. Without limiting that port range, RPC EPM will give DTC a port number anywhere above 1024 which makes life extremely difficult for us firewall admins. It’s like trying to nail down air, every time you reboot or restart the DTC service, it’ll get a new totally random port number to listen on.

Until you configure RPC to limit itself to handing out a specific range of ports to services that use it, you will not get RPC to work through a firewall so this first step is absolutely critical. I found the following KB article which details the required process to follow, but don’t follow it, this article doesn’t take a cluster in to consideration…:

Configuring Microsoft Distributed Transaction Coordinator (DTC) to work through a firewall

You’ll read through the article and then ask why on earth it is retired. Yes it really is that old and to be honest, it needed to be retired because there is a more GUI friendly way of  achieving the same thing. I never found it anywhere on the ‘net so here it is, with pictures!

Configure RPC to use a limited range of ports

  1. This process must be followed on all cluster nodes and all DMZ servers requiring access across the firewall.
  2. Start > Run > dcomcnfg
  3. Expand Component Services > Computers
  4. Right-click My Computer and select Properties
  5. On the Default Protocols tab, highlight Connection-oriented TCP/IP and click Properties.
  6. Add a port range. (See screenshots below)
    port-range-specifiedadd-port-range

    NB: You MUST specify a range of ports sufficient for the purposes of the target architecture and they must be above 1024. In other words, if you’re running a cluster, remember that MSCS uses RPC also. I recommend specifying a minimum range of 200 ports, somewhere in the 5000 range.

  7. Crucially, the one thing that EVERYBODY forgets to tell you is that you must apply this change, using the same options, to all cluster nodes and the IIS boxes in the DMZ as well.
    Microsoft tells us that DTC is cluster aware but you aren’t configuring DTC, you’re configuring RPC, which isn’t cluster aware, so these changes must be replicated on each cluster node and on each server in the DMZ.
  8. Now for the bad news. You must reboot every box you made the changes on in order to restart RPC on that server and ensure it is only handing out port numbers in the range you have specified.
  9. By following these steps, you have made RPC restart (yes, rebooting really is the only way). When DTC starts shortly afterwards, it will request a port from the RPC EPM and end up listening on a port in the range you have specified which means we can now open those same ports on the firewall.

There are two invaluable downloads from Microsoft that you need beyond this point.

Configure the firewall

You must open the following ports from your DMZ servers to the SQL cluster AND from the SQL cluster to the DMZ servers for MSDTC to operate as expected. Use DTC Ping (see below) to test name resolution and connectivity. DTCPing does not test actual DTC is operating as expected, just whether or not systems can resolve and connect to one another.

  • TCP/135 – MSRPC Endpoint Mapper
  • Using the above as an example:
    TCP/5500-5700 – RPC supplied MSDTC port

Test DTC works inside the firewall

Using DTC Tester…

  1. On your workstation that sits inside the firewall, configure a DSN to point to a non-critical database on your target SQL cluster – you should know how to do this.
  2. Open a command prompt and execute dtctester using the DSN you created, with a username and password with access to the database. Despite entering this in the DSN, you must specify the username and password on the command line for DTC Tester. The command line should be something like:
    dtctester DSNNAME username password
  3. This utility does an actual test of DTC within your firewall so that you are sure DTC is working as expected on your SQL cluster.
  4. If the test is successful, congratulations. If not (which is more likely) you’ll need to make some more changes, but this time to DTC.

Configure DTC on all servers (DMZ servers and Cluster nodes)

  1. On the active cluster node (DTC is cluster aware, RPC isn’t), click Start > Run > dcomcnfg
    Do not make this change on the passive cluster node. Doing so will prevent the cluster service from starting at next reboot.
  2. Expand Component Services > Computers
  3. Right-click My Computer and select Properties
  4. On the MSDTC tab, click the Security Configuration button
  5. Ensure Network DTC Access is enabled. If it is not, go to Add/Remove Programs and enable it there instead. You may need to reboot after doing this.
  6. Under Client and Administration, ensure Allow Remote Clients is enabled
  7. Under Transaction Manager Communication, ensure Allow Inbound and Allow Outbound is enabled.
    NB: I believe this is the crucial point and what was missing from Microsoft’s KB article about configuring DTC on SQL clusters. Mutual authentication is not supported on Microsoft Cluster Servers so don’t choose that…. BUT… selecting Incoming Caller Authentication Required won’t work either. Leaving us only one option – No Authentication Required. Check this.
  8. Under DTC Logon Account, ensure it is set to NT Authority\Network Service
  9. Click OK. You will be notified that MSDTC will be restarted, click OK to allow it to restart. These changes should be immediate. If you watch MSDTC on both nodes, you will see it restart on both.
  10. Repeat the above procedure on all servers that will use MSDTC. This includes DMZ servers.
    Since MSDTC is a two-way communication, you must ensure the No Authentication Required setting is applied on all servers that will be using MSDTC, so this means your test client workstation and any IIS servers sitting in your DMZ.
    netdtcaccess
  11. Re run DTC Tester from your client workstation and it should be successful. If it is not, go through everything above again and make sure all servers have been rebooted after the RPC changes made earlier.

Although I have not mentioned DTCPing at length, using it proves that DTC will work across firewalls and DMZ zones once you’ve added in the correct ports. MSDTC is a funny little service that uses NetBIOS names to resolve end points so don’t forget you might need to manually configure some entries in your HOSTS file for name resolution to work across your DMZ boundaries. HOSTS file changes are immediate once saved so thankfully, no reboots required!

Test DTC from the DMZ

Now for the big test.

  1. On one of your DMZ servers, configure a DSN using the same settings as used on your test client worksation earlier. If you have no name resolution within your DMZ, configure your HOSTS file to resolve names that way. Obviously if you’re using DTC, your application needs to know where the SQL cluster is!
  2. Again on your DMZ server, run DTC Tester just as before to connect to your clustered MSDTC resource and perform a transaction.
  3. Assuming you have configured the RPC port settings on all servers, rebooted them, configured MSDTC security settings on all servers and opened the correct firewall ports, DTC Tester should successfully enlist the DTC connection and prompt you to commit the transaction.

If you’re still struggling beyond this point, I can only suggest there may be an issue with your DTC cluster resource and it may be worthwhile removing it and re-installing (just the DTC resource, not the entire cluster!). I know it’s a bit naff that I’m assuming the above test is successful but in all honesty, if it isn’t, something else is afoot in your config that needs attention. If you have any comments or I’ve made any errors (quite likely) or if you just want to say thanks, please fill out the form below!

Thanks

-Lewis

12 thoughts on “MSDTC through a firewall to an SQL cluster with RPC”

  1. Thanks useful information! Wish MS had this documented somewhere.

    I will be testing this out myself, not the cluster portion but the firewall and specific ports being required.
    This page cannot be viewed in IE for some reason Firefox only.

  2. Hi Jeremy, thanks for the feedback. I noticed it works ok in IE8 but does flag an error about parent and child elements. Not entirely sure what that’s all about but I’ll have a look.
    Thanks
    Lewis

  3. What I don’t understand is where those settings which I make using the GUI are being saved to…
    I have already scanned the registry several times for the the port numbers I have entered but they are just not there.
    I would have expected that even if the GUI does not create the very same registry keys as desribed in the article http://support.microsoft.com/kb/250367
    it would still save the values somewhere in the registry but that is clearly not the case…
    So is there maybe an INI file for RPC if yes where on the filesystem is it?…

  4. pls ignore by previous comment,
    i have now tried it on another server and now I see the corresponding keys & values in the server’s registry, maybe it was a kind of permissions issue in the first server’s case…
    my compliments for this great blog posting!

  5. This is a fantastic article! I struggled in much the same way with this and it took me a good few days to finally get my head around it. But this is bang on! Wish I had this then.

    Good job Lewis! A*

  6. Great article, I’m having this issue right now with a SQL Server 2005 test instance hosted on a Windows Server 2008 R2 cluster. I have two SQL Server 2008 R2 instances and one SQL Server 2005 instance on this cluster. My only question is, why don’t the 2008 R2 instances have problems communicating to the MSDTC but the 2005 instance requires the configuration change documented in your article?

    Thanks,
    Adam

  7. February 2013 and I’m still using the information detailed here for SQL 2012 and Windows 2008 R2 communications. Am I glad I took the time to write this article 4 years ago!

  8. Thanks so much for this excellent resource! We used it late last night with “Great Success.” We also had to open up port 135 which began the communication between the servers. It seems that both source and destination are initiators, so the FW needed to be open both ways. Incoming Caller Authentication Required worked fine for us (Same domain, source: SQL2008R2 dest: SQL2000).

  9. Great article and well written. I am a Firewall Engineer & this article helped with a few pointers. Therefore, I like to share this with you. On an enterprise FW, you don’t have to open those dynamic ranges of 1024 – 65535 or as above states 5000+ if you don’t want to. Much like FTP, as the FW knows, once the client connects to server via port 21, the server will ask the client, to connect once more via 20. The FW does this without the FW engineer configuring anything but a one line of access that allows TCP FTP. For DTCRPC to work In this same manner, the FW engineer must write a simple policy and only allow TCP 135 from clients to server. The server then dictates what port for clients to connect back (hopefully, via steps above, you modify this by opening 200 or so ports instead 64000+ ports). The FW then looks at the one line of allow to 135 and the policy and knows (as it does with FTP) to dynamically allow subsequent ports dictated by the server. without an issue.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>