sqlorcl

Just another WordPress.com site

Category Archives: MSDTC

MSDTC is failing with remote server

We had an problem with remote DTC or Distributed transaction connection with one of our test SQL Server. We experienced “RPC Endpoint Mapper” and “firewall has closed the ports errors” error messages during a BizTalk server installation. Due to Architectural design the BizTalk Server and SQL server are in different machine and during installation BizTalk server couldn’t connect to SQL server through MSDTC services. Through further investigation, the following steps we followed to fix this remote connection issue.

  1. The very first step we confirmed that MSDTC services were configured correctly to both servers. There is an excellent tutorial posted by Mr. Denny how to configure the MSDTC services in Windows 2008 server.  Please follow that tutorial and setup the MSDTC services and security properties.
  2. Once the MSDTC was installed and configured in Windows 2008R2 the local DTC security configuration should be look like below screenshot (in our environment). To view the local DTC security configuration we need to GO Start >Administrative Tools > Component Services, it will open a Component Services Window, on the window navigate to Component Services > Computer > My Computer > Distributed Transactional Coordinator. Right click Local DTC and select properties and it will pop up Local DTC Properties windows.
      

       
     
     
     

    We also confirmed that boxes marked in red under Security Setting and Transactional Manager Communication also checked.

  3. As a part of project design BizTalk and database servers were in different machine so we configured Local DTC security configuration to both server’s.
  4. Firewall configurations are described in KB250367 to control RPC dynamic port allocation. We had done this configuration on both servers. Note also that the firewall must be open in both directions for the specified ports. Here are the steps:
    a) To start Registry Editor, click Start, click Run, type regedt32, and then click OK.
    b) You must use Regedt32.exe, rather than Regedit.exe, because Regedit.exe does not support the REG_MULTI_SZ data type that is required for the Ports value.
    c) Expand the tree by double-clicking the folders named in the following path:
    HKEY_LOCAL_MACHINE\Software\Microsoft\Rpc
    d) Click the RPC folder, and then click Add Key on the Edit menu.
    e) In the Add Key dialog box, in the Key Name box, type Internet, and then click OK.
    f) Add a key for Internet, by using the following values:
    Value: Ports
    Data Type: REG_MULTI-SZ
    Data: 5000-5020
    g) Add another key for Internet, by using the following values:
    Value: PortsInternetAvailable
    Data Type: REG_SZ
    Data: Y
    h) Add another key for Internet, by using the following values:
    Value: UseInternetPorts
    Data Type: REG_SZ
    Data: Y
    i) Restart the computer. When RPC restarts, it will assign incoming ports dynamically, based on the registry values that we have specified.Once we successfully completed the above steps the new registry should be look like this. 

      

  5. The next step to open these ports and msdtc.exe program as exceptions in the firewall. Here are the screenshots after we configured the firewall.
    a) Msdtc.exe program exception.

    b) TCP/IP ports.

    135 – RPC EPM (end point mapper)
    5000-5100 MSDTC [Dynamically assigned a port by the EPM]

Use the DTCTester Tool 

We downloaded and installed dtctester to both servers. DTCTester tool tests a distributed transaction against a specified Microsoft SQL Server. This tool helps to test distributed transactions across firewalls or against networks. dtctester performs distributed transactions by using ODBC API against a SQL Server database. This tool will create a temp table in the SQL server as well as inserts a record to the created temp table and at the end it will commit the changes.
The following were the steps we followed for testing DTC with DTCTester tool.  

  1. We created an ODBC data source for our SQL Server through the ODBC utility to the server where BizTalk Server had installed.
  2. Navigate to the folder in command prompt where the dtctester.exe file was extracted.
  3. Executed the following from the command line:
    dtctester <dsn name><user name><password>
    Replace the values in brackets as appropriate for your environment.
  4. We configured the ODBC Data source and executed the above command to both servers. If the test is successful then you will see these messages in command window. 
  5. We have seen the above messages in DTCTester result window in both servers which proved that MSDTC service was working remotely and locally.

Hopefully this post can help you to debug some MSDTC connection error.