Java 如何在 JBoss 中配置 SQL Server 数据源以使用特定的 Active Directory 用户进行连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24704742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I configure a SQL Server datasource in JBoss to connect using a specific Active Directory user?
提问by JoshDM
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=dbpasson the connection url, the system will try to connect as the service AD user, ABC\appuser.
JBoss 以 Active Directory 用户身份运行ABC\appuser。我想以 AD 用户身份连接到 MS SQL Server 8.0 数据库ABC\dbuser。使用参数integratedSecurity=true，除非我user=ABC\dbuser;password=dbpass在连接 url 上指定，否则系统将尝试以服务 AD 用户的身份连接ABC\appuser。
Per this question, I have confirmed that by using the following url, I can connect to the database as ABC\dbuserwhen running the application as ABC\appuser:
根据这个问题，我已经确认通过使用以下网址，我可以像ABC\dbuser运行应用程序一样连接到数据库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:
不幸的是，当我在 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-nameand passwordXML entries creates a similar failure warning.
为user-name和passwordXML 条目设置值会创建类似的失败警告。
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-urlvalue as expected or - changing JBoss to run as
ABC\dbuseror - giving the JBoss service user
ABC\appuserdatabase access by either giving it direct access or adding it to an AD Group with access.
- 扩展 JBoss 用于创建此数据源的任何类，将其替换
connection-url为按预期应用该值的自定义类，或者 - 更改 JBoss 以作为
ABC\dbuser或 ABC\appuser通过直接访问或将其添加到具有访问权限的 AD 组来授予 JBoss 服务用户数据库访问权限。
None of these workarounds is preferable; there must be a more elegant, accepted solution. How can I resolve this?
这些解决方法都不是可取的；必须有一个更优雅、更被接受的解决方案。我该如何解决这个问题？
采纳答案by Christopher Flather
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 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:
在很大程度上，您应该能够换出 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=trueto 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
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
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>
创建文件夹 JBOSS_HOME\modules\net\sourceforge\jtds\main
将文件 jtds-1.3.1.jar 复制到文件夹中。
在文件夹中创建一个名为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>将以下 XML 添加到 standalone.xml （修改驱动程序元素以添加驱动程序元素，如果它已经存在）
<drivers> <driver name="jtds" module="net.sourceforge.jtds"> <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class> </driver> </drivers>
回答by JoshDM
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).
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).
不知何故，在 JBoss 内部，当将尝试连接到此数据源时，上下文从 ABC\AppUser 帐户模拟到 ABC\DBUser 帐户。这需要在 JBoss 内部完成，不幸的是我不相信这是可能的（尽管我并不乐观）。
向 SQL Server 内的 ABC\AppUser 帐户授予连接和模拟权限（仅适用于 ABC\DBUser 登录）。使用 JBoss 数据源配置项 new-connect-sql 运行模拟命令成为 SQL Server 内部的 ABC\DBUser (EXECUTE AS LOGIN ='ABC\DBUser' WITH NO_REVERT)。
