Difference between revisions of "HowTo:Solve MSDTC-related problems"
| Line 39: | Line 39: | ||
On both the database server and the application server, you have to make sure the firewall doesn't block the MSDTC service. | 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). | 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). | ||
| + | {{attention|There 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: | There are two strategies to open the required ports: | ||
| Line 68: | Line 69: | ||
*** Add value: Name "UseInternetPorts", 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). | + | {{attention|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 and DTCPing = | ||
| Line 89: | Line 90: | ||
<!-- DO NOT REMOVE THIS -->{{Template:HowTo/End}}<!-- DO NOT REMOVE THIS --> | <!-- DO NOT REMOVE THIS -->{{Template:HowTo/End}}<!-- DO NOT REMOVE THIS --> | ||
| − | [[Category: | + | [[Category:Database|Solve MSDTC-related problems]] |
| − | + | ||
[[Category:How-To|Solve MSDTC-related problems]] | [[Category:How-To|Solve MSDTC-related problems]] | ||
[[Category:Install|Solve MSDTC-related problems]] | [[Category:Install|Solve MSDTC-related problems]] | ||
[[Category:Installing|Solve MSDTC-related problems]] | [[Category:Installing|Solve MSDTC-related problems]] | ||
| − | [[Category: | + | [[Category:Resources (internal)|Solve MSDTC-related problems]] |
| + | [[Category:Service|Solve MSDTC-related problems]] | ||
Revision as of 22:42, 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
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 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 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"
- Add key (folder): "Internet"
| 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. |
