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…:

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)
   

   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.

• DTC Tester – http://support.microsoft.com/kb/293799
• DTCPing – http://www.microsoft.com/DOWNLOADS/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&displaylang=en

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

   1	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.
    
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