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
Firewall configuration
Testing

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:

Configure DTC to work through firewalls (learn.microsoft.com)

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 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:

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.

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:

Test-Dtc (learn.microsoft.com)

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:

Trouble-shoot MSDTC connectivity issues (learn.microsoft.com)

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:

Trouble-shooting MSDTC issues with DTCPing (puneet-gupta.github.io)

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

Solve MSDTC-related problems