Installing SQL Server drivers/data sources for EAP 7.3 to use with RedHat JBPM

[David Goldstein]

I have been testing out JBPM under EAP 7.3 and need to change the database from H2 to Microsoft SQL Server.  I pulled mssql-jdbc-8.4.1.jre8.jar out of sqljdbc_8.4.1.0_enu.zip on the Microsoft's SQL Server page for the driver.  I have tried to use the JBOSS CLI and the Administrative console's database/datasource menus to install the driver, but what seems to have gotten me closest to working is installing the module.xml and driver files manually as well as editing standalone-full.xml.  The log indicates being unable to log on, but I think this is a data source configuration issue. I have substituted {label} for sensitive data below, but the credentials are from my login with Microsoft Server Management Studio.  We use the default ports.

10:51:47,029 INFO  [org.kie.workbench.common.screens.datasource.management.backend.DataSourceManagementBootstrap] (pool-26-thread-1) Initialize deployments task finished successfully.

10:51:49,882 ERROR [org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl] (Periodic Recovery) IJ000906: Error during crash recovery: java:/jboss/jbpmdsjndi (IJ031084: Unable to create connection): javax.resource.ResourceException: IJ031084: Unable to create connection

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.getXAManagedConnection(XAManagedConnectionFactory.java:531)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1$1.run(XAManagedConnectionFactory.java:417)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1$1.run(XAManagedConnectionFactory.java:414)

        at java.security.AccessController.doPrivileged(Native Method)

        at javax.security.auth.Subject.doAs(Subject.java:422)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:413)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory$1.run(XAManagedConnectionFactory.java:410)

        at java.security.AccessController.doPrivileged(Native Method)

        at org.jboss.jca.adapters.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:409)

        at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.open(XAResourceRecoveryImpl.java:355)

        at org.jboss.jca.core.tx.jbossts.XAResourceRecoveryImpl.getXAResources(XAResourceRecoveryImpl.java:193)

        at com.arjuna.ats.internal.jbossatx.jta.XAResourceRecoveryHelperWrapper.getXAResources(XAResourceRecoveryHelperWrapper.java:51)

        at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.resourceInitiatedRecoveryForRecoveryHelpers(XARecoveryModule.java:603)

        at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:221)

        at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:171)

        at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:770)

        at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:382)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '{security domain}/{user}'. ClientConnectionId:584b9635-fe0d-4d11-88f9-ad132bb67645

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)

        at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:283)

        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:129)

        at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:5233)

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:3988)

This is from my standalone-full.xml:

              <xa-datasource jndi-name="java:/jboss/jbpmdsjndi" pool-name="jbpm2"  enabled="true" use-java-context="true" > 

                    <xa-datasource-property name="ServerName">{correct server info}</xa-datasource-property>

                    <xa-datasource-property name="DatabaseName">{correct database name}</xa-datasource-property>

                    <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                    <driver>sqlserver</driver>  <!-- tried mssql-jdbc-8.4.1.jre8.jar - cannot login -->

                    <security>

                        <user-name>{domain}</user-name>

                        <password>{password}</password>

                    </security>

                    <validation>

                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>

                        <background-validation>true</background-validation>

                    </validation>

                </xa-datasource>

                <drivers>

    <!-- h2 driver is here -->

        <driver name="sqlserver" module="com.microsoft.sqlserver.jdbc">

                        <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                    </driver>

                </drivers>

             </datasources>

 

This is the module.xml:

<module xmlns="urn:jboss:module:1.3" name="com.microsoft.sqlserver.jdbc">

    <resources>

        <resource-root path="mssql-jdbc-8.4.1.jre8.jar"/>

    </resources>

    <dependencies>

        <module name="javax.api"/> 

        <module name="javax.transaction.api"/> 

    </dependencies>

</module>

This is the directory structure with the driver under sqlserver/jdbc/main and some clould stuff under azure/storage/main:

PS C:\DGGInstalls\RHPAM\jboss-eap-7.3\modules\system\layers\base\com\microsoft> tree                                                                                                                                                                                        Folder PATH listing

Volume serial number is 3837-1C7A

C:.

├───azure

│   └───storage

│       └───main

└───sqlserver

    └───jdbc

        └───main

PS C:\DGGInstalls\RHPAM\jboss-eap-7.3\modules\system\layers\base\com\microsoft\sqlserver\jdbc\main> ls                                                                                                                                                                      

    Directory: C:\DGGInstalls\RHPAM\jboss-eap-7.3\modules\system\layers\base\com\microsoft\sqlserver\jdbc\main

Mode                LastWriteTime         Length Name

----                -------------         ------ ----

-a----         9/4/2020   2:46 PM           1394 module.xml

-a----         9/3/2020   4:23 PM        1300917 mssql-jdbc-8.4.1.jre8.jar

I welcome any advice you might have on my logging into SQL Server from the RedHat version of JBPM running on EAP!

[David Goldstein]

Got this somewhat solved.  There is one set of JARs to access SQL Server accounts - that works.  There is another set for accessing the database via the Windows Logon - still working on that.  The JARs for windows logon has an odd naming convention, going from version 68118 (or something like that) to 0.1.  Also, there are configuration files showing for the libraries in .M2, but the files aren't there - they seem to e available then unavailable to the build!

[David Goldstein]

Got this to run.  Not deleting it so that others can learn from the lessons.

To run JDBC under SQL Server you need to not only load the JDBC drivers but two DLL's need to be added to the server.  Otherwise, you will see authentication problems.

Then you need to configure Hibernate for SQLServer 2008 dialect (which I used) or 2012 dialect.  There are many configuration files that refer to the H2 dialect, inside Business Central, in the source code, in test code and in configuration directories.  Due to the quantity, I did a "global find and replace" and watched each change.