Difference between revisions of "HowTo:Solve MSDTC-related problems"
| (7 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
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. | 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. | ||
| + | |||
| + | {{hint|On Microsoft Azure SQL Database services, MSDTC is not supported - but apparently, it's not needed either (as documented [[https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-transactions-overview?view=azuresql here]]). So If you don't face any problems this article can be ignored when working with Azure SQL Database.}} | ||
| + | |||
== Problem Symptoms == | == Problem Symptoms == | ||
| Line 14: | Line 17: | ||
The configuration is described here: | The configuration is described here: | ||
| − | https://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server | + | * [https://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server How do I enable MSDTC on SQL Server (StackOverflow)] |
| − | https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/enable-network-dtc-access?source=recommendations | + | * [https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/enable-network-dtc-access?source=recommendations Enable Network DTC Access (learn.microsoft.com)] |
| + | Use the following steps to configure DTC network access: | ||
* Open the "Component Services" console (typing that into the Windows start menu should find you the right app) | * 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. | * In the Component Services console, you should see a tree view to the left. | ||
| Line 41: | Line 45: | ||
{{attention|There should be an existing enabled rule for this already, but the RPC port 135 must be opened.}} | {{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 different strategies to open the required ports: |
| − | * Enable the (preconfigured) firewall rules for dynamic MSDTC ports | + | * Enable the (preconfigured) firewall rules for dynamic MSDTC ports (recommended if possible) |
| − | * Configure a fixed port for MSDTC and customize 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 (only recommended if the IT security policy requires it) |
| − | + | Custom ports 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| | + | {{hint|Customizing 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 === | ||
| − | If you use the default dynamic ports, there are | + | If you use the default dynamic ports, there are different ways of adding the firewall rules to open them: |
* Powershell: <source lang="winbatch">Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"</source> | * Powershell: <source lang="winbatch">Enable-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator"</source> | ||
* Netsh: <source lang="winbatch">netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes</source> | * Netsh: <source lang="winbatch">netsh advfirewall firewall set rule group="Distributed Transaction Coordinator" new enable=yes</source> | ||
| − | * | + | * Windows Firewall Advanced Settings UI: Enable the 3 Inbound and 1 Outbound Rules regarding the "Distributed Transaction Coordinator" |
| + | * Windows Firewall: Add a rule allowing the program MSDTC inbound and outbound (the path usually is "C:\windows\system32\msdtc.exe") | ||
=== Custom dynamic ports === | === 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: | 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 Configure DTC to work through firewalls (learn.microsoft.com)] |
Long story short, we have to add a couple of registry entries: | Long story short, we have to add a couple of registry entries: | ||
| Line 71: | Line 76: | ||
{{attention|If you use a custom 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).}} | ||
| − | = | + | Here's a documentation about how to add firewall rules with the Powershell: |
| + | * [https://learn.microsoft.com/en-us/powershell/module/netsecurity/new-netfirewallrule?view=windowsserver2022-ps Powershell New-NetFirewallRule (learn.microsoft.com)] | ||
| + | |||
| + | = Testing = | ||
| + | |||
| + | There are several tools you can use to test the correct configuration and function of the MSDTC on both your client and the host machine. | ||
| + | |||
| + | {{hint|It might pay off to check whether the "Distributed Transaction Coordinator" (MSDTC) Windows Service is running on the database server. You may wish to set the startup type to "Automatic". If it cannot be started, please check the Event Manager for errors.}} | ||
=== Test-Dtc === | === Test-Dtc === | ||
You can use the Powershell module (or cmdlet) "Test-Dtc" to check if everything was configured correctly: | 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 Test-Dtc (learn.microsoft.com)] | |
| − | https://learn.microsoft.com/en-us/powershell/module/msdtc/test-dtc?view=windowsserver2022-ps&source=recommendations | + | |
{{hint|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).}} | {{hint|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).}} | ||
| Line 82: | Line 93: | ||
=== DTCPing === | === DTCPing === | ||
Also, the DTCPing tool can be applied to test the correct setup: | 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 Trouble-shoot MSDTC connectivity issues (learn.microsoft.com)] | |
| − | 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. | 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: | 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/ Trouble-shooting MSDTC issues with DTCPing (puneet-gupta.github.io)] | |
| − | https://puneet-gupta.github.io/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/ | + | |
{{hint|The DTCPing program has to run on both the host and the client machine.}} | {{hint|The DTCPing program has to run on both the host and the client machine.}} | ||
<!-- DO NOT REMOVE THIS -->{{Template:HowTo/End}}<!-- DO NOT REMOVE THIS --> | <!-- DO NOT REMOVE THIS -->{{Template:HowTo/End}}<!-- DO NOT REMOVE THIS --> | ||
| + | |||
| + | = See also = | ||
| + | * [https://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server How do I enable MSDTC on SQL Server (StackOverflow)] | ||
| + | * [https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/enable-network-dtc-access?source=recommendations Enable Network DTC Access (learn.microsoft.com)] | ||
| + | * [https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/configure-dtc-to-work-through-firewalls Configure DTC to work through firewalls (learn.microsoft.com)] using custom port range (recommended only if necessary!) | ||
| + | * [https://learn.microsoft.com/en-us/powershell/module/netsecurity/new-netfirewallrule?view=windowsserver2022-ps Powershell New-NetFirewallRule (learn.microsoft.com)] | ||
| + | * [https://learn.microsoft.com/en-us/powershell/module/msdtc/test-dtc?view=windowsserver2022-ps&source=recommendations Test-Dtc (learn.microsoft.com)] | ||
| + | * [https://learn.microsoft.com/en-us/troubleshoot/windows-server/application-management/ms-dtc-connectivity-issues?source=recommendations Trouble-shoot MSDTC connectivity issues (learn.microsoft.com)] | ||
| + | * [https://puneet-gupta.github.io/puneetgupta/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool/ Trouble-shooting MSDTC issues with DTCPing (puneet-gupta.github.io)] | ||
[[Category:Database|Solve MSDTC-related problems]] | [[Category:Database|Solve MSDTC-related problems]] | ||
Latest revision as of 08:36, 25 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.
 | On Microsoft Azure SQL Database services, MSDTC is not supported - but apparently, it's not needed either (as documented [here]). So If you don't face any problems this article can be ignored when working with Azure SQL Database. |
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:
Use the following steps to configure DTC network access:
- 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 different strategies to open the required ports:
- Enable the (preconfigured) firewall rules for dynamic MSDTC ports (recommended if possible)
- Configure a fixed port for MSDTC and customize the port range for RPC, and open those ports on the firewall (only recommended if the IT security policy requires it)
Custom ports 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). |
| Customizing 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 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
- Windows Firewall Advanced Settings UI: Enable the 3 Inbound and 1 Outbound Rules regarding the "Distributed Transaction Coordinator"
- Windows Firewall: Add a rule allowing the program MSDTC inbound and outbound (the path usually is "C:\windows\system32\msdtc.exe")
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:
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 custom ports range, you have to create new rules to open that range specifically (inbound and outbound). |
Here's a documentation about how to add firewall rules with the Powershell:
Testing
There are several tools you can use to test the correct configuration and function of the MSDTC on both your client and the host machine.
| It might pay off to check whether the "Distributed Transaction Coordinator" (MSDTC) Windows Service is running on the database server. You may wish to set the startup type to "Automatic". If it cannot be started, please check the Event Manager for errors. |
Test-Dtc
You can use the Powershell module (or cmdlet) "Test-Dtc" to check if everything was configured correctly:
| 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:
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:
| The DTCPing program has to run on both the host and the client machine. |
See also
- How do I enable MSDTC on SQL Server (StackOverflow)
- Enable Network DTC Access (learn.microsoft.com)
- Configure DTC to work through firewalls (learn.microsoft.com) using custom port range (recommended only if necessary!)
- Powershell New-NetFirewallRule (learn.microsoft.com)
- Test-Dtc (learn.microsoft.com)
- Trouble-shoot MSDTC connectivity issues (learn.microsoft.com)
- Trouble-shooting MSDTC issues with DTCPing (puneet-gupta.github.io)
