Resolution:
1.  In JDBC Connection Configuration, the settings should be:

Connection Type: XA
XA DataSource Class: tibcosoftwareinc.jdbcx.sqlserver.SQLServerDataSource
DB URL: jdbc:tibcosoftwareinc:sqlserver://&lthost>:&ltport>DatabaseName=&ltdb>

2. Stored Procedures for JTA must be installed. Refer the link below:

http://media.datadirect.com/download/docs/connectsqlxml/jdbcug/title.htm
----------------------------------------
Chapter 6 : The SQL Server Driver
Section   : Installing Stored Procedures for JTA
-----------------------------------------
If this step is not done then the following error is seen:

BW-JDBC-100034 "Configuration Test Failed. Exception [java.sql.SQLException] occurred.
java.sql.SQLException: [tibcosoftwareinc] [SQLServer JDBC Driver][SQLServer] Could not find stored procedure 'master..xp_jdbc_open2'."

3. Install SQL Server 2000 Service Pack 4

If you running SQL server with anything below SP4 then you will get the following error:

java.sql.SQLException: [tibcosoftwareinc][SQLServer JDBC Driver][SQLServer]xa_open (0) returns -3

4. You may have to turn on support for XA transactions in SQL Server. To do this, follow these steps:

      a. Open Component Services.
      b. Expand the tree view to locate the computer where you want to turn on support for XA transactions (for example, My Computer).
      c. Right-click the computer name, and then click Properties.
      d. Click the MSDTC tab, and then click Security Configuration.
      e. Under Security Settings, click to select the check box for XA Transactions to turn on this support.

5. Confirm that  the DTC (Distributed Transaction Coordinator) service is started.

What are the steps required to configure XA type connection with SQL Server driver ?