Enabling MS DTC for XA transactions

Enable the Microsoft Distributed Transaction Coordinator (MS DTC) for XA transactions and make sure that the MS DTC service is marked as Automatic in Service Manager so it is running when the SQL Server service is started.

  • Click the Start button, type “dcomcnfg” in the Search field, and then press ENTER to open Component Services:

    Figure 529: Component services – overview

  • Expand Component Services>Computers>My Computer>Distributed Transaction Coordinator:

    Figure 530: Component services – distributed transaction Coordinator

  • Right click Local DTC and then select Properties.

  • Click the Security tab on the Local DTC Properties dialog box.

  • Select the Enable XA Transactions check box, and then click OK:

    Figure 531: Component services – local DTC properties 

  • Enabling XA transactions will cause a MS DTC service restart. Click Yes in the dialog, to confirm the restart:

    Figure 532: MS DTC service restart confirmation dialog

  • Click OK again to close the Properties dialog box, and then close Component Services console.

  • Stop and then restart SQL Server (using Management Studio), to make sure that it syncs up with the MS DTC changes. Note that the SQL Server instance restart requires a login with administrator rights (either SQL Sever account “sa” or Administrator account via Windows Authentication in the SQL Server).

Execute the database script “xa_install.sql” as an administrator of the SQL Server instance on every SQL Server instance that will participate in XA transactions. 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 JDBC Driver for SQL Server.

To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role (see step Enable membership in Manual database setup with Microsoft SQL Server Management Studio for related information).

You can configure only one version of the “sqljdbc_xa.dll” assembly on each SQL Server instance at a time. Applications may need to use different versions of the JDBC driver to connect to the same SQL Server instance by using the XA connection. In that case, “sqljdbc_xa.dll”, which comes with the newest JDBC driver, must be installed on the SQL Server instance.

To verify the version of “sqljdbc_xa.dll” that is currently installed on the SQL Server instance, open the “Binn” directory of the SQL Server computer that will participate in distributed transactions. Select the sqljdbc_xa.dll assembly and open the tab “Details” in the properties window:

Figure 533: File version of “sqljdbc_xa.dll”

Side note

The classes for the distributed transaction implementation are as follows:

  • com.microsoft.sqlserver.jdbc.SQLServerXADataSource:

    class factory for distributed connections (implements javax.sql.XADataSource)

  • com.microsoft.sqlserver.jdbc.SQLServerXAResource

    resource adapter for the transaction manager (implements javax.transaction.xa.XAResource)