IBM Db2 Query Monitor – certificates for CAE server in z/OS Unix
The IBM Db2 Query Monitor CAE server offers many advantages and features, such as web-based UI, consolidation of performance data from Db2 data sharing group members, alerting, automated actions, graphing, and much more.
The CAE server is a Java based server that can run under z/OS Unix or on a windows server. As it is Java-based it will exploit zIIP processors if available.
Many customers prefer to install the CAE server under Unix System Services as installing on a windows server requires provisioning a windows server, maintaining it (patching, upgrades, etc.), as well as additional network setup (firewalls). Typically, such work is carried out by separate groups in the enterprise making the installation and maintenance more complicated.
This series contains several blogs:
This blog series assumes that you are installing under z/OS Unix. For Windows installation please see the manual.
While using the CAE server several different network connections occur:
- From your web browser on your workstation to the CAE server
- Bi-directional communication between the CAE server and the CAE agents (within the LPAR or across LPARs in the sysplex)
- From the ISPF panels to the CAE agent (within the LPAR)
By default, Query Monitor uses a built-in encryption method for B (between CAE server and CAE agent) and C (between CAE agent and ISPF client) – see diagram above. You can optionally configure AT-TLS for communication B and C if desired. A future blog will cover more details on this.
As the CAE server is java-based it uses java libraries for TLS encryption which support industry standards, such as TLS v1.3 and 256+ bit encryption if you have applied the appropriate Java for z/OS maintenance. We will cover configuration of TLS versions and ciphers in a future blog.
Note: AT-TLS is not required for communication between the web browser and the CAE server (“A” in the diagram above).
Java on z/OS and hence the CAE server supports both file-based trust- and keystores and SAF (System Authentication Facility) keyrings.
A file based trust store or keystore is stored in the z/OS Unix file system and normally protected by a password. Many different formats exist such as Java Keystore (JKS) or PKCS12 (.p12). The store is maintained by z/OS Unix commands, e.g., “keytool”.
With a SAF keyring the certificates are stored in the ESM and the access to the keyring and certificates are protected by ESM permissions (instead of a password). For additional security the private key of a certificate can be stored in ICSF.
The recommendation is to use SAF keyrings for several reasons. One big advantage is having a central repository (the ESM) for all certificates. It is easy to identify certificates close to expiry, and have the certificates created or existing certificates re-keyed without involving the Query Monitor systems programmer. Most customers are already using SAF keyrings for other products such as z/OSMF and Db2 AT-TLS, so processes for managing keyrings and certificates are already in place.
Steps for setting up SAF keyrings with Db2 Query Monitor:
- Create SAF keyring with your certificates.
- Configure CAE server to use keyrings.
You should connect the following certificates to the keyring:
- TLS certificate used for the CAE server. The certificate should be a signed certificate valid for the LPAR(s) where you are planning to run the CAE server.
- The Root CA used to sign the TLS certificate (the Root CA is also installed on the user’s workstations, such that the web browser accepts the connection as secure)
- Optional: if you plan to connect to Query Monitor archive tables using an encrypted Db2 connection, the Root CA(s) used to sign the Db2 AT-TLS certificates
- Optional: if you plan to use Db2 Automation Tool’s REORG avoidance feature, you must also connect the Root CA used to sign the Db2 Automation Tool server certificate
- Optional: if you're planning to use AT-TLS between the CAE server and CAE agent, the Root CA for the AT-TLS certificate
Additional notes:
- Keyring owner: Normally the keyring is owned by the Query Monitor CAE started task user. If you are planning to use a keyring owned by a different user, you must apply QM (Query Monitor) APAR PH50828. In this case additional ESM (External Security Manager) permissions are also required: either UPDATE on IRR.DIGTCERT.LISTRING in class FACILITY or CONTROL on <keyring owner>.<keyring name>.LST in class RDATALIB.
- The TLS certificate must be connected with USAGE(PERSONAL) and Root CAs with USAGE(CERTAUTH). Connecting with USAGE(SITE) is not supported.
Query Monitor CAE server configuration example:
CQM_HTTPS_PORT=9423
CQM_CAE_KEYSTORE_TYPE=JCERACFKS
CQM_CAE_TRUSTSTORE=safkeyring:///CQMring
CQM_CAE_KEYSTORE=safkeyring:///CQMring
CQM_WEB_KEY_ALIAS=qmcaeservercert
Supported keystore types for keyrings are:
- JCERACFKS for certificates and keys stored in the ESM.
- JCECCARACFKS for certificates stored in the ESM with private key data in ICSF
The trust store and keystore parameters uses the “safkeyring://” syntax:
safkeyring://<keyring owner>/<keyring name>
If the keyring owner is the same as the started task user id, the keyring owner can be omitted:
safkeyring:///<keyring name>
Important: keyring names are case sensitive (!)
CQM_WEB_KEY_ALIAS contains the label of the certificate. By default, the CAE server will use the certificate connected with the default attribute if this parameter is not provided.
Important: Spaces in the certificate label are NOT supported by Query Monitor
Examples using RACF:
The following examples are using RACF, but Query Monitor works with all three major ESMs (RACF, TSS, and ACF2).
In all examples below we create a keyring “CQMring” owned by the started task user “CQMUSER”.
Example 1: The certificate and root CA are assumed to already be in RACF, so we only need to connect them to the keyring.
RACDCERT ADDRING(CQMring) ID(CQMUSER)
RACDCERT CONNECT(CERTAUTH LABEL(Rocket Software Root CA')) +
RING(CQMring) USAGE(CERTAUTH)) ID(CQMUSER)
RACDCERT CONNECT(SITE LABEL('qmcaeservercert') +
RING(CQMring) USAGE(PERSONAL) DEFAULT) ID(CQMUSER)
SETROPTS RACLIST(DIGTRING) REFRESH
RACDCERT ID(CQMUSER) LISTRING(CQMring)
Digital ring information for user CQMUSER:
Ring:
>CQMring<
Certificate Label Name Cert Owner USAGE DEFAULT
-------------------------------- ------------ -------- -------
qmcaeservercert SITE PERSONAL YES
Rocket Software Root CA CERTAUTH CERTAUTH NO
Example 2: Create a new CA and use it to sign a new certificate
In this example we first create a new CA and then use it to sign a TLS certificate.
The TLS certificate will use the default expiry of one year and contains the Subject Alt Names of the DNS names for the two LPARs as well as the hostname of a DVIPA.
The new CA and the new TLS certificate are both connected to a new keyring for Query Monitor.
RACDCERT ADDRING(CQMring) ID(CQMUSER)
RACDCERT GENCERT CERTAUTH +
SUBJECTSDN( +
CN('Db2 Query Monitor CA') +
) +
SIZE(2048) +
NOTAFTER(DATE(2030-05-01)) +
WITHLABEL('qmca') +
KEYUSAGE(CERTSIGN)
RACDCERT CONNECT(CERTAUTH LABEL('qmca')) +
RING(CQMring) USAGE(CERTAUTH)) ID(CQMUSER)
RACDCERT GENCERT ID(CQMUSER) +
SUBJECTSDN( +
CN('Db2 Query Monitor CAE Server certificate') +
OU('Db2 Query Monitor') +
O('Rocket Software') +
L('Waltham') +
SP('MA') +
C('US')) +
SIZE(2048) +
WITHLABEL('qmcaeservercert') +
KEYUSAGE(HANDSHAKE) +
ALTNAME(DOMAIN('qmdvipahost.rocketsoftware.com')) +
SIGNWITH(CERTAUTH LABEL('qmca'))
RACDCERT CONNECT(ID(CQMUSER) LABEL('qmcaeservercert') +
RING(CQMring) USAGE(PERSONAL) DEFAULT) ID(CQMUSER)
Example 3: import an externally signed certificate
Many customers have an internal or external certificate signing organization that creates the certificates and provides them in PKCS12 files, which can be imported into RACF.
RACDCERT ADDRING(CQMring) ID(CQMUSER)
RACDCERT ADD('HLQ.QMCERT.PKCS12') +
ID(QMUSER) +
WITHLABEL('qmcaeservercert') +
PASSWORD('passw0rd') +
TRUST
RACDCERT CONNECT(SITE LABEL('qmcaeservercert') +
RING(CQMring) USAGE(PERSONAL) DEFAULT) ID(CQMUSER)
/* The assumption is that the company external Root CA */
/* is already in RACF. */
/* If not, it would also have to be imported */
RACDCERT CONNECT(CERTAUTH LABEL('Company external Root CA')) +
RING(CQMring) USAGE(CERTAUTH)) ID(CQMUSER)
Important: Query Monitor CAE server does not support dynamic refresh of properties, so after changing any parameters or changing the keyring (e.g., after re-keying an expired certificate), you will need to stop and start the CAE server.
#IBMChampion