Configuring BWAgent for Microsoft SQL Server and TIBCO FTL®

The BWAgent can be configured to use Microsoft SQL database with TIBCO FTL for transport.

Note: Use of TIBCO FTL with ActiveMatrix BusinessWorks for configuring BWAgent and for configuring group provider for engine does not require TIBCO FTL licenses.
Note: The database name must be unique per agent network if multiple networks share the same physical database. BWAgent and BWEngine support the sharing of the same database, users, and schemas.
Before you begin
  • Install and configure TIBCO FTL on the same machine that you have installed ActiveMatrix BusinessWorks 6.x on. For more information, see the "Setting Up TIBCO FTL® for BWAgent Transport" topic in the TIBCO ActiveMatrix BusinessWorks™ Installation guide.
    Important: For the version of TIBCO FTL that is supported with the version of ActiveMatrix BusinessWorks 6.x, see the ActiveMatrix BusinessWorks readme.
  • Install Microsoft SQL server. The Microsoft SQL driver is available by default.

After installing Microsoft SQL Server, create a database bwadmindb and grant privileges to the default database owner root as described in the following methods:

SQL Server Authentication

    Procedure
  1. Open Microsoft SQL Server Management Studio.
  2. From the Object Explorer pane, right-click Databases > New Database to create the database bwadmindb.
  3. Right-click Security > Logins > New Login....
  4. Make the following configurations from the Login-New window:
    1. Type bwuser in the Login name field.
    2. Select SQL Server Authentication.
    3. Optional. Unselect Enforce password policy, Enforce password expiration, or User must change password at next login.
    4. In the Default database field, select bwadmindb.
    5. From the Select a page pane, on the left side of the Login - New window, click the Server Roles tab.
    6. To configure the BWAgent with MS SQL Server, set the values for Minimum Server Role and Database Role required for a user, for a particular database. In MS SQL Server Management Server, navigate to Security > Logins. Right-click Login Properties > Server Roles. The minimum server role required for a particular user is public. Under User Mapping, the minimum database role membership for the selected database for a user mapped to the login should be one of the following two combinations: public and db_owner OR public, db_datawriter, db_datareader, and db_ddladmin.
    7. Click OK.
  5. Stop the BWAgent if it is running.
  6. Open the bwagent_ftl.json file in BW_HOME\config (Windows) or ${BW_HOME}/config (Unix).
  7. Update the following properties for your environment:
    Property Name Microsoft SQL Value
    dbtypesqlserver
    dbdrivercom.microsoft.sqlserver.jdbc.SQLServerDriver
    dbconnectionurljdbc:sqlserver://localhost:1433;databaseName=bwadmindb;trustServerCertificate=true
    dbuserbwuser
    dbpasswordbwuser
  8. Run the BWAdmin config command with the -cf option and push the changes from the JSON file to the bwagent.ini file. 
    BW_HOME\bin>bwadmin config -cf ../config/bwagent_ftl.json agent
  9. Restart the BWAgent.

 

Windows Authentication

    Procedure
  1. Open Microsoft SQL Server Management Studio.
  2. From the Object Explorer pane, right-click Databases > New Database to create the database bwadmindb.

    Ensure that you have the required permissions on the SQL server.

  3. Configure the Database Engine with your system's Windows Authentication.
  4. When using Windows Authentication Integrated Security, perform the following steps:
    1. Download the sqljdbc_12.8.1.0_enu.zip file from the official Microsoft website and extract it.

    2. Copy sqljdbc_12.8.1.0_enu/enu/auth/x64/mssql-jdbc_auth-12.8.1.x64.dll to Java/jre7/bin and Java/jre7/lib.

    3. Add the latest JAR file mssql-jdbc-12.8.1.jre11.jar to the following location: /config/design/thirdparty.

    4. Add the same latest JAR file to BWAgent Home: \system\shared\com.tibco.bw.tpcl.jdbc.datasourcefactory.sqlserver_4.0.1300.012\lib.

    5. Then, add the same latest JAR file in the Bundle-ClassPath of the MANIFEST.MF file.

  5. Stop the BWAgent if it is running.
  6. Open the bwagent_ftl.json file in BW_HOME\config (Windows) or ${BW_HOME}/config (Unix).
  7. Update the following properties for your environment.
    Property Name Microsoft SQL Value
    dbtypesqlserver
    dbdrivercom.microsoft.sqlserver.jdbc.SQLServerDriver
    dbconnectionurljdbc:sqlserver://localhost:1433;databaseName=Testdb;IntegratedSecurity=true;encrypt=false
    dbusersystemUserName
    dbpasswordBlank
  8. Run the BWAdmin config command with the -cf option and push the changes from the JSON file to the bwagent.ini file. 
    BW_HOME\bin>bwadmin config -cf ../config/bwagent_ftl.json agent
  9. Restart the BWAgent.