Jump to: navigation, search

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


m
Line 43: Line 43:
 
There are two strategies to open the required ports:
 
There are two strategies to open the required ports:
 
* Enable the (preconfigured) firewall rules for dynamic MSDTC 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
+
* Configure a fixed port for MSDTC and customize 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).
 
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).
  
 
{{attention|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).}}
 
{{attention|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).}}
{{hint|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.}}
+
{{hint|Cusotmizing 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 ===
 
=== Default dynamic ports ===
Line 56: Line 56:
 
* Or use Windows Firewall Advanced Settings, enabling the 3 Inbound and 1 Outbound Rules
 
* Or use Windows Firewall Advanced Settings, enabling the 3 Inbound and 1 Outbound Rules
  
=== Restricted dynamic ports ===
+
=== Custom dynamic ports ===
Here it is described how to configure a specific port for MSDTC and how to restrict the port range used by RPC:
+
Here it is described how to configure a specific port for MSDTC and how to customize the port range used by RPC:
 
https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls
 
https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls
  
Line 69: Line 69:
 
*** Add value: Name "UseInternetPorts", Type "REG_SZ", value "Y"
 
*** Add value: Name "UseInternetPorts", Type "REG_SZ", value "Y"
  
{{attention|If you use a restricted ports range, you have to create new rules to open that range specifically (inbound and outbound).}}
+
{{attention|If you use a custom ports range, you have to create new rules to open that range specifically (inbound and outbound).}}
  
 
= Test-Dtc and DTCPing =
 
= Test-Dtc and DTCPing =

Revision as of 22:43, 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

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.
IC Attention.pngPlease make sure this is done in both the database server as well as the application server!
IC Attention.pngThe involved (host and client) machine names have to be found via DNS or NetBIOS; MSDTC doesn't work with just IP-addresses.
IC Hint square.pngIf 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).

IC Attention.pngThere should be an existing enabled rule for this already, but the RPC port 135 must be opened.

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 customize 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).

IC Attention.pngPlease 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).
IC Hint square.pngCusotmizing 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

Custom dynamic ports

Here it is described how to configure a specific port for MSDTC and how to customize 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"
IC Attention.pngIf you use a custom 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

IC Hint square.pngFor 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/

IC Hint square.pngThe DTCPing program has to run on both the host and the client machine.