Troubleshooting MSDTC: The transaction manager has disabled its support for remote/network transactions

While rolling out a large stored procedure to our test servers today Luvy and I got a cryptic seeming server communications error message indicating there is a problem with the MSDTC settings.

The transaction manager has disabled its support for remote/network transactions

At first we thought this had something to do with the linked server we were using. However after testing out the various server option settings and checking for similar errors on Google it became clear that the error was due to configuration issues on the servers housing our SQL server databases.

One of the servers is Windows 2008 and the other is Windows 2003 R2, so finding the locations of the settings was different between the two machines.

The first set of steps was that we had to check the running services. On both machines this was the same:

1. Click Start -> Administrative Tools -> Services
2. In the Services popup look for the service called ‘Distributed Transaction Coordinator’ and make sure it is running

Secondly we had to check the MSDTC options on the computer.

• On the Windows 2003 machine this was done by clicking:

1. Start -> Administrative Tools -> Component Services
2. In the Component Services manager go to: Console Root -> Computers -> My Computer
3. Right click properties on My Computer and select the MSDTC Tab and click Security Configuration
4. Make sure to check the following:
• Network DTC Access
• Allow Remote Clients
• Allow Inbound
• Allow Outbound
• Enable Transaction Internet Protocol (TIP) Transactions

• On the Windows 2008 Machine this was done by clicking:

1. Start -> Administrative Tools -> Component Services
2. In the Component Services manager go to: Console Root -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC
3. Right click the Local DTC node and click Properties
4. In the Local DTC Properties click the Security tab
5. In the Security Tab make sure to check the following:
   • Network DTC Access
   • Allow Remote Clients
   • Allow Inbound
   • Allow Outbound

After following these steps we decided to play things safe and to restart both servers (although it might also work to simply restart the Distributed Transaction Coordinator service)

At this time we ran our stored procedure test again. Instead of an immediate error the query hung for a few seconds, but then we got the error again.

At this point we needed to check the firewall settings on both computers. To do this:

1. Click Start -> Control Panel -> Windows Firewall
2. In the Windows Firewall popup, on the left menu click the option: Allow a program through Windows Firewall
3. In the Windows Firewall Settings Popup Click the Exceptions Tab, then click the Add Program button
4. In the Add a Program popup click the Browse button
5. Browse to c:\Windows\System32
6. Then select the file msdtc.exe
7. Then click the Change Scope button
8. In the Change Scope popup click the My network (subnet) only radio button (we only wanted this functionality for server communications within our internal corporate network)
9. Then save the changes by clicking OK
10. Open a command prompt button (click Start -> then type cmd)
11. In the DOS command prompt type
    1. net stop msdtc
    2. net start msdtc

Once these steps had been completed on both servers we ran a test of the stored procedure once more. We were quite pleased at this point that the procedure ran without any problems.

References:

• MSDN Data Developer Center
• SQL Team.com Help Forums
• Javier Perez’s Blog
• Wikipedia: Distributed Transaction Coordinator (MSDTC) service