How to Protect Secure Websites & APIs from Certificates Expiring
If you have encrypted your website, API, Telnet server, or other service with TLS (SSL), good for you!
Now, do you know when those certificates will expire? If they expire without renewal, your service could become unavailable without warning.
In the past, to find expiration dates for digital certificates on IBM i, you’d either have to look in the Digital Certificate Manager (DCM), call the Retrieve Certificate Information (QYCURTVCI, QycuRetrieveCertificateInfo) API, or keep extremely good notes!
CERTIFICATE_INFO, an IBM i service recently delivered by IBM, solves all this. This SQL table function, documented here, returns a result table that contains information about server or Certificate Authority (CA) certificates, including their expiration date.
Here’s how to use CERTIFICATE_INFO to find expiring certificates.
Authorities needed
To call CERTIFICATE_INFO, the user must have *ALLOBJ and *SECADM special authorities and pass the certificate store password as a parameter.
Example: Certificates expiring one month out
The IBM documentation provides a practical example: listing certificates from the *SYSTEM certificate store (the default) that will expire within the next month. Note that the SQL below obtains the certificate store password from a global variable, which could be set elsewhere for security.
|
1 2 3 |
CREATE VARIABLE MYLIB.SYSTEM_CERT_PW VARCHAR(30); SET MYLIB.SYSTEM_CERT_PW = 'cert_pwd'; SELECT * FROM TABLE(QSYS2.CERTIFICATE_INFO(CERTIFICATE_STORE_PASSWORD=> MYLIB.SYSTEM_CERT_PW)) WHERE VALIDITY_END < CURRENT DATE + 1 MONTH; |
Running a simplified version of the example SQL on our system returned these results:
Apache & nginx certificate setup
For fast, reliable help updating your web server certificates, consider our SSL/TLS Install & Learn service. We will walk you through each step, including the configuration of web apps, APIs, FTP, Telenet, etc.
This is just one of many ways to help protect your IBM i when you offer APIs and other online access to your systems.
As reported in a previous article on Apache security fixes, we’re helping clients stay on top of web server security. If you’d like to set up a schedule with us, let me know.
We decided to use this table function to send a regular, automated email. However, we thought it would be much more useful if we could see which applications were assigned each certificate. Because this information is not available via SQL, we built a UDTF to do it.
I have copied a version of the code to GitHub. Included are an RPG module/program and UDTF creation statement, plus a cut-down version of the program we use to combine the information in a meaningful way. What’s missing from this is how we use an internal tool to email the information as an embedded table.
https://gist.github.com/zkarj735/05074c55aaf5ae4222b12751d255fb1c