Exception with MS-SQL Server, JDBC, and XA Transactions

advertisements

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: 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'

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:

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

I'll also add that I ran

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

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:

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.

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.

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:

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

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.

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