STEP 1:
a. Delete the existing non-XA datasources and re-add them as XA datasources.This can be done via the JBoss Administration Console> Configuration > Datasources or via CLI command steps.
An example of using CLI command steps is given below. They also include the addition and registration of the Microsoft SQL Server JDBC driver as a core module which would have been previously done via core module addition or via jar file deployment, in order to be able to create the non-XA datasources in the first instance.
From the Red Hat JBoss 7.1 documentation see section "12.15.8. Example Microsoft SQL Server XA Datasource" to use CLI (file jboss-cli.bat(.sh)) to create the SQL Server XA datasources:
=== Add Microsoft SQL Server JDBC driver as core module and register it ===
module add --name=com.microsoft --resources=C:/PROGRA~1/MICROS~1.4FO/sqljdbc_7.4/enu/mssql-jdbc-7.4.1.jre8.jar --dependencies=javax.api,javax.transaction.api,javax.xml.bind.api
/subsystem=datasources/jdbc-driver=sqlserver:add(driver-name=sqlserver,driver-module-name=com.microsoft,driver-xa-datasource-class-name=com.microsoft.sqlserver.jdbc.SQLServerXADataSource)
===
=== Add a Microsoft SQL Server XA datasource for DB1 and DB2 (these examples use the SQL Server "sa" account) ===
xa-data-source add --name=DB1 --jndi-name=java:/DB1 --driver-name=sqlserver --user-name=sa --password=sa_password --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"hostname","DatabaseName"=>"DB1","SelectMethod"=>"cursor"}
xa-data-source add --name=DB2 --jndi-name=java:/DB2 --driver-name=sqlserver --user-name=sa --password=sa_password --validate-on-match=true --background-validation=false --background-validation=false --valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker --exception-sorter-class-name=org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLExceptionSorter --same-rm-override=false --xa-datasource-properties={"ServerName"=>"hostname","DatabaseName"=>"DB2","SelectMethod"=>"cursor"}
===
b. From JBoss Administration Console> Configuration > Datasources use "Test Connection" for the new XA datasources DB1 & DB2 and verify they are successful.
Also check the JBoss server.log, because although the connection is reported as successful, there may a SQL Server stored procedure error reported:
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'.", XAException.XAER_RMERR
If such an error exists the Gen EJB transaction will also likely fail to to connect to the first database DB1 with the same original error code IJ031070 i.e.
===
TIRM170E: An error was encountered on the server
BaseDBMSManager.getStatement() caught ABException getting connection for: DB1 Exception: com.ca.gen.abrt.ABException: [Function: DataSourceDBMSManager.connect]Error attempting to determine if transactions are supported: java.sql.SQLException: IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK
===
Resolution
This warning message indicates that there is some configuration required on SQLServer in order for it to support XA transaction recovery. Many databases do not support XA recovery out of the box and must be manually configured to enable such support. You have to contact the vendor support for more information on how to perform the necessary configuration.
Root Cause
XA datasource is configured in JBoss but XA is not supported on database side. Check this article Understanding XA Transactions from Microsoft MSDN for more details.
===
After those steps are complete, the JBoss Test Connection step for the datasources should show no errors in the JBoss server.log file and the Gen EJB transaction execution against both databases should also then be successful.