Exception with MS-SQL Server,JDBC and XA Transactions

Question

Hi, 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

Answer 1

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:

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

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.

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.aspx for other limitations you might be running into: particularly if you are using 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.

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

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't be 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.

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.