MS-SQL Server，JDBC和XA事务的例外 [英] Exception with MS-SQL Server,JDBC and XA Transactions

问题描述

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

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

javax.transaction.xa.XAException:com.microsoft.sqlserver.jdbc_SQLServerException:创建XA控件连接失败.错误:对对象'xp_sqljdbc_xa_init_ex'，数据库'master'模式'dbo'的EXECUTE权限被拒绝

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'

我遵循了这些教程了解XA交易和如何使MSSQL Server XA数据源正常工作? 在遵循了第一个教程之后，我还在SSMS中运行了以下命令:

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

使用主版GO
EXEC sp_addrolemember [SqlJDBCXAUser]，"MyUserName"开始

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

我还要补充一点

使用主GO EXEC sp_grantdbaccess'MyUserName'，'MyUserName'GO

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

验证用户是否有权访问主数据库，并且收到错误消息该用户已存在于当前数据库中". 最后，我通过SSMS验证了角色SqlJDBCXAUser确实具有针对xp_sqljdbc_xa_init_ex授予的EXECUTE.
我正在使用的数据库显然不是master而是myDBName. 就此问题而言，两者之间的唯一关联是MyUserName是myDBName的所有者，并作为用户在master中存在.
我的服务器在Windows XP SP3上运行(因此，第一个教程中提到的修补程序并不适用，因为它适用于XP SP2及以下版本，我知道我尝试运行此修补程序).

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 SqlJDBCXAUser does have EXECUTE granted in regard to xp_sqljdbc_xa_init_ex.
The DB I'm using is obviously not master but myDBName. The only correlation between the two, with regard to this issue, is that MyUserName is the owner of myDBName and 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).

有人遇到过这个问题吗?我真的很感谢一些潜在客户.
谢谢，
伊泰

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

更新:
我再次看了Microsoft中的第一个教程，有两段我不确定它们的含义，它们可能包含解决方案:

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:

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

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 instance时，是否表示包含几个数据库(包括master和myDBName)的sql server(我习惯于对oracle的术语有所不同)?我按给定的名称运行了xa_install.sql脚本，并且显示了use master.

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

这是第二段:

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

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:

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

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

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

我不确定，但是我认为上面的粗体句子表示SqlJDBCXAUser角色应仅在master上定义，并且应授予其他访问myDBName的用户访问master的权限，然后添加到该角色中，并且会在使用myDBName数据库使用xa软件包时某种方式(不知道如何)启用它们.

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

更新2: 这是来自SSMS的SqlJDBCXAUser角色下存储过程的安全设置的屏幕截图.

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

推荐答案

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

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

这篇关于MS-SQL Server，JDBC和XA事务的例外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！