IBM Support

Manually enable SSL communication to Oracle database from MDM

Troubleshooting


Problem

When you have an InfoSphere MDM Version 11.4 instance with fix pack 4 installed (V11.4.0.4), you have the ability to enable SSL communication to the Oracle database. This tech note lists the manual steps required to enable SSL.

Cause

The ODBC/JDBC driver version to support SSL communication to an Oracle database is only available after fix pack 4 for InfoSphere MDM Version 11.4. Manual enablement is required after InfoSphere MDM installation and application of the fix pack.

Environment

When SSL communication to Oracle database is required.

Resolving The Problem

Use the following steps to update the ODBC driver version from fix pack 4.

  2. Go to the <MDM_INSTALL_HOME>/mds/scripts directory.
  3. Open the build-utils-config.xml file.
  4. Replace the mad.db.driver.version from 25 to 27.
    Change
    <property name="mad.db.driver.version" value="25"/>
    to
    <property name="mad.db.driver.version" value="27"/>
  5. Make a backup of the odbc.ini file that is in <MDM_INSTALL_HOME>/mds/conf when the operating system is AIX, UNIX, Linux, or Solaris.
  6. Go to the <MDM_INSTALL_HOME>/mds/scripts directory and run the following madconfig targets:
    - When the operating system is AIX, UNIX, Linux, or Solaris, run the following commands:
    ./madconfig.sh remove_datasource
    ./madconfig.sh unregister_odbc
    ./madconfig.sh register_odbc
    ./madconfig.sh create_datasource
    As prompted by the targets, provide values for database type, database host, database port, database name, and database schema.

    - When the operating system is Microsoft Windows, run the following commands:
    madconfig.bat remove_datasource
    madconfig.bat unregister_odbc
    madconfig.bat register_odbc
    madconfig.bat create_datasource
    As prompted by the targets, provide values for database type, database host, database port, database name, and database schema.
  7. The preceding steps will remove the ODBC datasource that uses the driver with version 25 and create the ODBC datasource with driver version 27.
  8. If any custom properties were added to odbc.ini, add them back to the newly created odbc.ini.
  9. If the database is SSL enabled, add the property encryptionMethod=SSL in the file <MDM_INSTALL_HOME>/mds/conf/com.ibm.mdm.mds.jdbc.cfg

    Note: The following steps are to be performed for modifying ODBC Data Source used at runtime.
  10. Go to <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/scripts
  11. Open the file build-utils-config.xml.
  12. Replace the value of mad.db.driver.version from 25 to 27.
    Change
    <property name="mad.db.driver.version" value="25"/>
    to
    <property name="mad.db.driver.version" value="27"/>
  13. Take a backup of file odbc.ini found in <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/conf
  14. Copy <MDM_INSTALL_HOME>/mds/conf/odbc.ini to replace <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/conf/odbc.ini
    Note: This step is not required Windows operating systems because the Data Source is maintained as a system DSN and has been updated during configuration.
  15. If any custom properties were added to odbc.ini, add them back to the newly created odbc.ini.
  16. If the database is SSL enabled, add the property encryptionMethod=SSL in file <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/conf/com.ibm.mdm.mds.jdbc.cfg
  17. When InfoSphere MDM is installed on a cluster, repeat steps 8 to 14 for each node in the cluster.


Use the following steps to enable SSL communication to Oracle database:
  1. Add the database certificate to the trust store and update the JVM custom properties in the WebSphere Application Server Integrated Solutions Console (admin console).
    2. VariableValueDescription
    javax.net.ssl.trustStore/<WAS_HOME>/profiles/Dmgr01/etc/trust.p12Trust store location (e.g., /opt/IBM/WebSphere/AppServer8556/Dmgr01/etc/trust.p12)
    javax.net.ssl.trustStorePasswordWebASTrust store password
    javax.net.ssl.trustStoreType PKCS12Trust store type
  2. For Linux and AIX systems, you can use ikeyman.sh to add a certificate to the trust store:
    a. Run ikeyman.sh command to launch the IBM Key Management GUI.
    b. In the IBM Key Management window, click Open Key database file and proceed by entering the
    datastore values from the table above.
    c. From the drop-down menu under Key database content, select Signer certificates.
    d. Add the necessary certificates.

    To update JVM custom properties,
    a. In WebSphere Application Server admin console, go to Servers>All servers>{Server-Name}>Java and Process.
    b. Add the three variables from the table above to the list and save.
  3. Update odbc.ini file in the following two locations:
    Location 1: <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/conf

    Location 2: <MDM_INSTALL_HOME>/mds/conf

    Edit the odbc.ini file in these locations and update this following entry with the right SSL database port , in this example below our DB SSL port is 2485.
    PortNumber=2485

    Then add the following four entries as example below with required arguments (EncryptionMethod, TrustStore, TrustStorePassword ,ValidateServerCertificate)

    EncryptionMethod=1
    TrustStore=/opt/IBM/WebSphere/AppServer8556/profiles/Dmgr01/etc/trust.p12
    TrustStorePassword=WebAS
    ValidateServerCertificate=0

    For Windows systems, open the ODBC Data Source Administrator in the System DSN panel:
    a. Select the Data Source and click on Configure.
    b. In the General Pane, change the port number to the SSL port.
    c. Go to the Security Pane, set the Encryption Method to 1 - SSL Auto.
    d. Set the values for the Trust Store (Path to trust file) and Trust Store Password and then
    disable Validate Server Certificate.
    f. Click OK.
  4. Update com.ibm.mdm.mds.jdbc.cfg file in these following two locations:
    Location 1: <WAS_PROFILE_HOME>/installedApps/<cellName>/MDM-native-<instanceId>.ear/native.war/conf
    Location 2: <MDM_INSTALL_HOME>/mds/conf

    Update the port number to the DB SSL port: portNumber=2485
    Then add this entry: encryptionMethod=SSL

    Note: If you have client Java applications that use the above JDBC configuration file to communicate to SSL enabled Oracle database, you must set the following JVM system properties:
    a. javax.net.ssl.trustStore
    b. javax.net.ssl.trustStorePassword
    c. javax.net.ssl.trustStoreType

    For example:
    System.setProperty("javax.net.ssl.trustStore", "/opt/IBM/MDM/trust.p12");
    System.setProperty("javax.net.ssl.trustStorePassword", "password");
    System.setProperty("javax.net.ssl.trustStoreType", "PKCS12");
  5. Update the data sources for SSL communication in the WebSphere Application Server admin console:

    DWLConfig datasource:
    Change the URL field to example: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mdmora12oas.torolab.ibm.com)(PORT=2485))(CONNECT_DATA=(SERVICE_NAME=ora12oas)))

    Edit the custom properties and make these following changes:
    - Update the port number to the SSL port: portNumber=2485
    - Add these two custom properties entries:
    sslConnection true and
    connectionProperties
    oracle.net.ssl_version=1.0;javax.net.ssl.trustStore=/opt/IBM/WebSphere/AppServer8556/profiles/Dmgr01/etc/trust.p12;javax.net.ssl.trustStoreType=PKCS12;javax.net.ssl.trustStorePassword=WebAS

    Note: Save and Test connection should be successful.

    DWLCustomer datasource
    Change URL field to example: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=mdmora12oas.torolab.ibm.com)(PORT=2485))(CONNECT_DATA=(SERVICE_NAME=ora12oas)))

    Edit the custom properties and make the following changes:
    - Update the port number to the SSL port: portNumber=2485
    - Add these two custom properties entries:
    sslConnection true and
    connectionProperties
    racle.net.ssl_version=1.0;javax.net.ssl.trustStore=/opt/IBM/WebSphere/AppServer8556/profiles/Dmgr01/etc/trust.p12;javax.net.ssl.trustStoreType=PKCS12;javax.net.ssl.trustStorePassword=WebAS

    Note: Save and Test connection should be successful.

    MDM datasource
    - Change one custom property: portNumber 2485 ( refer to your Oracle Database installation for the right SSL port number)
    - Add one custom property: encryptionMethod SSL
  6. Cluster installation: When the installation is on a Cluster, add the certificate to the WebSphere Application Server by following these steps.
    a. From the WebSphere Application Server admin console, expand Security and open SSL certificate and key management.
    b. Click on Key stores and certificates available on the right side of the console.
    c. With the Keystore usages set to 'SSL Keystores', click New.
    d. Provide the name as MDMServer-DB-SSL-TrustStore for example.
    e. Provide the SSL file path and pasword and select the appropriate Keystore Type (PKCS12 for .p12 files)
    [Note: Can be the same trust file used for javax.net.ssl.trustStore property as used above.]
    f. Enable Initialize At Startup and Click OK and Save.

    Note: When the cluster is horizontal, each node will look for the SSL Trust File at the Path provided in this Panel. The trust file has to be copied to all the machines in the cluster at the Path specified in this Panel.

    g. Restart the Cluster.

[{"Product":{"code":"SSWSR9","label":"IBM InfoSphere Master Data Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"11.4","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSLVY3","label":"Initiate Master Data Service"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSPVUA","label":"IBM InfoSphere Master Data Management Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSLKUM","label":"InfoSphere Master Data Management Server for Product Information Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 April 2022

UID

swg21971003

Page Feedback