Enabling XA Data Sources on MS SQL

You must enable XA data sources if you are using MS SQL.

  1. Ensure that Microsoft SQL Server is installed and configured with the databases as outlined above.

  2. Set the Microsoft Distributed Transaction Coordinator (DTC) Service from Manual to Automatic in the Windows Services panel. Note: If the DTC Service is not running, restart the SQL Server. The DTC Service must be started before the SQL Server Service starts for the SQL Server’s JDBC driver to operate in XA mode. Whenever changes are made to the DTC Service, you must restart the SQL Server.

  3. Copy the sqljdbc_xa.dll from the XA subdirectory in the JDBC driver distribution to the MSSQL/bin and MSSQL/91/DTS/bin directory of the SQL Server installation.

  4. Windows 2003 only: You must add registry keys to the DTC key for XA support. Refer to the Microsoft knowledgebase website for additional information.

  5. Enable XA transactions for the DTC by accessing Start > Control Panel > Administrative Tools > Component Services. Open the Component Services tree option, and select your computer from the Computers directory. Right click on your computer to access the Properties panel. Select the MSDTC tab, and click the Security Configuration button. Select the Enable XA Transactions check box and click OK to save your settings. Restart the SQL Server manually once the DTC Service is running.

  6. Start the SQL Server Management Studio and connect to the local database engine as the system administration. Open the xa_install.sql file from the subdirectory of the downloaded and extracted JDBC driver. Execute the script by selecting Query > Execute.

  7. Verify that the DTC Service started properly by accessing the management node of your database server in the SQL Server Management Studio. The DTC icon will contain a green arrow if the SQL Server has recognized the DTC Service. If there is no arrow on the DTC entry, ensure that the DTC was restarted before the database and that the sqljdbc_xa.dll is in the MSSQL/bin directory.

  8. In the SQL Server Management Studio, add the SqlJDBCSAUser role to the admin, svcagent and reporting users by executing the following script while replacing the ‘admin' name with the name of your database users:

    USE master
    GO
    EXEC sp_grantdbaccess 'admin'., 'admin'
    GO
    EXEC sp_addrolemember [SqlJDBCXAUser}, 'admin'

  9. Verify the assignment of the SqlJDBCXAUser role by opening the master schema in the systems databases and looking at the role in the security folder. All of the users you added should be listed as members.

    Note: Windows XP requires a hot fix to allow XA transactions to work. If you have installed Service Pack 3, the hot fix has been applied. Refer to the Microsoft Support knowledgebase website for additional information.

 
loading table of contents...