Configuring XA transactions

Edit online
Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-03-13 12:15
Configure XA transactions after the Microsoft SQL Server database is installed and before you start the server. The SQL Server JDBC driver provides support for Java™ Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions. JDBC connections that are obtained from the SQLServerXADataSource class can participate in standard distributed transaction processing environments such as Java Platform, Enterprise Edition (Java EE) application servers.

About this task

Failure to configure the XA transactions can result in the following error when the server starts:javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."..

The MS DTC service should be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started.

Procedure

  1. To enable MS DTC for XA transactions, complete the following steps:
    1. Select Control Panel > Administrative Tools > Component Services.
    2. Select Distributed Transaction CoordinatorComponent Services > Computers > My Computer.
    3. Right-click Local DTC and then select Properties.
    4. Click the Security tab on the Local DTC Properties window.
    5. Select the Enable XA Transactions check box, and click OK. This will restart the MS DTC service.
    6. Click OK again to close the Properties window, and then close Component Services.
    7. Restart SQL Server to ensure that it syncs up with the MS DTC changes.
  2. Configure the JDBC Distributed Transaction Components:
    1. Download the sqljdbc_xa.dll file from the Microsoft site.
    2. Copy the sqljdbc_xa.dll file to the Binn directory (for a default SQL Server install, the location is C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn) of the SQL Server computer. Use the sqljdbc_xa.dll file in the x64 folder.
    3. Run the xa_install.sql database script on SQL Server. For example; from the command prompt, run sqlcmd -i xa_install.sql. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance. You can ignore errors about unable to drop procedures that don't exist.
  3. Follow these steps for configuring Windows authentication:
    • Locate the sqljdbc_auth.dll file in the Microsoft JDBC driver package.
    • Copy the sqljdbc_auth.dll file to the Binn directory (for a default SQL Server install, the location is C:/Program Files/Microsoft SQL Server/MSSQL12.MSSQLSERVER/MSSQL/Binn) of the SQL Server computer. Use the sqljdbc_auth.dll file in the x64 folder.

What to do next

After you configure the XA transactions and before you start the server, you must configure your TCP/IP connectivity by using the following steps:
  1. From the Start menu, click Microsoft SQL Server 2014 > Configuration Tools > SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration > Protocols for MSSQLSERVER (where MSSQLSERVER corresponds to the instance name that you defined when you installed SQL Server).
  3. Locate TCP/IP.
  4. Double-click TCP/IP and enable it under the Protocol tab.
  5. Click the IP Addresses tab to enable the TCP port for each configured IP address.
In some situations, it is possible that you could observe the following lock timeout errors in Business Automation Workflow SystemOut.logs: 
Lock request time out period exceeded.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 
  Lock request time out period exceeded.
To help prevent a lock timeout from occurring (or to resolve a lock timeout after it has occurred), you can add the custom property ENABLE_XARESOURCE_TIMEOUT on the transaction service. The transaction service provides an appropriate timeout value to the XA resources when they are enlisted in global transactions. Information about the ENABLE_XARESOURCE_TIMEOUT custom property is found in the topic Transaction service custom properties External link opens a new window or tab .