Generating and using self-signed certificates

About this task

On 20 August 2020, the SSL Certificate on IAS and Db2 Warehouse systems expired. To get customers operational as quickly as possible, an IBM certificate was used. Since then, IBM has developed an updated SSL Certificate strategy using a self-signed certificate. Now that this procedure is available, we will be revoking the certificate used on 20 August. The certificate used is revoked on 26 September 2020. Plan to apply the new solution as soon as possible.
Note: Perform the steps as the root user.

Procedure

  1. Download the new certificate generation script and copy it to the system.
  2. Install the script according to the product being patched.
    • On IAS:
      1. Copy the script to the dashDB container on the head node and run the installation script:
        docker cp install_self_signed_cert.sh dashDB:/scratch
docker exec -it dashDB bash /scratch/install_self_signed_cert.sh -p IIASdb
      2. Copy the script to the web_console container on the head node and run the installation script:
        docker cp install_self_signed_cert.sh web_console:/scratch
docker exec -it web_console bash /scratch/install_self_signed_cert.sh -p IIASweb
    • On Db2 Warehouse:
      Copy the script to the Db2 Warehouse container on the head node and run the installation script:
      docker cp install_self_signed_cert.sh <container_name>:/tmp
docker exec -it <container_name> bash /tmp/install_self_signed_cert.sh -p Db2wh
    • On IDAA:
      Copy the script inside the container on the head node and run the installation script: 
      docker cp install_self_signed_cert.sh <container_name>:/tmp
docker exec -it <container_name> bash /tmp/install_self_signed_cert.sh -p IDAA
    Note: If you connect to database from clients with ssl, follow Step 3 and Step 4 from the Procedure section for client setup.
  3. Extract the self-signed root CA certificate.
    Go to CONNECT > Connection information on the web console to download the certificate. The file's named DigiCertGlobalRootCA.crt.
    The certificate is also available at /mnt/blumeta0/db2/ssl_keystore/rootCA.pem inside the database container. You can extract it to tmp with this command:
    docker cp dashDB:/mnt/blumeta0/db2/ssl_keystore/rootCA.pem /tmp
    or access it directly with 
    <host volume>/db2/ssl_keystore/rootCA.pem
  4. Set up the client to trust the certificate. For example, as described in Configuring TLS support in non-Java Db2 clients.
    • If you're using Db2 Warehouse client containers, you might use the db_catalog command instead of db2 catalog:
      db_catalog --add ma-bhpwr10-7.swg.usma.ibm.com --ssl
      For more information, see db_catalog command.

      After the setup, you must log out of the Db2 Warehouse client containers. Then, log in again for the configuration to take effect.
    Note: The certificates are valid only for the life of the Db2 Warehouse container. If the containers are redeployed after the manual procedure for any reasons (for example, enabling Guardium), you must perform the steps of this procedure again.

Example

Steps after extracting the root CA cert file to a current directory on client:
  1. Create a key database:
    gsk8capicmd_64 -keydb -create -db "mydbclient.kdb" -pw "password" -stash
  2. Add the signer certificate into the client key database:
    gsk8capicmd_64 -cert -add -db "mydbclient.kdb" -pw "password" -label "dbselfsigned" -file rootCA.pem -format ascii
  3. Use the ssl_clnt_keydb and ssl_clnt_stash configuration parameters to specify the client key-database and the stash file:
    db2 update dbm cfg using SSL_CLNT_KEYDB /mnt/clientdir/clienthome/db2inst1/mydbclient.kdb SSL_CLNT_STASH /mnt/clientdir/clienthome/db2inst1/mydbclient.sth
  4. Catalog the node and database so that client applications can establish TLS connections to them:
    db_catalog --add ma-bhpwr10-7.swg.usma.ibm.com --ssl --alias mydb
  5. Log out, log in and connect to the database:
    db2 connect to mydb user <db_username> using <password>

Known issues

After setting up the manual procedure:
  • BLUDR doesn’t work.

    WORKAROUND:

    To refresh the Db2 ssl certificate for BLUDR:
    1. On the source system, in the Db2 container, as su-dsadm:
      1. Ensure that BLUDR_SHARED_DIR is set:
        • On IAS or Db2 Warehouse on Cloud:
          BLUDR_SHARED_DIR=/head/bludr
        • On Db2 Warehouse:
          BLUDR_SHARED_DIR=/mnt/blumeta0/bludr
      2. Import local Db2 ssl certificate to the BLUDR truststore:
        1. /opt/ibm/java/jre/bin/keytool -delete -keystore ${BLUDR_SHARED_DIR}/certificates/cacerts -storepass "$(base64 -d ${BLUDR_SHARED_DIR}/certificates/truststore_pwd)" -alias db2_ssl -noprompt
        2. /opt/ibm/java/jre/bin/keytool -import -trustcacerts -keystore ${BLUDR_SHARED_DIR}/certificates/cacerts -storepass "$(base64 -d ${BLUDR_SHARED_DIR}/certificates/truststore_pwd)" -alias db2_ssl -noprompt -import -file /mnt/blumeta0/db2/ssl_keystore/rootCA.pem
      3. Import the target Db2 ssl certificate to the BLUDR truststore.

        The certificate is available at /mnt/blumeta0/db2/ssl_keystore/rootCA.pem inside the database container. You can extract the self-signed root CA certificate to your system. For more information, see the Procedure section.

        1. /opt/ibm/java/jre/bin/keytool -delete -keystore ${BLUDR_SHARED_DIR}/certificates/cacerts -storepass "$(base64 -d ${BLUDR_SHARED_DIR}/certificates/truststore_pwd)" -alias db2_ssl_<target_hostname> -noprompt
        2. /opt/ibm/java/jre/bin/keytool -import -trustcacerts -keystore ${BLUDR_SHARED_DIR}/certificates/cacerts -storepass "$(base64 -d ${BLUDR_SHARED_DIR}/certificates/truststore_pwd)" -alias db2_ssl_<target_hostname> -noprompt -import -file ${BLUDR_SHARED_DIR}/certificates/<target_hostname>_rootCA.pem
      4. As the root or db2inst1 user, import the remote target Db2 root certificate to the local Db2 ssl keystore for Db2CLP:
        opt/ibm/db2/V11.5.0.0/gskit/bin/gsk8capicmd_64 -cert -add -db /mnt/blumeta0/db2/ssl_keystore/bludb_ssl.kdb -stashed -label db2_ssl_rootCA_<target_hostname> -file ${BLUDR_SHARED_DIR}/certificates/<target_hostname>_rootCA.pem -format ascii -trust enable -fips
      5. Verify the Db2 certificate aliases in the BLUDR truststore :
        keytool -v -list -keystore ${BLUDR_SHARED_DIR}/certificates/cacerts  -storepass "$(base64 -d ${BLUDR_SHARED_DIR}/certificates/truststore_pwd)"  | grep Alias -A10 | grep db2 -A10
      6. Restart the bludr server and web console.

        Run the restart commands as the dsadm user:

        1. The bludr server:
          /opt/ibm/bludr/scripts/bin/bludr-restart.sh
        2. The web console:
          • On Db2 Warehouse:
            /opt/ibm/dsserver/bin/restart.sh
          • On IAS:
            • Restart the API server:
              systemctl restart apiserver
            • From the IAS console, on the platform
              ap apps disable webconsole
ap apps enable webconsole
    2. On the target system, do the same steps as for the source system. Import the source Db2 ssl certificate and local Db2 ssl certificate to the BLUDR truststore, and import the remote source Db2 root certificate for Db2CLP.
  • Lift to cloud doesn’t work.

    WORKAROUND:

    1. Restart the web console container:
      ap apps restart WEBCONSOLE
    2. Type y when prompted to restart the web console:
      WebConsole will be restarted. Are you sure you want to proceed? [y/N]:
y
Restart request sent successfully
  • The db_migrate_iias command doesn’t work with ssl.

    WORKAROUND:

    Run the commands from the source or target machine from which you want to invoke db_migrate_iias.
    1. As the db2inst1 user inside the db2wh container, verify that SSL_CLNT_KEYDB and SSL_CLNT_STASH are set. If they are not set, set them:
      db2 update dbm cfg using SSL_CLNT_KEYDB
/mnt/blumeta0/db2/ssl_keystore/bludb_ssl.kdb SSL_CLNT_STASH
/mnt/blumeta0/db2/ssl_keystore/bludb_ssl.sth
      Example:
      [db2inst1@ma-bhpwr5-8 - Db2wh /]$ db2 update dbm cfg using SSL_CLNT_KEYDB /mnt/blumeta0/db2/ssl_keystore/bludb_ssl.kdb SSL_CLNT_STASH /mnt/blumeta0/db2/ssl_keystore/bludb_ssl.sth
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
    2. Extract the self-signed root CA certificate from the other system.

      For more information, see the Procedure section.

    3. Add the certificate to the db2wh default truststore:
      [db2inst1@ma-bhpwr5-8 - Db2wh ~]$ /opt/ibm/db2/V11.5.0.0/gskit/bin/gsk8capicmd_64 -cert -add -db /mnt/blumeta0/db2/ssl_keystore/bludb_ssl.kdb -stashed -label ss_bluhel21 -file <fully_qualified_path_to_the_CA_cert_copied>  -format ascii -trust enable -fips
  • After a head node failover, the web console can’t access the database.

    WORKAROUND:

    You must run the installation script again in the web_console container on the new head node after head node failover:
    docker cp install_self_signed_cert.sh web_console:/scratch
docker exec -it web_console bash /scratch/install_self_signed_cert.sh -p IIASweb
  • The dbload and dbsql commands don’t work with ssl.

    WORKAROUND:

    Set up the client to trust the certificate as described in Step 3 and Step 4. There's no need to catalog the database.
  • While running the db_restore command from web_console, a Database restore failed on the web console. error appears. The restore actually succeeds in backend.

    WORKAROUND:

    To validate the progress of the restore operation, as bluadmin, run the db2 list utilities show detail command to establish if a restore is running.

    If a restore operation is in progress, the example output for each MLN is:
    Restore percentage output:
ID = 1
Type = RESTORE
Database Name = BLUDB
Member Number = 1
Description = db
Start Time = 09/02/2020 09:57:33.923808
State = Executing
Invocation Type = User
Progress Monitoring:
Completed Work = 922976256 bytes
Start Time = 09/02/2020 09:57:33.923813
    ID = 1
Type = RESTORE
Database Name = BLUDB
Member Number = 2
Description = db
Start Time = 09/02/2020 09:57:33.904257
State = Executing
Invocation Type = User
Progress Monitoring:
Completed Work = 922976256 bytes
Start Time = 09/02/2020 09:57:33.904264

    You can find a log file associated with this restore operation in the /opt/ibm/appliance/storage/scratch/bluadmin_BNR/logs directory directory. Review the logs in the most recent restore log and tracelog for progress details.