One of my clients has applications that use MSDTC. We’re spinning up a SQL Server 2016 Availability Group to take advantage of the MSDTC support it introduced. Most of these distributed transactions take place across linked servers. And of course the new servers are in a new domain with a very limited trust with the old domain.
I found lots of articles and checklists for configuring MSDTC but none had everything I needed. This is the checklist I came up with to configure cross-domain MSDTC with a limited trust in place. (I don’t know what kind of trust it is. I just know I have to put accounts into Domain Local groups in order to use them across the trust. And it’s one way.)
I found LOTS of articles on configuring MSDTC. These are the ones I found to be the most comprehensive.
- Troubleshooting MSDTC Communication Checklist – This is one of the better checklists I found. Has a good list of required ports.
- Troubleshooting Problems with MSDTC – Another good resource
- DTCPing – A VERY useful utility to confirm basic connectivity.
- DTCTester – Utility to perform a distributed transaction.
There are the steps that future Bill will need to get this configured next time.
We were crossing a domain boundary and weren’t getting good NetBIOS name resolution. I had to put a host file entry on both servers pointing to each other with just the machine name. I eventually had to put a second name in for the Availability Group Listener. You need to be able to have a ping –a resolve the names on both sides using only the name and not a FQDN. And if you’re editing the hosts file don’t forget to run NOTEPAD as an Administrator.
Fix the Matching CIDs
I’m not sure exactly how this came to happen but both these machines had the same CID values for MSDTC. (Note: I still don’t really know what a CID is but I know they can’t have the same one – see the second document above.) The only way to change the CID value is reinstall MSDTC. If you’re building machines from images you’ll probably have this problem. You reinstall by running:
I suggest a reboot after each step. Until I rebooted I didn’t see the service installed.
Open the Ports
I had the following ports open in the firewall:
- Port 135 both ways (for RPC)
- The dynamic ports 49152-65535
MSDTC starts talking on 135 and then jumps to a dynamic port. I think the firewall people may have done something fancier but that’s what I told them.
I also had to:
- Enable the three inbound Windows Firewall rules for Distributed Transaction Coordinator
- Enable the one outbound Windows Firewall rule for Distributed Transaction Coordinator
Those rules exist but they were disabled by default in my environment.
Configure MSDTC Permissions
Using DCOMCNFG.EXE I had to enable the following permissions in MSDTC.
- Network DTC Access
- Allow Inbound
- Allow Outbound
- No Authentication Required – This one was a little frustrating. I’d prefer to have them authenticate but I haven’t worked on that enough yet.
There are screenshots of this all over the web that you’ve probably already found.
At this point you should be able to get DTCPing to work. You run it on both sides and they talk to each other simulating a transaction. It give good error messages in the application. It also writes a text file with more detail diagnostic logging. It’s a very handy utility. If you get any errors you’ll need to work through those.
This little command-line utility actually runs a distributed transaction. It creates a temporary table and and inserts a row into it. You’ll need to configure a 32-bit ODBC entry. Look for C:\Windows\SysWOW64\odbcad32.exe to make that entry. The 64-bit utility is in C:\Windows\System32 and has the same name. That’s some confusing naming right there. You’ll also need a SQL Server login and password. If you’re going to test against multiple machines your ODBC source should point to a hosts file entry. It makes testing much easier.