jboss 7 AS datasource for sqlserver

Question

I run jboss in standalone mode and have set my datasource in the standalone.xml to the following:

<datasource jndi-name="MyDenaliDS" pool-name="MyDenaliDs_Pool" enabled="true" jta="true" 
                                                   use-java-context="true" use-ccm="true">
    <connection-url>
        jdbc:sqlserver://myip:1433;databaseName=mydb;integratedSecurity=true
    </connection-url>
    <driver>
        sqljdbc
    </driver>
    <security>
        <user-name>
            username
        </user-name>
        <password>
            password
        </password>
    </security>
</datasource>
<drivers>
    <driver name="sqljdbc" module="com.microsoft.sqlserver.jdbc">
        <driver-class>
            com.microsoft.sqlserver.jdbc.SQLServerDataSource
        </driver-class>
    </driver>                    
</drivers>

in the folder %jbosshome%\modules\com\microsoft\sqlserver\jdbc\ I have the sqljdb4.jar and the following module.xml:

<?xml version="1.0" encoding="UTF-8"?>
<module name="com.microsoft.sqlserver.jdbc" xmlns="urn:jboss:module:1.0">
    <resources>
        <resource-root path="sqljdbc4.jar"/> 
     </resources>
     <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/> 
     </dependencies>
</module>

When I start the jboss it gives me the following error:

> New missing/unsatisfied dependencies:    service
> jboss.jdbc-driver.sqljdbc (missing)

Anyone know what I've done incorrect or what I'm missing?

Phenacaine

Answer 1

got the same error like you before

instead of %jbosshome%\modules\com\microsoft\sqlserver\jdbc\

try %jbosshome%\modules\com\microsoft\sqlserver\jdbc\main

and put the .jar of the jdbc driver and the module.xml overthere.

Balzac

I did this and it still does not work for me in JBoss 7.0.2.Final – Atom

Answer 2

using Jboss AS 7.1.1, as well as putting the module in %jbosshome%\modules\com\microsoft\sqlserver\jdbc\main I had to make a slight change to the xml - the driver element in standalone.xml should be :

 <driver name="sqljdbc" module="com.microsoft.sqlserver.jdbc">
      <driver-class>
           com.microsoft.sqlserver.jdbc.SQLServerDriver
      </driver-class>
 </driver>

and the module.xml should be:

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

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

    <resources>
        <resource-root path="sqljdbc4.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.servlet.api" optional="true"/>
    </dependencies>

</module>

Mendicity

I'm still getting 14:55:59,408 INFO [org.jboss.as.controller] (Controller Boot Thread) Service status report New missing/unsatisfied dependencies: service jboss.jdbc-driver.sqljdbc (missing) – Atom

Cheers for that – it helped! The only thing I changed was the <driver> tag within the standalone.xml to be: <drivers> <driver name="sqlserver" module="com.microsoft.sqlserver.jdbc"/> </drivers> – Llywellyn

Answer 3

With Jboss 7.1.1 I managed to get it working without specifying module.

Just put sqljdbc4.jar into deployment directory: %JBOSSHOME%\standalone\deployments

Configuration needed for datasource in standalone.xml is as follows:

<datasource jndi-name="java:jboss/datasources/myPool" pool-name="myPool" enabled="true" use-java-context="true">
    <connection-url>jdbc:sqlserver://127.0.0.1:1433;databaseName=myName;</connection-url>
    <driver>sqljdbc4.jar</driver>
    <pool>
        <min-pool-size>10</min-pool-size>
        <max-pool-size>100</max-pool-size>
        <prefill>true</prefill>
    </pool>
    <security>
        <user-name>myUser</user-name>
        <password>myPassword</password>
    </security>
    <statement>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
        <share-prepared-statements>true</share-prepared-statements>
    </statement>
</datasource>

Imalda

Using the jar file name as driver name and skipping the <drivers> part also worked for me. The SQL Server driver is JDBC Type 4 compliant and it is sufficient to deploy it. – Psychoanalysis

Answer 4

I used this configuration and it worked OK on AS7

<datasource jndi-name="java:jboss/datasources/stagingDS" pool-name="stagingDS" enabled="true" use-java-context="true">
    <connection-url>jdbc:sqlserver://linp-sqlrpt-01;databaseName=pmdm</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <driver>sqlserver</driver>
    <security>
        <user-name>username</user-name>
        <password>password</password>
    </security>
 </datasource>

<drivers>
    <driver name="sqlserver" module="com.microsoft.jdbc">
        <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
    </driver>
</drivers>

Of course if you're not using the distributed DataSource you can configure the regular driver class as previously defined.

The module file looks like this.

<module xmlns="urn:jboss:module:1.1" name="com.microsoft.jdbc">
    <properties>
        <property name="jboss.api" value="private"/>
    </properties>

    <resources>
        <resource-root path="sqljdbc4.jar"/>
    </resources>

    <dependencies>
       <module name="javax.api"/>
       <module name="javax.transaction.api"/>
       <module name="javax.servlet.api" optional="true"/>
    </dependencies>
</module>

Asante

Answer 5

Deploying JDBC jar file (e.g. sqljdbc41.jar) by admin console should allow to select and use it when defining a new data source.

Clintclintock

in my case, JNDI name is shown in the error log module is not installed, test change jndi name, original name is still in error log, thus find the name in error log is related to another place in standalone.xml, so the jndi name is irrelevant of module install. – Probity

Answer 6

this is the work for me. in JBOSS 7, MSSQL JDBC driver 6, type 4, file name:sqljdbc42.jar

• Install the driver name:sqljdbc42.jar in [JBoss HOME]\modules\com\microsoft\main

• add under the same directory module.xml with content below

  <module xmlns="urn:jboss:module:1.1" name="com.microsoft">
  <resources>
  <resource-root path="sqljdbc42.jar"/>
  </resources>
  <dependencies>
  <module name="javax.api"/>
  <module name="javax.xml.bind.api"/>
  <module name="javax.transaction.api"/>
  </dependencies>
  </module>
  

• insert datasource definition to standalone.xml in block <subsystem xmlns="urn:jboss:domain:datasources:4.0"> <datasources> as below

  <datasource jta="true" jndi-name="java:jboss/datasources/iwebDS" pool-name="MSSQLDS" enabled="true" use-ccm="true">
  <connection-url>jdbc:sqlserver://172.24.17.52:1803;DatabaseName=IWEB_IWEBSIT</connection-url>
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  <driver>sqlserver2008</driver>
  <pool>
  <min-pool-size>10</min-pool-size>
  <initial-pool-size>10</initial-pool-size>
  <max-pool-size>100</max-pool-size>
  <flush-strategy>FailingConnectionOnly</flush-strategy>
  </pool>
  <security>
  <user-name>[sqlserver user]</user-name>
  <password>[sqlserver password]</password>
  </security>
  <validation>
  <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
  <background-validation>true</background-validation>
  </validation>
  </datasource>
  

• insert driver definition to standalone.xml in block <subsystem xmlns="urn:jboss:domain:datasources:4.0"> <drivers> as below

  <driver name="sqlserver2008" module="com.microsoft">
  <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
  </driver>
  

ps: "sqlserver2008" is the name as you wish.

Toscanini