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

active-directory, java, jboss, jdbc, sql-server

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:

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

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

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>

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>

Problem

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: - 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 - changing JBoss to run as `ABC\dbuser` or - 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?

Original source