IBM Support

Generating and using self-signed certificates

How To


Summary

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 will be revoked on 30 October 2020. Plan to apply the new solution as soon as possible.

Steps

Attention:
If you are running IBM Db2 Analytics Accelerator for z/OS V7 and if you are confronted with symptoms related to the expiration of the subject certificate as described in APAR PH28723 or PH28727, please
  • Contact IBM support for help. The certificate issue will be resolved in a remote maintenance session requiring an outage of up to one hour.
  • Skip the following paragraphs (starting with "WARNING: ...") as they do NOT apply to you.
WARNING: The procedure causes a database outage as the script brings the database down and then back up again when the procedure is finished. The procedure takes 3 - 5 minutes to complete.
PROCEDURE
Perform the steps as the root user.
1. Download the new certificate generation script below and copy it to the system.
2. Install the script according to the product being patched. 
  • On IAS:
Copy the script to '/opt/ibm/appliance/storage/scratch' on the head node, make the script executable, and run the installation script as root:
docker exec -it dashDB bash /scratch/install_self-signed_cert.sh -p IIASdb
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
 
NOTE: If you connect to database from clients with ssl, follow Step 3 and Step 4 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 name of the file is 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 you extracted 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 you set up the manual procedure:
  • IBM Data Replication for Availability doesn’t work.

    WORKAROUND:

    To refresh the Db2 ssl certificate for IBM Data Replication for Availability:
    1. On the source system, in the Db2 container, as su-dsadm:
         a) 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
        b) Import local Db2 ssl certificate to the IBM Data Replication for Availability truststore:
    /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
    /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
       c) Import the target Db2 ssl certificate to the IBM Data Replication for Availability 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.

    /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
    /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
        d) 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
        e) Verify the Db2 certificate aliases in the IBM Data Replication for Availability 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
        f) Restart the IBM Data Replication for Availability server and web console.

           Run the restart commands as the dsadm user:

           i. The IBM Data Replication for Availability server:
    /opt/ibm/bludr/scripts/bin/bludr-restart.sh
          ii. 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 IBM Data Replication for Availability 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 system from which you want to invoke db_migrate_iias.
       1. Extract the self-signed root CA certificate from the other system.

           For more information, see the Procedure section.

       2. 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 of the Procedure section. There's no need to catalog the database.
  • While you're running the db_restore command from web_console, a 'Database restore failed on the web console' error appears. The restore 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.

Additional Information

Document Location

Worldwide

[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSHRBY","label":"IBM Integrated Analytics System"},"ARM Category":[{"code":"a8m0z0000001hWNAAY","label":"IBM Integrated Analytics System->Other"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"},{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSCJDQ","label":"IBM Db2 Warehouse"},"ARM Category":[{"code":"a8m500000008PkdAAE","label":"Connectivity"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Version(s)"}]

Product Synonym

IAS;IIAS;db2wh;dashDB

Document Information

Modified date:
07 October 2020

UID

ibm16334641