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'
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
The DB I'm using is obviously not
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
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.
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
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
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