A bug in the jtds JDBC driver causes a hang when connecting to MS SQL Server that requires encrypted (SSL) connections. A patch is developed for the issue (see https://sourceforge.net/p/jtds/bugs/767/). 

BMC has rebuilt the driver with the patch and has tested it for use in Workbench for Eclipse and File-AID/EX in version 18.02.06 and all later releases. Starting with the release 19.01.01, the updated driver is included in the distribution of Workbench for Eclipse, File-AID Services, File-AID Execution Server, and File-AID Rules Engine (FARE) in all supported environments.

The following section explains how to adjust the connection definitions for different components to be able to connect with the Microsoft SQL Server that requires encrypted connections.

File-AID/EX

1. The connectors must be modified, to use the encrypted connection demanded by the SQL Server instance.

2. In the connection template, for each connector, add the parameter ;ssl=request; immediately after the table name in the Database Name: field.

   Important

   The semi-colons at the beginning and end of the parameter are required.

3. The same change can be done for repository connection definitions. Open the Repository Management Utility application from Workbench for Eclipse, click the Repositories tab, and edit all connections to SQL Server that require encrypted connection by adding the parameter ;ssl=request; to the Database name: field.

Bulk modification of existing specifications:

The File-AID/EX ConverterPro Specification Replication Utility is used to modify existing ConverterPro specifications that use connection templates that are not compatible with forced encryption (i.e. ;ssl=request; parameter is not present).

To perform the modification action, perform the following steps:

1. Identify File-AID/EX ConverterPro specifications that have connections that need to support forced encryption.
2. Start File-AID/EX and create a connection template that can be used to replace those connection templates that are incompatible with forced encryption. When multiple databases are accessed, multiple connection templates are required.
3. Start the Specification Replication Utility from Workbench for EclipseFile-AID/EX, Tools option.
4. Create a new override file for the replacement of the non-conforming specifications. The override file will need to identify the connection template to be replaced, as well as the connection template to replace it with.
5. Select the ConverterPro specifications to be modified within the utility and select the override file created in step 4.
6. Run the utility. The utility will replace the non-conforming connection template with the replacement template.

Workbench for Eclipse

Configuring Host Explorer connections

Host Explorer supports additional connection parameters for JDBC connections. Connections to source and target database tables can be configured to include the ssl=request parameter by defining the parameter within the Properties tab of the database connection configuration dialog box.

For existing, source and target connections used in the File-AID Data Editor, enter ssl in the Property Name field and request in the Property Value field in the Properties tab of the Database Connection dialog box.

However, note that for connections that will be used in File-AID/EX tools; Related Extract, Related Loader, Application Relationship Editor, and Selection Criteria editor, are processed by the File-AID/EX execution engine and need to be defined in slightly different manner. For these connections, you should also add the ;ssl=request; parameter after the Database Name: as described in the File-AID/EX topic of this section.

File-AID Services

Configuring Data Privacy Repositories

Translation table connections are configured in the same manner as mentioned in the preceding topic, except for adding the ;ssl=request; after the Database Name:.

Configuring translation tables

The configuration of translation tables follows the same process as mentioned in the preceding topic. Namely, adding the ;ssl=request; parameter to the connection string definition. If the translation table database connection is already been configured as described in the Workbench for Eclipse section, there is no further change required. This is because the modified parameter will be automatically propagated to the translation table connection definition.

The Edit Data Privacy Repository utility is used to modify multiple translation table definitions. The utility is available once the project repository is opened. To access the utility, right-click on the open repository node in the tree and select the utility from the context menu.

Once the utility is opened, you can select the Translate Tables tab at the bottom of the editor. It will display a grid with the current translation table definitions within the repository. Stretch the Host column to view the entire connection string for the Translate Tables. In the Host column, click the connection string to edit an entry. Add the ;ssl=request; parameter after the database name.

After editing the Translate Tables connection strings, click the save icon in the toolbar to save the values.

Result

After performing the reconfiguration of the connectors, the data disguise will work normally. BMC has tested Data Privacy translations and coverage, related extracts, related loads, ConverterPro, File-AID/EX repository definitions and found all to work normally after modifying the connections strings.