An XA transaction is a  global transaction usually covering multiple resources.

Use  the  Microsoft SQL Server JDBC Driver to support the   Java Platform based distributed transactions

Steps to enable XA transactions

1)  Enable MSDTC

On Windows 2003 > Start > Run > “dcomconfg” > Expand Component Services > Expand Computers > Right -click  My Computer > Click Properties > Click MSDTC tab > Click Security Configuration > Check the “Enable XA Transactions”

Use MS DTC and sys.dm_tran_active_transactions  to monitor the state of the MSDTC transactions

2) Install  SQLJDBC_XA.dll 

Prior to running this script  copy the extended stored procedure dll SQLJDBC_XA.dll  to the target SQL Server's Binn folder.  Restart the SQL Server Instance

3) Create a role  and Attach the role to relevant users

Permissions to the distributed transaction support procedures for the Microsoft SQL Server 2005  JDBC Driver are granted through the SQL Server role [SqlJDBCXAUser].  Maintain a secure default configuration, no user is granted access to this role by default. Ensure the actions you execute are compatible with your SQL Server Security Policy

use master
go

-- Drop any existing procedure definitions.
exec sp_dropextendedproc 'xp_sqljdbc_xa_init' 
exec sp_dropextendedproc 'xp_sqljdbc_xa_start'
exec sp_dropextendedproc 'xp_sqljdbc_xa_end'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare'
exec sp_dropextendedproc 'xp_sqljdbc_xa_commit'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget'
exec sp_dropextendedproc 'xp_sqljdbc_xa_recover'
exec sp_dropextendedproc 'xp_sqljdbc_xa_rollback_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_forget_ex'
exec sp_dropextendedproc 'xp_sqljdbc_xa_prepare_ex'
go

-- Install the procedures.
exec sp_addextendedproc 'xp_sqljdbc_xa_init', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_start', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_end', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_commit', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_recover', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_rollback_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_forget_ex', 'SQLJDBC_XA.dll'
exec sp_addextendedproc 'xp_sqljdbc_xa_prepare_ex', 'SQLJDBC_XA.dll'
go
use master 
GO
sp_addrole [SqlJDBCXAUser]
go

-- Grant privileges to [SqlJDBCXAUser] role to the extended stored procedures.
grant execute on xp_sqljdbc_xa_init to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_start to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_end to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_commit to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_recover to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_rollback_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_forget_ex to [SqlJDBCXAUser]
grant execute on xp_sqljdbc_xa_prepare_ex to [SqlJDBCXAUser]
go