Instructions for enabling XA transactions on Sql Server

Document ID : KB000049444
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

New in ITPAM 4.0, the JDBC drivers that are used must support XA distributed transactions. Sql Server is not by default. The following is a supplement to the install guide to walk through enabling XA transactions on Sql Server.

Solution:

Here are the instructions to enable XA distributed transactions:

These changes require a Database Server restart. Coordinate with other users of the Database Server and complete the changes in a maintenance window.

  1. Obtain a copy of the XA driver. The XA driver is on CA Process Automation DVD1 in the folder thirdparty\mssql, or you can download Microsoft SQL Server JDBC Driver 3.0 directly from Microsoft then extract the file into a scratch directory.

    Figure 1

  2. Navigate to the sqljdbc_3.0\enu\xa folder and copy the sqljdbc_xa.dll file from either the x64 or x86 folder (based on the system architecture of the database server machine where MS SQL Server is installed) into the <mssql_install_location>\MSSQL.1\MSSQL\Bin folder.

  3. Create a non-'sa' account for CA Process Automation to use to access its internal databases.

    1. Log in to the SQL Management Studio.


  4. Create a user (for example, pamxauser) and assign master as the default database.

    Figure 2


  • In the User Mappings, verify that the public database role is assigned to the master database.

    Figure 3


  • In the Server Roles, verify that dbcreator is selected.

    Figure 4


  • Click OK.


  • Enable XA transactions for Distributed Transaction Coordinator.

    For Windows 2008

    • Navigate to Administrative Tools, Component Services.


  • Expand Distributed Transactions and open the Local DTC properties.

    Figure 5


  • Select the Security tab and select Enable XA Transactions.

    Figure 6


For Windows 2003

  • Navigate to Administrative Tools, Component Services.


  • Right-click My Computer and select Properties.

    Figure 7


  • Click the MSDTC tab.

    Figure 8


  • Click the Security Configuration button under Transaction Configuration.

    Figure 9


  • In the Security Configuration window, select Enable XA Transactions.


  • Click OK, Click OK, then select File, Exit.


  • Open the MS SQL Server client (Management Studio) as user 'sa'.

    • Select File, Open, File and then browse from the folder from Step 2 to the xa_install.sql script under sqljdbc_3.0\enu\xa.

      Figure 10


  • Click Execute to run the script and load the DLL.

    Note: If a permissions message similar to the following appears, ignore the message:
    Msg 3701, Level 16, State 15, Procedure sp_dropextendedproc, Line 18 Cannot drop the procedure 'xp_sqljdbc_xa_init', because it does not exist or you do not have permission.

    Figure 11

    Figure 12

  • Run the following SQL commands, replacing pamxauser with the user name you used:

    use master
    go
    exec sp_grantdbaccess 'pamxauser'
    go
    exec sp_addrolemember [SqlJDBCXAUser],'pamxauser'

    Figure 13

    Note: An error message that the user exists opens. Ignore this message.

    Figure 14

  • Verify that the SqlJDBCXAUser role is checked for the pamxauser user for the master database, then exit Management Studio.

    Figure 15

  • Restart your MS SQL Server Service.
  • You are now ready to install ITPAM 4.0