Java MS-SQL Server、JDBC 和 XA 事务的异常

Exception with MS-SQL Server,JDBC and XA Transactions

提问by Ittai

I'm getting the following exception in my log when I try to perform an XA transaction:

当我尝试执行 XA 事务时，我的日志中出现以下异常：

javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc_SQLServerException: failed to create the XA control connection. Error: "The EXECUTE permission was denied on the object 'xp_sqljdbc_xa_init_ex', database 'master' schema 'dbo'

javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc_SQLServerException: 未能创建 XA 控制连接。错误：“对象‘xp_sqljdbc_xa_init_ex’的EXECUTE权限被拒绝，数据库‘master’架构‘dbo’

I followed these tutorials Understanding XA Transactionsand How to make MSSQL Server XA Datasource Work?After following the first tutorial I also ran the following command in SSMS:

我按照这些教程了解 XA 事务和如何使 MSSQL Server XA 数据源工作？遵循第一个教程后，我还在 SSMS 中运行了以下命令：

use master GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'MyUserName' GO

使用 master GO
EXEC sp_addrolemember [SqlJDBCXAUser], 'MyUserName' GO

I'll also add that I ran

我还要补充一点，我跑了

use master GO EXEC sp_grantdbaccess 'MyUserName','MyUserName' GO

使用 master GO EXEC sp_grantdbaccess 'MyUserName','MyUserName' GO

to verify that the user has access to the master db and I got an error that "the user already exists in the current database". Lastly I verified, via SSMS, that the role SqlJDBCXAUserdoes have EXECUTE granted in regard to xp_sqljdbc_xa_init_ex.
The DB I'm using is obviously not masterbut myDBName. The only correlation between the two, with regard to this issue, is that MyUserNameis the owner of myDBNameand exists as a user in master.
My Server is running on Windows XP SP3 (so the hotfix mentioned in the first tutorial is not relevant as it is meant for XP SP2 and under, I know as I tried to run the hotfix).

验证用户是否有权访问主数据库，并且我收到一个错误“用户已存在于当前数据库中”。最后，我通过 SSMS 验证了该角色SqlJDBCXAUser确实已授予关于xp_sqljdbc_xa_init_ex.
我正在使用的数据库显然不是master但myDBName. 只有两者之间的相关性，对于这个问题，就是MyUserName是的主人myDBName，并存在作为用户master。
我的服务器在 Windows XP SP3 上运行（因此第一个教程中提到的修补程序不相关，因为它适用于 XP SP2 及更低版本，我知道我尝试运行该修补程序）。

Has someone encountered this issue? I'd really appreciate some leads.
Thanks,
Ittai

有人遇到过这个问题吗？我真的很感激一些线索。
谢谢，一
泰

Update:
I've looked at the first tutorial, from Microsoft, again and there are two paragraphs which I'm not sure what they mean and they might contain the solution:

更新：
我Microsoft再次查看了第一个教程，来自, 并且有两段我不确定它们的意思，它们可能包含解决方案：

Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance.

在将参与分布式事务的每个 SQL Server 实例上执行数据库脚本 xa_install.sql。此脚本安装由 sqljdbc_xa.dll 调用的扩展存储过程。这些扩展存储过程实现了对 Microsoft SQL Server JDBC 驱动程序的分布式事务和 XA 支持。您需要以 SQL Server 实例的管理员身份运行此脚本。

When they say SQL Server instance, do they mean the sql server which contains several databases, including masterand myDBName(I'm used to oracle terms which are a bit different)? I ran the xa_install.sqlscript once as it was given and it states use master.

当他们说 时SQL Server instance，他们的意思是包含多个数据库的 sql server，包括master和myDBName（我习惯于有点不同的 oracle 术语）？我运行了xa_install.sql一次脚本，因为它给出了它的状态use master。

This is the second paragraph:

这是第二段：

Configuring the User-Defined Roles
To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role. For example, use the following Transact-SQL code to add a user named 'shelby' (SQL standard login user named 'shelby') to the SqlJDBCXAUser role:

配置用户定义的角色
要授予特定用户参与 JDBC 驱动程序的分布式事务的权限，请将用户添加到 SqlJDBCXAUser 角色。例如，使用以下 Transact-SQL 代码将名为“shelby”的用户（名为“shelby”的 SQL 标准登录用户）添加到 SqlJDBCXAUser 角色：

USE master  
GO  
EXEC sp_grantdbaccess 'shelby', 'shelby'  
GO  
EXEC sp_addrolemember [SqlJDBCXAUser], 'shelby'  

SQL user-defined roles are defined per database. To create your own role for security purposes, you will have to define the role in each database, and add users in a per database manner. The SqlJDBCXAUser role is strictly defined in the master databasebecause it is used to grant access to the SQL JDBC extended stored procedures that reside in master. You will have to first grant individual users access to master, and then grant them access to the SqlJDBCXAUser role while you are logged into the master database.

SQL 用户定义的角色是按数据库定义的。要出于安全目的创建自己的角色，您必须在每个数据库中定义角色，并以每个数据库的方式添加用户。SqlJDBCXAUser 角色在 master 数据库中被严格定义，因为它用于授予对驻留在 master 中的 SQL JDBC 扩展存储过程的访问权限。您必须首先授予单个用户对 master 的访问权限，然后在您登录到 master 数据库时授予他们对 SqlJDBCXAUser 角色的访问权限。

I'm not sure but I think that the above bolded sentence says that the SqlJDBCXAUserrole should only be defined on masterand that other users which access myDBNameshould be granted access to masterand then added to the role and that will somehow(don't know how) will enable them when using the myDBNamedatabase to use the xa packages.

我不确定，但我认为上面加粗的句子说SqlJDBCXAUser角色应该只定义在master，其他用户myDBName应该被授予访问权限master，然后添加到角色中，这将以某种方式（不知道如何）将在使用myDBName数据库使用 xa 包时启用它们。

Update 2:This is a screenshot from SSMS of the stored procedure's security settings under the SqlJDBCXAUser role

更新 2：这是 SqlJDBCXAUser 角色下存储过程安全设置的 SSMS 截图

采纳答案by Jan

We only had to do the following:

我们只需要执行以下操作：

USE [master]
GO
CREATE USER [UserName] FOR LOGIN [UserName] WITH DEFAULT_SCHEMA=[dbo]
use [master]
GO
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_commit] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_end] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_forget] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_forget_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_init] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_init_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_prepare] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_prepare_ex] TO [UserName] 
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_recover] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_rollback] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_rollback_ex] TO [UserName]
GRANT EXECUTE ON [dbo].[xp_sqljdbc_xa_start] TO [UserName]
GO

回答by Berin Loritsch

It's been a while since I've used Java with SQL server, but just off the bat I noticed something in your T-SQL that might not have behaved the way you wanted. The snippet:

我已经有一段时间没有将 Java 与 SQL Server 一起使用了，但刚开始我就注意到您的 T-SQL 中的某些内容可能没有按照您想要的方式运行。片段：

use master GO;
EXEC sp_addrolemember [SqlJDBCXAUser], 'MyUserName' GO;

Only applies the [SqlJDBCXAUser] to your username in the master database. If your database is in another instance, you will also have to add the role there. The other I'm assuming was a typo ('sp_gratdbaccess' should be 'sp_grantdbaccess').

仅将 [SqlJDBCXAUser] 应用于您在 master 数据库中的用户名。如果您的数据库在另一个实例中，您还必须在那里添加角色。我假设的另一个是拼写错误（“sp_gratdbaccess”应该是“sp_grantdbaccess”）。

I'm assuming your 'xa_install.sql' scripts that you had to run in all participating servers ran successfully, and you received no error messages? Examine the script for the roles it defines, just to make sure what you are typing matches what is needed.

我假设您必须在所有参与的服务器中运行的“xa_install.sql”脚本都成功运行，并且您没有收到错误消息？检查脚本中定义的角色，以确保您键入的内容与需要的内容匹配。

Update:

更新：

Just some sanity checks:

只是一些健全性检查：

• Is SQL Server on a Windows XP (why?) server? then you have a hotfix: http://support.microsoft.com/kb/922668
• Is SQL Server on a Windows 2003 box with Microsoft Distributed Transaction Coordinator (MS DTC)? the you have a hotfix: http://support.microsoft.com/kb/899756
• Check http://msdn.microsoft.com/en-us/library/aa342335.aspxfor other limitations you might be running into: particularly if you are using MS DTC.

• SQL Server 是否在 Windows XP（为什么？）服务器上？那么你有一个修补程序：http: //support.microsoft.com/kb/922668
• SQL Server 是否在带有 Microsoft 分布式事务协调器 (MS DTC) 的 Windows 2003 机器上？你有一个修补程序：http: //support.microsoft.com/kb/899756
• 检查http://msdn.microsoft.com/en-us/library/aa342335.aspx以了解您可能遇到的其他限制：特别是如果您使用 MS DTC。

Microsoft is ambiguous when it calls things an "Instance", particularly because they apply it to database instances (your database) as well as SQL Server instances. One physical server can have multiple copies of SQL server running at the same time listening on different ports. Each of these would have its own Master database instance. By the context of the other statements (i.e. the XA transaction support lives in the master database), they are talking about every copy of SQL Server you have running. If your app's database is spread accross 4 instances (installations) of SQL Server, you have to perform the XA installation steps on all four installations.

当 Microsoft 将事物称为“实例”时，它是模棱两可的，特别是因为他们将其应用于数据库实例（您的数据库）以及 SQL Server 实例。一台物理服务器可以同时运行多个 SQL Server 副本，侦听不同的端口。每一个都有自己的主数据库实例。根据其他语句的上下文（即 XA 事务支持存在于 master 数据库中），它们谈论的是您运行的 SQL Server 的每个副本。如果您的应用程序的数据库分布在 SQL Server 的 4 个实例（安装）中，则您必须对所有四个安装执行 XA 安装步骤。

The last step to make sure that the roles took, and are applied to your system properly, open up the master database with the management console. You want to make sure your user is in the Databases/master/Security/Users folder, and that it has the SqlJDBCXAUser role enabled (checkbox for the role).

最后一步是确保角色扮演并正确应用于您的系统，使用管理控制台打开主数据库。您要确保您的用户位于 Databases/master/Security/Users 文件夹中，并且启用了 SqlJDBCXAUser 角色（该角色的复选框）。

Next, go to the offending stored procedure that is complaining, and make sure that any security settings include the SqlJDBCXAUser role. The role names shouldn'tbe case sensitive (as SQL itself is not), but it wouldn't hurt to make sure the role case is the same case--just in case.

接下来，转到出现问题的有问题的存储过程，并确保所有安全设置都包含 SqlJDBCXAUser 角色。角色名称不应该区分大小写（因为 SQL 本身不区分大小写），但确保角色大小写相同也无妨——以防万一。

If that fails, also run the 'xa_install.sql' script in your MyDatabase instance. I personally hate this ambiguity, but it very well could be what they mean. But before you do that, make sure you don't need any hot fixes or have a configuration where it won't work right. Undoing something a complicated SQL script does can be a major pain. That's why I suggest doing this last.

如果失败，还要在 MyDatabase 实例中运行“xa_install.sql”脚本。我个人讨厌这种歧义，但这很可能就是他们的意思。但在您这样做之前，请确保您不需要任何热修复或配置无法正常工作。撤消复杂的 SQL 脚本所做的事情可能会很痛苦。这就是为什么我建议最后这样做。