How do I configure a SQL Server datasource in JBoss to connect using a specific Active Directory user?

Question

JBoss runs as Active Directory user ABC\appuser. I want to connect to a MS SQL Server 8.0 database as AD user ABC\dbuser. Using parameter integratedSecurity=true, unless I specify user=ABC\dbuser;password=dbpass on the connection url, the system will try to connect as the service AD user, ABC\appuser.

Per this question, I have confirmed that by using the following url, I can connect to the database as ABC\dbuser when running the application as ABC\appuser:

jdbc:sqlserver://MYHOSTNAME:1433;DatabaseName=MyDatabaseName;integratedSecurity=true;user=ABC\dbuser;password=dbpass

Unfortunately, when I set the url for the datasource in the JBoss configuration xml ( JBoss\jboss-eap-6.1.0\standalone\configuration\standalone.xml ) as follows:

<datasource jndi-name="java:jboss/datasources/MyDatabaseName" pool-name="MyPoolName" enabled="true" use-java-context="true">
  <connection-url>jdbc:sqlserver://MYHOSTNAME:1433;DatabaseName=MyDatabaseName;integratedSecurity=true;user=ABC\dbuser;password=dbpass</connection-url>
  <driver>sqlserver</driver>
  <pool>
    <min-pool-size>1</min-pool-size>
    <max-pool-size>10</max-pool-size>
    <prefill>true</prefill>
  </pool>
  <security>
    <user-name></user-name>
    <password></password>
  </security>
</datasource>

I am unable to create the pool resource with this warning:

WARN  
[org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (JCA PoolFiller) 
IJ000610: Unable to fill pool: 
javax.resource.ResourceException: Could not create connection

Setting values for user-name and password XML entries creates a similar failure warning.

My current workaround options seem to be any of:

1. extending whichever class JBoss is using to create this datasource, replacing it with a custom class that applies the connection-url value as expected or
2. changing JBoss to run as ABC\dbuser or
3. giving the JBoss service user ABC\appuser database access by either giving it direct access or adding it to an AD Group with access.

None of these workarounds is preferable; there must be a more elegant, accepted solution. How can I resolve this?

Solution

I'm surprised the connection string is working. My understanding was that the integratedSecurity property in the Microsoft provided JDBC driver worked the same way as the Integrated Security or Trusted Connection properties in the equivalent .NET connection string.

That is to say that setting integratedSecurity to true makes the JDBC driver effectively ignore the user and password provided and attempt to login as the user that the application is running as.

I don't have a solution with the Microsoft SQL Server driver but this problem can be solved using the open source jTDS JDBC Driver.

For the large part you should be able to swap out the JDBC driver JAR file and tweak the connection XML to look something like this:

<datasource jndi-name="java:jboss/datasources/MyDatabaseName" pool-name="MyPoolName" enabled="true" use-java-context="true">
  <connection-url>jdbc:jtds:sqlserver://MYHOSTNAME:1433/MyDatabaseName;domain=ABC</connection-url>
  <driver>jtds</driver>
  <pool>
    <min-pool-size>1</min-pool-size>
    <max-pool-size>10</max-pool-size>
    <prefill>true</prefill>
  </pool>
  <security>
    <user-name>dbuser</user-name>
    <password>dbpass</password>
  </security>
</datasource>

Depending on the configuration of the SQL Server you are connecting to you may need to also add useNTLMv2=true to the connection URL.

i.e. the entire connection URL would be:

jdbc:jtds:sqlserver://MYHOSTNAME:1433/MyDatabaseName;domain=ABC;useNTLMv2=true

EDIT: Unfortunately in the version of JBoss EAP you're targeting adding a new JDBC driver isn't as easy as dropping the jar in the right place.

Here are the rather cumbersome instructions for adding the new JDBC driver:

1. Create the folder JBOSS_HOME\modules\net\sourceforge\jtds\main

2. Copy the file jtds-1.3.1.jar into the folder.

3. Create a file named module.xml in the folder with the following contents

   <?xml version="1.0" encoding="UTF-8"?>
   <module xmlns="urn:jboss:module:1.0" name="net.sourceforge.jtds">
     <resources>
       <resource-root path="jtds-1.3.1.jar"/>
     </resources>
     <dependencies>
       <module name="javax.api"/>
       <module name="javax.transaction.api"/>
     </dependencies>
   </module>
   

4. Add the following XML to standalone.xml (modify the drivers element to add the driver element if it already exists)

   <drivers>
     <driver name="jtds" module="net.sourceforge.jtds">
       <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
     </driver>
   </drivers>
   

Comments

[JoshDM]

This worked, but we did end up going (my preferred) "workaround" route and adding the AD user the service runs as, "appuser" to an AD group with access to the SQL Server database. This is fine, as the Service AD user "appuser" was the only consumer of the DB AD user, "dbuser".