Difference between revisions of "HowTo:Solve MSDTC-related problems"

Revision as of 22:38, 16 August 2023

From version 4 on, UBIK^® uses the Distributed Transaction Coordinator (MSDTC or DTC) to manage database transactions. However, there's the necessity for correct configuration on both the database server as well as the application server to make it work. This article explains how to do that.

Problem Symptoms

UBIK^® Studio or a UBIK^® service can't connect to your database or fails to create a session
Database view creation or instance data transfer doesn't work because of an exception
There's an error/exception log entry like: "Communication with the underlying transaction manager has failed"

Solution

[edit]

Configuring MSDTC Network Access
Firewall configuration
Test-Dtc and DTCPing

Configuring MSDTC Network Access

MSDTC has to be configured on both the machine where the database is hosted and the machine where the UBIK^® software accessing the database is run. Usually, this is a database server and an application server provided by the customer or hosted in the cloud.

The configuration is described here: https://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/enable-network-dtc-access?source=recommendations

Open the "Component Services" console (typing that into the Windows start menu should find you the right app)
In the Component Services console, you should see a tree view to the left.
Navigate down Console Root - Component Services - Computers - My Computer - Distributed Transaction Coordinator - Local DTC
Right-click on "Local DTC" and click on "Properties"
In the "Security" tab of the properties dialog, enable the checkboxes for
- "Network DTC Access" right at the top
- "Allow Inbound" in the section "Transaction Manager Configuration"
- "Allow Outbound" in the section "Transaction Manager Configuration"
Choose the radio box for "No Authentication Required" in the section "Transaction Manager Configuration".
Make sure the "NT AUTHORITY\NetworkService" account without credentials is specified in the "DTC Logon Account" section.
Click Apply and OK.

Please make sure this is done in both the database server as well as the application server!

The involved (host and client) machine names have to be found via DNS or NetBIOS; MSDTC doesn't work with just IP-addresses.

If the customer requires it, you might have to adapt the authentication settings. This is not explored in this article.

Firewall configuration

On both the database server and the application server, you have to make sure the firewall doesn't block the MSDTC service. Technically, MSDTC uses the RPC endpoint mapping service on port 135 to determine the port for the DTC endpoint, which can be dynamic (within the range of 49152-65535).

There are two strategies to open the required ports:

Enable the (preconfigured) firewall rules for dynamic MSDTC ports
Configure a fixed port for MSDTC and restrict the port range for RPC, and open those ports on the firewall

The latter should only be necessary if the customer's IT security policy requires it (e.g., if there's an external firewall that doesn't care much about the dynamic ports in your database server).

Please make sure the firewall is configured correctly both on the client machine (e.g., the application server) and the host machine (e.g., the DB server).

Restricting the dynamic ports mostly makes sense on the DB server, but it might also be necessary on the client machine depending on the customer's IT security policy.

Default dynamic ports

If you use the default dynamic ports, there are three different ways of adding the firewall rules to open them:

Powershell:
Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"
Netsh:
netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes
Or use Windows Firewall Advanced Settings, enabling the 3 Inbound and 1 Outbound Rules

Restricted dynamic ports

Here it is described how to configure a specific port for MSDTC and how to restrict the port range used by RPC: https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls

Long story short, we have to add a couple of registry entries:

HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC
- Add value: Name "ServerTcpPort", Type "DWORD (32-bit)", value "40299" (you can choose a port here, that's just a suggestion)
HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc
- Add key (folder): "Internet"
  - Add value: Name "Ports", Type "REG_MULTI_SZ", value "40200-40299" (you can choose a range of ports here, that's just a suggestion, but the DTC port should be within that range)
  - Add value: Name "PortsInternetAvailable", Type "REG_SZ", value "Y"
  - Add value: Name "UseInternetPorts", Type "REG_SZ", value "Y"

If you use a restricted ports range, you have to create new rules to open that range specifically (inbound and outbound).

Test-Dtc and DTCPing

Test-Dtc

You can use the Powershell module (or cmdlet) "Test-Dtc" to check if everything was configured correctly: https://learn.microsoft.com/en-us/powershell/module/msdtc/test-dtc?view=windowsserver2022-ps&source=recommendations

For an end-to-end test, you have to open an inbound port for the resource manager service created by this (3002 by default, but you can specify a different one).

DTCPing

Also, the DTCPing tool can be applied to test the correct setup. https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/ms-dtc-connectivity-issues?source=recommendations A good thing about this program is that it makes you notice to use the involved machine's names instead of IP addresses.

Here's a link to a detailed article describing how to trouble-shoot MSDTC issues using the DTCPing tool: https://puneet-gupta.github.io/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/

The DTCPing program has to run on both the host and the client machine.