Instructions for enabling XA transactions on Sql Server

book

Article ID: 49444

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) CA Workload Automation Agent CA Process Automation Base

Issue/Introduction

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.

    <Please see attached file for image>

    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.

    <Please see attached file for image>

    Figure 2


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

    <Please see attached file for image>

    Figure 3


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

    <Please see attached file for image>

    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.

    <Please see attached file for image>

    Figure 5


  • Select the Security tab and select Enable XA Transactions.

    <Please see attached file for image>

    Figure 6


For Windows 2003

  • Navigate to Administrative Tools, Component Services.


  • Right-click My Computer and select Properties.

    <Please see attached file for image>

    Figure 7


  • Click the MSDTC tab.

    <Please see attached file for image>

    Figure 8


  • Click the Security Configuration button under Transaction Configuration.

    <Please see attached file for image>

    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.

      <Please see attached file for image>

      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.

    <Please see attached file for image>

    Figure 11

    <Please see attached file for image>

    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'

    <Please see attached file for image>

    Figure 13

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

    <Please see attached file for image>

    Figure 14

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

    <Please see attached file for image>

    Figure 15

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

Environment

Release:
Component: ITPAM

Attachments

1558712457855000049444_sktwi1f5rjvs16sm3.gif get_app
1558712456081000049444_sktwi1f5rjvs16sm2.gif get_app
1558712454211000049444_sktwi1f5rjvs16sm1.gif get_app
1558712452274000049444_sktwi1f5rjvs16sm0.gif get_app
1558712450309000049444_sktwi1f5rjvs16slz.gif get_app
1558712448169000049444_sktwi1f5rjvs16sly.gif get_app
1558712444636000049444_sktwi1f5rjvs16slx.gif get_app
1558712442595000049444_sktwi1f5rjvs16slw.gif get_app
1558712440277000049444_sktwi1f5rjvs16slv.gif get_app
1558712438305000049444_sktwi1f5rjvs16slu.gif get_app
1558712436369000049444_sktwi1f5rjvs16slt.gif get_app
1558712434421000049444_sktwi1f5rjvs16sls.gif get_app
1558712432768000049444_sktwi1f5rjvs16slr.gif get_app
1558712430671000049444_sktwi1f5rjvs16slq.gif get_app
1558712428913000049444_sktwi1f5rjvs16slp.gif get_app