Configuring application and reporting servers to use a secure connection to the database (Db2)

After you set up SSL on IBM® Db2®, configure WebSphere® Liberty and Cognos® to use the secure connection.

Before you begin

Ensure that you completed all steps in Setting up SSL on the database server (Db2).

Ensure that you know the SSL port number for Db2.

Procedure

Log on to the admin application server as the OpenPages installation user (opuser).
If you do not have the SSL certificate for Db2, run the following command:
```
openssl s_client -servername <db_server_url> -connect <db_server_url>:<db_server_ssl_port> </dev/null 2>/dev/null | \ 
sed -ne '/-BEGIN CERTIFICATE-/,/-END CERTIFICATE-/p' > <directory>/db2.cert;
```
- Replace <db_server_url> with the fully qualified domain name (FQDN) of the database server.
- Replace <db_server_ssl_port> with the SSL port for Db2.
- Replace <directory> with the full path to a directory on the application server. This directory is where the certificate is saved.

Import the Db2 certificate to the Java™ keystore on the application server.

For example:

/opt/ibm/java-x86_64-80/jre/bin/keytool -import \
        -alias db2_ssl \
        -trustcacerts \
        -file /home/opuser/db2.cert \
        -keystore /opt/ibm/java-x86_64-80/jre/lib/security/cacerts \
        -storepass <Java_password>

Import the Db2 certificate to the Liberty keystore on the application server.

If you are using vertical application servers, do this step on each of them.

For example:

/opt/ibm/java-x86_64-80/jre/bin/keytool -import \
        -alias db2_ssl \
        -trustcacerts \
        -file /home/opuser/db2.cert \
        -keystore /home/opuser/IBM/OpenPages/wlp-usr/servers/OpenPagesNodeServer1Server1/resources/security/key.p12 \
        -storetype PKCS12 \
        -storepass <password>

Modify the bootstrap.properties file.
1. Open the following file in a text editor: <OP_HOME>/wlp-usr/servers/<server_name>Server<#>/bootstrap.properties
2. Set the op.db2.ssl property to true.
3. Update the op.db2.portNumber property with the SSL port number for Db2.
Modify the aurora.properties file.
1. Open the following file in a text editor: <OP_HOME>/aurora/conf/aurora.properties
2. Update the database.PORT property with the SSL port number for Db2.
3. Update the database.URL property with the SSL port number for Db2. Append :sslConnection=true; to the URL.
  For example:
```
database.URL=jdbc\:db2\://op-appserver.ibm.com\:50001/OPX:sslConnection=true;
```

Import the Db2 certificate to the Java keystore on each reporting server.

For example, if you're using the Java that is provided with Cognos:

/usr/IBM/cognos/analytics/ibm-jre/jre/bin/keytool -import \
        -alias db2_ssl \
        -trustcacerts \
        -file /home/opuser/db2.cert \
        -keystore <COGNOS_HOME>/ibm-jre/jre/lib/security/cacerts \
        -storepass <Java_password>

Or if you're using the Java that is provided with OpenPages:

/opt/ibm/java-x86_64-80/jre/bin/keytool -import \
        -alias db2_ssl \
        -trustcacerts \
        -file /home/opuser/db2.cert \
        -keystore /opt/ibm/java-x86_64-80/jre/lib/security/cacerts \
        -storepass <Java_password>

Configure the IBM Db2 database client to use the secure connection. Do this step on each application server and reporting server.

Import the Db2 certificate to the Java keystore that is bundled with the Db2 client software.

For example:

/home/opuser/sqllib/java/jdk64/jre/bin/keytool -import \
        -alias db2_ssl \
        -trustcacerts \
        -file /home/opuser/db2.cert \
        -keystore /home/opuser/sqllib/java/jdk64/jre/lib/security/cacerts \
        -storepass <password>

Create a keystore for the Db2 client.

For example:

gsk8capicmd -keydb -create -db /home/opuser/sqllib/security/keystore/opx_db.p12 -pw "OpenPages1" -type pkcs12 -stash
db2 update dbm cfg using SSL_CLNT_KEYDB /home/opuser/sqllib/security/keystore/opx_db.p12
db2 update dbm cfg using SSL_CLNT_STASH /home/opuser/sqllib/security/keystore/opx_db.sth

Import the Db2 certificate.

For example:

gsk8capicmd -cert \
            -add \
            -db /home/opuser/sqllib/security/keystore/opx_db.p12 \
            -stashed \
            -label db2_ssl \
            -file /home/opuser/db2.cert \
            -format ascii \
            -fips

Recatalog the OpenPages node.

For example:

db2 uncatalog node openpage
db2 catalog TCPIP NODE openpage REMOTE op-dbserver.com server 50001 SECURITY SSL
db2cli writecfg add -dsn OPXSSL -database OPX -host op-dbserver.com -port 50001 -parameter 'SecurityTransportMode=SSL'

Restart the OpenPages application services.
Repeat steps 1 to 10 on each application server.
Update the database connection information in Cognos so that the reporting servers use SSL to connect to the OpenPages database.
1. Open IBM Cognos Administration. Select the OpenPages data source and edit the connection string.
  
  For more information, see Update Db2 database connection references for Cognos.
2. Click the JDBC tab.
3. Change Port number to the SSL port number for Db2.
4. In the JDBC Connection Parameter field, type sslConnection="true";.
5. Save your changes and exit IBM Cognos Administration.
6. Restart the reporting servers.