IBM Support

Instructions to Enable TLS Communications connecting to DB2 Databases in IBM Information Server v11.x

How To


Summary

This document cover steps required to enable TLS communication for connections connecting to DB2 XMeta and DSODB databases used by Information Server. It does not cover the database connections that are defined for products like DataStage, Information Analyzer, Metadata Asset Manager(IMAM)

Steps

The procedure documented here supports enablement of DB2 to support TLS connections and the changes that need to be made in Information Server to support TLS connections to the XMeta repository and DSODB databases.
In this document, IS installation path refers to the location where IBM Information Server is installed on your machine. By default, /opt/IBM/InformationServer on Unix or C:\IBM\InformationServer on Windows.
Services tier refers to the machine where the IBM Information Server Services tier is installed.
Engine tier refers to the machines where the IBM Information Server Engine tier is installed.
For details on supported Operating systems and Database Servers see the link.
The command examples in this document are for Unix environments and to adjust to run in a Windows environment.
 3. Before you Begin
Before you begin this process, back up your complete Information Server installation.
4. Configure TLS Support in DB2
These steps are for configuring TLS support for the XMeta repository DB2 database.
4.1. Upgrade DB2 10.5
This section applies to Information Server versions 11.3.x and 11.5.x.  If the current environment is version 11.7 or later, then skip to the next section.
If the Information Server environment is using DB2 version 10.5, then it must be at a minimum level of Fix Pack 4 to support TLS version 1.2. The db2jcc.jar library installed in Information Server needs to be updated by replacing all instances with the one in the {DB2 installation path}/java directory.
Replace the following instances of db2jcc.jar with {DB2 installation path}/java/db2jcc.jar
Services tier:
  {IS installation path}/ASBServer/lib/db2jcc.jar
  {IS installation path}/ASBServer/apps/lib/iis/classes/db2jcc.jar
Engine tier:
  {IS installation path}/ASBNode/lib/java/db2jcc.jar
4.2. Set the TLS version
Run the following command to determine whether DB2 is configured to support TLS connections:
db2 get dbm cfg | grep SSL_VERSIONS
If SSL_VERSIONS is not set to wanted TLS version (for example TLS 1.2), run the following command:
db2 update dbm cfg using SSL_VERSIONS TLSv12
The above command configures DB2 to accept TLS 1.2 connections. 
4.3. Configure DB2 to support TLS connections
Perform the steps mentioned in the following DB2 documentation link.
DB2 11.5
DB2 11.1
DB2 10.5
The SSL_SVCENAME port that is mentioned in the above link can be determined by looking for the SVCENAME setting in the output from the following command:
db2 get dbm cfg | grep -i svce
Be sure that the DB2 instance owner has read access to the keystore and stash files that were generated.
The DB2 instance (for example db2inst1) is the current DB2 instance. 
Run the following command to determine the DB2 instance:
db2 get instance
Setting DB2COMM to a value of SSL,TCPIP enables support for both the TLS and non-TLS ports.  If only the TLS port should be open, then set the value to SSL.
Hold off on stopping and starting DB2 until the services file is updated with DB2 TLS port.

4.4. Update the services file
The services file in Unix and  Windows environments needs to be updated with the DB2 TLS port that was configured in the previous section.
- Unix systems: /etc/services
- Windows systems: C:\Windows\System32\drivers\etc\services
The services file need to have an existing entry for the DB2 non-TLS (that is TCPIP) port like the following:
db2c_db2inst1   50000/tcp
For example, if the SSL_SVCENAME used in step 4.3 is db2c_db2_ssl and the TLS port number is 50001, add following entry to the services file.
db2c_db2_ssl   50001/tcp
Make sure the port assigned to the TLS service is not already in use. 

4.5. Restart the DB2 instance

db2stop
db2start
The following command can be run to verify the system is listening for requests on port 50001:
netstat -an | grep 50001
Something like the following output should be returned:
Unix systems:
tcp   0   0 0.0.0.0:50001     0.0.0.0:*       LISTEN
Windows systems:
TCP    0.0.0.0:50001        0.0.0.0:0     LISTENING
* WebSphere Application Server Network Deployment
A. Open the WebSphere Application Server administrative console.
B. Go to Security > SSL certificate and key management > Key stores and certificates
    For WAS ND stand-alone profile, click NodeDefaultTrustStore.
    For WAS ND clustered profile, click CellDefaultTrustStore.
C. Click Signer certificates.
D. Click Retrieve from port.
E. Enter the host name and TLS listen port of the DB2 server.
F. Type an alias name for the certificate.
G. Click Retrieve signer information.
H. Click OK and then Save to save the configuration.
I. The DB2 certificate should appear in the list of certificates.
* WebSphere Liberty Profile
If WLP in use as the Information Server Services tier, download the DB2 TLS certificate file from DB2 server to the Information Server Services tier, and run following command as root or Administrator.
{IS installation path}/jdk/jre/bin/keytool -import -file {certificate file} -alias {alias} -keystore {IS installation path}/wlp/usr/servers/iis/resources/security/iis-server-truststore.jks -storetype JKS -storepass iiskeypass -noprompt
Note: iiskeypass is the installation default password for iis-server-truststore.jks, if you changed it after installation, adjust the -storepass argument.
* WebSphere Application Server Network Deployment
A. Open the WebSphere Application Server administrative console.
B. Go to Resources > JDBC > Data sources.
C. Select "ASB JDBC DataSource" in the data source list.
D. Update the Port number in the Common and required data source properties section. Enter the value of the TLS port that you set in the database server.
E. Click Apply.
F. In the Additional Properties section, select Custom properties.
G. Click New.
H. Enter sslConnection in the Name field and enter true in the Value field.
I. Click OK and then Save to save the configurations.
Perform the same configuration for the "ASB JDBC XA DataSource", "ASB JDBC Staging XA DataSource", "JReport JDBC DataSource", and any other data source that connects to the DB2 instance.
* WebSphere Liberty Profile
A. Edit {IS installation path}/wlp/usr/servers/iis/bootstrap.properties file, set iis.db.port to the DB2 TLS port number, and save the file.
    If IADB and QSSRDDB connect to the same DB2 instance as XMeta, change ia.db.port and srd.db.port setting as well.
B. Edit {IS installation path}/wlp/usr/servers/iis/dataSources.xml file, add sslConnection="true" to properties.db2.jcc property for jdbc/ASBDataSource, jdbc/ASBStagingDataSource, and jdbc/JReportDataSource, then save the file.
    - Example properties.db2.jcc setting for jdbc/ASBDataSource
  <dataSource jndiName="jdbc/ASBDataSource"
              id="DataSource_ASBDataSource" jdbcDriverRef="db2Driver"
              type="javax.sql.ConnectionPoolDataSource" isolationLevel="TRANSACTION_READ_COMMITTED"
              beginTranForResultSetScrollingAPIs="true" connectionManagerRef="ConnectionPool_ASBDataSource">
    <properties.db2.jcc databaseName="${iis.db.name}" portNumber="${iis.db.port}" driverType="4" sslConnection="true"
                        serverName="${iis.db.host}" password="${iis.db.password}" user="${iis.db.user}"/>
  </dataSource>
    If IADB and QSSRDDB connect to the same DB2 instance as XMeta, make the same change for {IS installation path}/wlp/usr/servers/iis/iadb.DataSources.xml and {IS installation path}/wlp/usr/servers/iis/qssrd.dataSources.xml.
6. Configure the Information Server Services Tier Download the DB2 certificate file from DB2 server to the Information Server Services tier, and run following command as root or Administrator. 
{IS installation path}/jdk/jre/bin/keytool -import -file {certificate file} -alias {alias} -keystore {IS installation path}/jdk/jre/lib/security/cacerts -storetype JKS -storepass changeit -noprompt

{IS installation path}/jdk/jre/bin/keytool -import -file {certificate file} -alias {alias} -keystore {IS installation path}/Updates/_jvm/lib/security/cacerts -storetype JKS -storepass changeit -noprompt

{IS installation path}/jdk/jre/bin/keytool -import -file {certificate file} -alias {alias} -keystore {IS installation path}/_uninstall/_jvm/lib/security/cacerts -storetype JKS -storepass changeit -noprompt
Note:
- changeit is the installation default password for cacerts, if you changed it after installation, adjust the -storepass argument.
- In Windows, import the DB2 certificate into {IS installation path}\jdk32\jre\lib\security\cacerts in addition.
- When upgrading Information Server JDK or Update Installer, new cacerts file can be installed, in that case you need to recover the cacerts files from backup or to reimport the DB2 certificate.
The Information Server Services tier has administrative tools that connect to the XMeta repository database. The connection properties are stored in a database.properties file. A copy of the database.properties file is deployed to the WebSphere Application Server.
(A) Make a backup copy of the current {IS installation path}/ASBServer/conf/database.properties file.
(B) Edit {IS installation path}/ASBServer/conf/database.properties file, change the port number in the url property to the DB2 TLS port number, append :sslConnection=true; to the end of the url property after the database name, and save the file.
    Note:
    - Do not omit the trailing semicolon in ":sslConnection=true;".
 -Example ASBServer/conf/database.properties file setting for Information Server releases released before 11711SP2 and 11713
databasename=xmeta
type=db2
driver=com.ibm.db2.jcc.DB2Driver
url=jdbc:db2://mydb2.example.com:50001/xmeta:sslConnection=true;
user={iisenc}lp79+st5T5+LuDwVe5L7nw==
password={iisenc}TROOXb9LD8zK3kZjK8xK2A==
 -Example ASBServer/conf/database.properties file setting for Information Server releases                           11711SP2, 11713 and later releases
databasename=xmeta
type=db2
driver=com.ibm.db2.jcc.DB2Driver
url=jdbc:db2://mydb2.example.com:50001/xmeta:sslConnection=true;useJDBC4DataType=2;useJDBC4ColumnNameAndLabelSemantics=2;
user={iisenc}lp79+st5T5+LuDwVe5L7nw==
password={iisenc}TROOXb9LD8zK3kZjK8xK2A==
(C) Perform the same steps on file {IS installation path}/ASBServer/apps/lib/iis/classes/database.properties for WAS ND, on {IS installation path}/ASBServer/apps/lib/iis/15properties/database.properties for WLP.
    Or, copy ASBServer/conf/database.properties to ASBServer/apps/lib/iis/classes/database.properties or ASBServer/apps/lib/iis/15properties/database.properties.
(D) Run the following command to propagate the changes to WebSphere Application Server. 
{IS installation path}/ASBServer/bin/AppServerAdmin.sh -db -user {xmeta user Id} -password {xmeta user password}
6.3. Update the imam_staging_repository.properties File
Do the steps (A) and (B) of 6.2 on {IS installation path}/ASBServer/conf/imam_staging_repository.properties file.
7. Update ISA Lite setting in Services tier
There are some updates that need to be made to ISA Lite on the Services tier.
This step applies to Information Server versions 11.3.x and 11.5.x. If the current environment is version 11.7 or later, then skip to the next section.
If the Information Server environment is using DB2 version 10.5, then it must be at a minimum level of Fix Pack 4 to support TLS version 1.2. The db2jcc.jar library installed as part of ISA Lite needs to be updated by replacing all instances with the one in the {DB2 installation path}/java directory.
Replace the following instances of db2jcc.jar with {DB2 installation path}/java/db2jcc.jar file.
  {IS installation path}/ISALite/tools/prereqcheck/utilities/jdbc/db2jcc.jar
  {IS installation path}/ISALite/tools/prereqcheck/lib/db2jcc.jar
The Following script file need to be updated.
  {IS installation path}/ISALite/tools/HealthChecker/SuiteHealthChecker.11.sh in Unix and Linux.
  {IS installation path}\ISALite\tools\HealthChecker\SuiteHealthChecker.11.bat in Windows.

Before updating the script, make a backup copy of the file.
The following three JVM arguments need to be added to the statement at the bottom of the script file that executes the Java command:

"-Djavax.net.ssl.trustStore={IS installation path}/ASBServer/conf/iis-client-truststore.p12"
"-Djavax.net.ssl.trustStorePassword=iiskeypass"
"-Djavax.net.ssl.trustStoreType=PKCS12"
Note:
- iiskeypass is the installation default password for iis-client-truststore.p12, if you changed it after installation, adjust the -Djavax.net.ssl.trustStorePassword argument.
- Example setting for {IS installation path}\ISALite\tools\HealthChecker\SuiteHealthChecker.11.bat
REM Original setting
REM %JAVA% %LANGUAGE_OPTIONS% -DNODE_DIR=%NODE_DIR% -classpath %CLASSPATH% com.ibm.isf.healthcheck.HealthCheck  %*
REM
REM Modified setting
%JAVA% %LANGUAGE_OPTIONS% -DNODE_DIR=%NODE_DIR% -classpath %CLASSPATH% -Djavax.net.ssl.trustStore=C:\IBM\InformationServer\ASBServer\conf\iis-client-truststore.p12 -Djavax.net.ssl.trustStorePassword=iiskeypass -Djavax.net.ssl.trustStoreType=PKCS12 com.ibm.isf.healthcheck.HealthCheck  %*

8. Configure DSODB

If DSODB is not configured in your system, skip this step.
If the DSODB database is colocated with the Metadata Repository (XMETA) database, then the DB2 instance was already configured to support TLS connections in the previous steps.
If the DSODB database is NOT colocated with the XMeta database, then perform the same steps on the DSODB database instance that were performed on the XMeta database instance in step 4.
Download the DB2 TLS certificate file from DSODB DB2 server to the Information Server Engine tier, and run following command as root or Administrator.
{IS installation path}/jdk/jre/bin/keytool -import -file {certificate file} -alias {alias} -keystore {IS installation path}/jdk/jre/lib/security/cacerts -storetype JKS -storepass changeit -noprompt
Note:
- changeit is the installation default password for cacerts, if you changed it after installation, adjust the -storepass argument.
- When upgrading Information Server JDK, new cacerts file is installed, in that case you need to recover the cacerts files from backup or to reimport the DB2 certificate.

8.3. Update the registered Database Server

Run the following command on the Services tier to list the registered databases:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -listDatabases
For each database in the list that has been configured to use TLS connections in step 4 and step 8.1, do the following steps.
Run the following command to display the registered database properties, where {database name} is the name of the database that was returned from the -listDatabases command:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -displayDatabase -dbName {database name}
If the database server port needs to be updated to the DB2 TLS port, then run the following command to update the port, where {database version} and {database server host} are found in the output of the -displayDatabase command and {database server TLS port} is the DB2 TLS port that was configured previously:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -updateDatabaseServer -databaseType DB2 -databaseVersion {database version} -serverHost {database server host} -serverPort {database server TLS port}

8.4. Update the registered DSODB repository

Run the following command on the Services tier to list the registered repositories:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -listRepositories
Locate the repository name of the registered DSODB repository (for example dsodb) in the command output.
Run the following command to display the registered DSODB repository properties, where {repository name} is the name of the registered DSODB repository that was returned from the -listRepositories command:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -displayRepository -reposName {repository name}
Check if the connection URL needs to be updated with the security port and sslConnection=true property setting. Run the following command to update the connection URL, where {url} is the updated connection URL:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -updateRepositoryConnection -reposName {repository name} -connectionURL "{url}"
# For example
# /opt/IBM/InformationServer/ASBServer/bin/RepositoryAdmin.sh -updateRepositoryConnection -reposName dsodb -connectionURL "jdbc:db2://db2.example.com:50002/dsodb:sslConnection=true;"
A. Make a backup of {IS installation path}/Server/DSODBConnect.cfg file in Engine tier.
B. Generate a new DSODBConnect.cfg file by running the following command:
{IS installation path}/ASBNode/bin/RegistrationCommand.sh -user {admin user} -password {password} -gcf -repository {dsodb repository name} -cf {IS installation path}/Server/DSODB/DSODBConnect.tmpl -results {IS installation path}/Server/DSODB/DSODBConnect.cfg

8.6. Run the following command as dsadm or equivalent user in Engine tier

{IS installation path}/Server/DSODB/bin/DSAppWatcher.sh -test
{IS installation path}/Server/DSODB/bin/DSAppWatcher.sh -start

9. Configure QSSRDDB

These steps are for configuring TLS support for the QSSRDDB DB2 database connections. If QSSRDDB is not configured in your system, skip this step.
If the QSSRDDB database is colocated with the XMeta database, then the DB2 instance was already configured to support TLS connections in the previous steps.
If the QSSRDDB is NOT colocated with the XMeta database, then perform the same steps on the QSSRDDB database instance that were performed on the XMeta database instance in step 4.
Run the following command on the services tier to list the registered repositories:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -listRepositories
If the output lists a QSSRDDB repository, then continue with the next steps.
Run the following command to display the registered QSSRDDB repository properties:
{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -displayRepository -reposName QSSRDDB

Check if the connection URL needs to be updated with the DB2 TLS port and sslConnection=true property setting. Run the following command to update the connection URL, where {url} is the updated connection URL:

{IS installation path}/ASBServer/bin/RepositoryAdmin.sh -updateRepositoryConnection -reposName QSSRDDB -connectionURL "{url}"
Run the following command on the services tier to update com.ibm.iis.xmeta.repo.conn.POJO_STAGING property:
{IS installation path}/ASBServer/bin/xmetaAdmin.sh setProperty -file {IS installation path}/ASBServer/conf/imam_staging_repository.properties -dbfile {IS installation path}/ASBServer/conf/database.properties com.ibm.iis.xmeta.repo.conn.POJO_STAGING
Note:
- Always use full path name for -file argument.
- Full path or relative path "../conf/database.properties" can be used as the -dbfile argument.

10. Stop Services
Stop all the Information Server services on each Engine and Services tier. Follow the steps found at:
- Linux/Unix
Shutting down services (Linux, UNIX)
- Windows
Shutting down services (Windows)
11. Start Services
Start all the Information Server services on each Engine and Services tier. Follow the steps found at:
- Linux/Unix
Starting services (Linux, UNIX)
- Windows
Starting services (Windows)
Post restart, it is highly recommended to run ISALite General Diagnostic Health Checker on all tiers and check the output for any errors.
Caveat
After applying this technote, patch installation can fail because the installer cannot establish TLS connection to the XMeta database.
To prevent the error, make sure the TLS connection to XMeta database is possible using following command in Services tier just before starting the installer.
{IS installation path}/Updates/_jvm/bin/java -classpath {IS installation path}/ASBServer/lib/db2jcc.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://<db2-host>:<db2-tls-port>/xmeta:sslConnection=true -user <xmeta-user-name> -password <xmeta-password>
To test the TLS connection with db2jcc4.jar, run the following command. Make sure you do not have any backslashes(\) in the url
{IS installation path}/Updates/_jvm/bin/java -classpath {IS installation path}/ASBServer/lib/db2jcc4.jar com.ibm.db2.jcc.DB2Jcc -url 'jdbc:db2://<db2-host>:<db2-tls-port>/xmeta:sslConnection=true;useJDBC4DataType=2;useJDBC4ColumnNameAndLabelSemantics=2;' -user <xmeta-user-name> -password <xmeta-password>
The command must print "Test Connection Successful" message like following example.
C:\IBM\InformationServer>Updates\_jvm\bin\java -classpath ASBServer\lib\db2jcc.jar com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://mydb2.example.com:50001/xmeta:sslConnection=true -user xmeta -password secret!


[jcc][10521][13706]Command : java com.ibm.db2.jcc.DB2Jcc -url jdbc:db2://mydb2.example.com:50001/xmeta:sslConnection=true -user xmeta -password ********


[jcc][10516][13709]Test Connection Successful.

DB product version = SQL11012
DB product name = DB2/NT64
DB URL = jdbc:db2://mydb2.example.com:50001/xmeta
DB Drivername = IBM Data Server Driver for JDBC and SQLJ
DB OS Name = Windows Server 2016

C:\IBM\InformationServer>
Possible failure cases:
- DB2 TLS certificate is not in Updates/_jvm/lib/security/cacerts.
  Solution: Import DB2 TLS certificate into Updates/_jvm/lib/security/cacerts as per step 6.1.
- Key store password for Updates/_jvm/lib/security/cacerts is not changeit
  Solution: Reset the key store password to the installation default "changeit", for example, using "keytool -storepasswd -new changeit -keystore Updates/_jvm/lib/security/cacerts -storepass <current-password>" command.
- DB2 is configured to use TLS v1.1 or v1.0, but the JVM in Updates/_jvm does not support TLS v1.1 and older version.
  Solution: Configure DB2 to use TLS v1.2.

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSZJPZ","label":"InfoSphere Information Server"},"Component":"XMETA,DSODB","Platform":[{"code":"PF016","label":"Linux"}],"Version":"11.7.x","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
18 April 2023

UID

ibm10881025

Page Feedback