Services with missing/unavailable dependencies EAP6.4

[jaspersunshine]

Hey guys, I will try to provide as many details as possible to to you hoping that the issue can be resolved sometimes soon.

Setup:

1. JBoss EAP 6.4 running on one virtual machine
2. Microsoft SQL server developer edition 2014 running on a different virtual machine.
   1. TCP/IP is enabled thru Sql Server Configuration Manager
   2. I have added an database called example owned by User prototype with password goforit and authentication mode is set to SQL Server Authentication

1. We originally use built-in H2 database for testing and now would like to test it with ms SQL server which is closer to our final setup.

persistence.xml file:

<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="http://java.sun.com/xml/ns/persistence"

        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence

          http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

        version="2.0">

  <persistence-unit name="obps" transaction-type="JTA">

      <description>Forge Persistence Unit</description>

      <provider>org.hibernate.ejb.HibernatePersistence</provider>

      <jta-data-source>java:jboss/datasources/ExampleDSExternal</jta-data-source>

      <class>com.obps.cpm.impl.beams.BeamConfig</class>

      <class>com.obps.cpm.impl.beams.BeamInfo</class>

      <class>com.obps.cpm.impl.beams.BeamInfoKey</class>

      <class>com.obps.cpm.impl.beams.BeamVertex</class>

      <class>com.obps.cpm.impl.beams.BeamVertexKey</class>

      <class>com.obps.cpm.impl.beams.ElementWeight</class>

      <class>com.obps.cpm.impl.beams.ElementWeightKey</class>

      <class>com.obps.cpm.impl.beams.InterbeamGain</class>

      <class>com.obps.cpm.impl.beams.InterbeamGainKey</class>

      <class>com.obps.cpm.impl.ephem.EphemRecord</class>

      <class>com.obps.cpm.impl.ephem.EphemReport</class>

      <!--  <exclude-unlisted-classes>false</exclude-unlisted-classes>-->

      <properties>

          <!-- Use option "update" so that data from previous runs remains in database /-->

          <property name="hibernate.hbm2ddl.auto" value="update"/>

          <!--property name="hibernate.hbm2ddl.auto" value="create"/-->

          <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2012Dialect"/>

          <property name="hibernate.show_sql" value="true"/>

          <!--  property name="javax.persistence.schema-generation.database.action" value="drop-and-create"/>-->

      </properties>

  </persistence-unit>

</persistence>

data source file not working (MS SQL)

mssql-ds.xml:

<?xml version="1.0" encoding="UTF-8"?>

<datasources>

     <datasource

          jndi-name="java:jboss/datasources/ExampleDSExternal"

          enabled="true"

          use-java-context="true" pool-name="ExampleDSExternal">

          <connection-url>jdbc:sqlserver://10.10.10.25:1443;DatabaseName=Example</connection-url>

          <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

          <security>

               <user-name>prototype</user-name>

               <password>goforit</password>

     </security>

     </datasource>

</datasources>

The below data source file is for H2 database configuration and everything works.

<?xml version="1.0" encoding="UTF-8"?>

<datasources xmlns="http://www.ironjacamar.org/schema/datasources_1_0.xsd">

    <datasource

        jndi-name="java:jboss/datasources/ExampleDSExternal"

        enabled="true"

        use-java-context="true" pool-name="ExampleDSExternal">

        <connection-url>jdbc:h2:tcp://10.10.10.22/~/test</connection-url>

        <driver>h2</driver>

        <security>

            <user-name>sa</user-name>

            <password>sa</password>

        </security>

    </datasource>

    <drivers>

    <driver name='h2' module="com.h2database.h2">

    <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

    </driver>

    </drivers>

</datasources>

The error output when trying to deploy the application on the server

"{\"JBAS014771: Services with missing/unavailable dependencies\" => [\"jboss.persistenceunit.\\\"OBPS_CPM.war#obps\\\" is missing [jboss.naming.context.java.jboss.datasources.ExampleDSExternal]\"]}"

I am suspecting that either the data source xml file configuration is not correct, or the access to the developer edition of SQL server is not set up properly because I do not know the ins and outs of it. So my question is, is there any issue with my data source file or the persistence.xml file? or do you think I have not set up the SQL server properly to be connected externally? For example, should I choose windows authentication or the SQL server authentication and how does it affect my data source configuration?

I also learnt recently that you can configure the connection to the database thru standalone.xml file. What are the pros/cons of doing it as opposed to the approach I am using right now??

Sorry to bombard you with questions and hopefully I can resolve the issue soon. Thank you!

[jaspersunshine]

Bump can anyone provide some help please?

[jaspersunshine]

Hey guys, a quick update. I tested the connection to the SQL server instance from my application server and the connection was successful, which means I can rule out the possibility that it is a SQL server configuration error.

Thanks

[weishinpchen]

Have you tried turning it off and on?

[mqpaul]

The *-ds.xml files are not used in Jboss eap 6 and 7 anymore. So you have to add the data source (and jdbc driver) to the configuration xml. If you use the standalone.xml you add it there. Rename, remove or comment out the H2 datasource definition.

I prefer to apply all changes on the configuration by cli scripts. I do not have a mssql database example for you but for Oracle.

NOTE. I remove existing definitions first to avoid error messages rerunning a cli script

NOTE. You have to copy the jdbc driver in de modules directory tree and provide a module.xml, see Administration and Configuration Guide

NOTE you can also deploy a jdbc driver

#### driver.cli #####

if (outcome == success) of /subsystem=datasources/jdbc-driver=oracle:read-resource

   /subsystem=datasources/jdbc-driver=oracle:remove

end-if

/subsystem=datasources/jdbc-driver=oracle:add(driver-module-name=com.oracle.ojdbc, driver-name=oracle, driver-xa-datasource-class-name=oracle.jdbc.xa.client.OracleXADataSource)

exit

#### datasource.cli ####

if (outcome == success) of /subsystem=datasources/data-source=EXAMPLE:read-resource

   /subsystem=datasources/data-source=EXAMPLE:remove

end-if

/subsystem=datasources/data-source=EXAMPLE:add( \

    jndi-name="java:/ExampleDS", \

    connection-url=jdbc:oracle:thin:@myhost.intern:1520:EXAMPLE, \

    user-name=example, \

    password="${VAULT::EXAMPLE::password::1}", \

    use-java-context=false, \

    check-valid-connection-sql="select 1 from dual", \

    min-pool-size=2, \

    max-pool-size=50, \

    driver-name=oracle, \

    flush-strategy=FailingConnectionOnly)

#Enable

/subsystem=datasources/data-source=EXAMPLE:enable

#reload server to apply changes

/:reload

[jaspersunshine]

Hey Paul I was able to get it to work last night. I will post my solution one of these days.

But essentially things I missed are:

1. I didnt know that one has to install the vendor specific JDBC driver as a core module. (I thought I can just drop the jar file in the build path and Voila it would work).

2. Like you point out, looks like data source are all being configured in the standalone.xml or domain.xml based on deployment scheme.

3. My connection url link is not correct, the port number should be 1433 instead of 1443.

Some additional questions, are there any good documents I can read up for further understanding of relationship between persistence.xml, -ds.xml, and standalone.xml? Can I still use -ds.xml if I choose to and what will be the disadvantage of using it (other than having a separate file)?

Thanks again!