MS-SQL Server, JDBC, and XA transaction exception

Question

I get the following exception in my log when I try to complete an XA transaction:

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

I followed these tutorials Understanding XA Transactions and How to Make MSSQL Server Work Xat Datasource? After completing the first lesson, I also ran the following command in SSMS:

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

I will also add that I ran

use the GO EXEC wizard sp_grantdbaccess 'MyUserName', 'MyUserName' GO

to make sure the user has access to master db, and I got the error message "the user already exists in the current database." Finally, through SSMS, he confirmed that the SqlJDBCXAUser role has EXECUTE provided with respect to xp_sqljdbc_xa_init_ex .
The database I'm using is clearly not master , but myDBName . The only correlation between them on this issue is that MyUserName is the owner of myDBName and exists as a user in master .
My server is running Windows XP with Service Pack 3 (so the fix mentioned in the first lesson does not matter, since it is for XP SP2 and below, I know how I tried to run the fix).

Has anyone encountered this issue? I would really appreciate some conclusions.
Thanks,
Ittay

Update:
I looked at the first tutorial starting with Microsoft , and there are two paragraphs that I'm not sure what they mean, and they may contain a solution:

Run the xa_install.sql script database on each instance of SQL Server that will participate in distributed transactions. This script installs the extended stored procedures that sqljdbc_xa.dll calls. 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 the administrator of the instance of SQL Server.

When they say SQL Server instance , do they mean a sql server that contains several databases, including master and myDBName (I use terms that are slightly different for the oracle)? I ran xa_install.sql script once when it was specified and it indicates use master .

This is the second paragraph:

Customizing 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" to the SqlJDBCXAUser role (a standard SQL user user named "shelby"):

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

User-defined user roles are defined for each database . To create your own security role, you will need to define a role in each database and add users to the database. The role of SqlJDBCXAUser is strictly defined in the main database , as it is used to provide access to the extended JDBC SQL stored procedures, which are located in the main. You must first grant individual users access to the wizard, and then grant them access to the SqlJDBCXAUser role when you log in to the main database.

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

Update 2: This is a screenshot of SSMS stored procedure security parameters in the role of SqlJDBCXAUser

Answer 1

We needed 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 

Answer 2

It has been a while since I used Java with a SQL server, but from the very beginning I noticed something in your T-SQL that might not have been the way you wanted. Fragment:

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

Applies [SqlJDBCXAUser] to your username in the main database. If your database is in a different instance, you will also have to add a role there. Another that I assume is a typo ("sp_gratdbaccess" should be "sp_grantdbaccess").

I assume that your "xa_install.sql" scripts, which you must run on all participating servers, ran successfully and you did not receive error messages? Examine the script for the roles that it defines, just to make sure you type what you need.

Update:

Just some sanity checks:

• Is SQL Server on a Windows XP server (why?)? then you have a fix: http://support.microsoft.com/kb/922668
• Is SQL Server in a Windows 2003 window with Microsoft Distributed Transaction Coordinator (MS DTC)? you have a fix: http://support.microsoft.com/kb/899756
• Note http://msdn.microsoft.com/en-us/library/aa342335.aspx other restrictions that may occur: especially if you use MS DTC.

Microsoft is ambiguous when it calls things an “instance”, especially because they apply it to database instances (your database) as well as SQL Server instances. One physical server can have several instances of SQL server listening on different ports at the same time. Each of them will have its own instance of the Master database. In the context of the other statements (i.e., XA Transaction Support lives in the main database), they talk about every copy of SQL Server that you run. If your application database extends to 4 instances (installations) of SQL Server, you need to follow the steps to install XA on all four installations.

The last step is to ensure that the roles are accepted and applied to your system properly, open the main database using the management console. You want to make sure that your user is in the Databases / Wizard / Security / Users folder and that he has the SqlJDBCXAUser role enabled (checkbox for the role).

Then go to the violating stored procedure that complains, and make sure that any security settings include the SqlJDBCXAUser role. Role names should not be case sensitive (since SQL itself is not), but it would not hurt to make sure that the role example is the same case, just in case.

If this fails, run the "xa_install.sql" script in your instance of MyDatabase. I personally hate this ambiguity, but it's very good what they mean. But before you do this, make sure that you do not need any hot fixes or that you have a configuration where it will not work correctly. Undoing something complicated SQL script can be a serious pain. That is why I propose making it last.