如何在JBoss中配置SQL Server数据源以使用特定的Active Directory用户进行连接？

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.

JBoss作为Active Directory用户ABC \ appuser运行。我想以AD用户ABC \ dbuser连接到MS SQL Server 8.0数据库。使用参数integratedSecurity = true,除非我在连接URL上指定user = ABC \ dbuser; password = dbpass,否则系统将尝试连接为服务AD用户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:

根据这个问题,我已经确认通过使用以下url,当以ABC \ appuser运行应用程序时,我可以作为ABC \ dbuser连接到数据库:

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:

不幸的是,当我在JBoss配置xml(JBoss \ jboss-eap-6.1.0 \ standalone \ configuration \ standalone.xml)中设置数据源的url时,如下所示:

<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.

设置用户名和密码XML条目的值会创建类似的故障警告。

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

扩展JBoss用于创建此数据源的任何类,将其替换为按预期应用connection-url值的自定义类或

changing JBoss to run as ABC\dbuser or

将JBoss改为ABC \ dbuser或

giving the JBoss service user ABC\appuser database access by either giving it direct access or adding it to an AD Group with access.

通过直接访问或将其添加到具有访问权限的AD组,为JBoss服务用户ABC \ appuser数据库访问提供访问权限。

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

这些变通办法都不是优选的;必须有一个更优雅,被接受的解决方案。我该如何解决这个问题?

2 个解决方案

#1

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.

我很惊讶连接字符串正在工作。我的理解是,Microsoft提供的JDBC驱动程序中的integratedSecurity属性与等效.NET连接字符串中的Integrated Security或Trusted Connection属性的工作方式相同。

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.

也就是说,将integratedSecurity设置为true会使JDBC驱动程序有效地忽略所提供的用户和密码,并尝试以应用程序运行的用户身份登录。

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.

我没有Microsoft SQL Server驱动程序的解决方案,但可以使用开源jTDS JDBC驱动程序解决此问题。

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:

对于大部分,您应该能够交换JDBC驱动程序JAR文件并调整连接XML,如下所示:

<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.

根据要连接的SQL Server的配置,您可能还需要将useNTLMv2 = true添加到连接URL。

i.e. the entire connection URL would be:

即整个连接URL将是:

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.

编辑:不幸的是,在JBoss EAP版本中,您的目标是添加新的JDBC驱动程序并不像在正确的位置放置jar那么容易。

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

以下是添加新JDBC驱动程序的相当繁琐的说明:

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

创建文件夹JBOSS_HOME \ modules \ net \ sourceforge \ jtds \ main
Copy the file jtds-1.3.1.jar into the folder.

将文件jtds-1.3.1.jar复制到该文件夹中。

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

在包含以下内容的文件夹中创建名为module.xml的文件

<?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)

将以下XML添加到standalone.xml(修改drivers元素以添加驱动元素(如果已存在))
```
<drivers>
  <driver name="jtds" module="net.sourceforge.jtds">
    <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
  </driver>
</drivers>
```

#2

First, I'm not even sure how the connection string you're using is working at all. If you specify trusted connection and a user/pass combo an error should be returned since using both is not possible. You either want to connect using the current account context or a specific username/password combo, not both. Even so, SQL Server does not store AD passwords nor will it authenticate a user/pass combo as anything else but a SQL Server Login.

首先,我甚至不确定你使用的连接字符串是如何工作的。如果指定了可信连接和用户/通过组合,则应返回错误,因为无法使用这两者。您要么使用当前帐户上下文或特定用户名/密码组合进行连接,而不是两者。即便如此,SQL Server也不存储AD密码,也不会将用户/传递组合验证为除SQL Server登录之外的任何其他内容。

Secondly, I'm not sure you really mean SQL Server 8.0 RC@ as RC2 would mean release candidate 2 of version 8.0 which was SQL Server 2000. If so, that is not supported at all and I would suggest migrating to 2012 or 2014.

其次,我不确定你的意思是SQL Server 8.0 RC @,因为RC2意味着发布版本8.0的候选版本2是SQL Server 2000.如果是这样,那根本就不支持,我建议迁移到2012或2014。

Now, I'm not entirely well versed in JBoss but you have (as I see it) two different options.

现在,我并不完全精通JBoss,但你有(我认为)两种不同的选择。

Somehow, inside of JBoss when a connection to this datasource is going to be attempted the context is impersonated from the ABC\AppUser account to the ABC\DBUser account. This would need to be done inside of JBoss, unfortunately I do not believe that to be possible (though I'm not positive).

不知何故,在JBoss内部将要尝试连接到此数据源时,上下文将从ABC \ AppUser帐户模拟到ABC \ DBUser帐户。这需要在JBoss内部完成,不幸的是我不相信这是可能的(虽然我不是积极的)。
Give connect and impersonation permissions (only for the ABC\DBUser login) to the ABC\AppUser account inside of SQL Server. Use the JBoss datasource configuration item new-connect-sql to run the impersonation command to become ABC\DBUser inside of SQL Server (EXECUTE AS LOGIN ='ABC\DBUser' WITH NO_REVERT).

将连接和模拟权限(仅限ABC \ DBUser登录)授予SQL Server内的ABC \ AppUser帐户。使用JBoss数据源配置项new-connect-sql运行impersonation命令,使其成为SQL Server内的ABC \ DBUser(EXECUTE AS LOGIN ='ABC \ DBUser'WITH NO_REVERT)。

#1