It is a now common requirement for Db2 Z to provide secure and encrypted network communication to all clients. This includes end-user workstations which may run distributed tools like Data Studio or IBM Data Server client. And of course, this includes application servers who get application data from Db2 Z.
Encrypted network communication provides security against nefarious individuals who may have access to the internal company network but do not have access to Db2!
After all, the mainframe is almost never directly visible outside the company network. Network encryption is protecting us from people inside the network already!
I suppose the main fear is that such nefarious individuals may somehow sniff and see important userids and passwords and then use the userid to gain unauthorized access to Db2 data. And even more simply, it prevents such individuals from seeing sensitive or private application data flying back and forth over the network.
This IDUG content committee blog post provides an overview of my experiences in enabling my Db2 Z to encrypt network communication!
To get a good background of the big picture about why or how to enable network encryption, I had to do some research. Here are some of the resources used by me. I think you can find them useful!
The 2014 IBM Redpaper “DB2 for z/OS: Configuring TLS/SSL for Secure Client/Server Communications” provides the main reference for enabling Db2 Z network encryption.
Of course, there exists a wealth of useful presentations from past IDUG conferences and other sources
Encrypt or Die! How to Secure Db2 for z/OS DDF Traffic using SECPORT and AT-TLS
By James Gill – Triton Consulting > EMEA 2018
or link to replay
The 101 of Db2 client configuration – How db2cli makes a DBA’s life easier
Christoph Theisen – IBM > EMEA 2019 (track F Session 17)
REST Without Stress - How to Secure Db2 for z REST Services (and Db2 for z/OS in general) via AT-TLS
Christoph Theisen – IBM > EMEA 2020
Light at the end of the Encrypted Tunnel
Greg Steger – IBM Toronto Lab > Db2 Night Session Season 11 #217
Finally, as always, the IBM documentation website (formerly known as the Knowledge center) provides the final say with detailed reference information on what to do (not why, but how!)
Reading the above REDPAPER and presentations provides a decent background on what is necessary for configuring Db2 z/OS as a server that provides encrypted network via SECPORT.
The presentations go over some of the same details about configuring Db2 Z and then they also provide good details and options on how to configure client computers.
I am not going rehash in this blog post the details of how TLS and SSL work. I will not explain PKI (public-key-infrastructure) and certificates and public/private keys and certificate authorities (CA). I will not explain or diagram the back and forth on how a secure connection is established. All that is explained in the references described above AND in many other places throughout the internet.
Instead, here, I want to focus on the exact thoughts and decision-making process and all the steps required for me to change my Db2 to use secure network encryption.
As an application DBA, I did not have authority to do anything described in the REDPAPER! How complicated for me.
- Mainframe information security team needs to provide a new unsigned certificate for signing by the CA. Our decision was one per LPAR.
- The certificate signing (CA) authority needs to “sign” and send back the “signed” certificate.
- Mainframe information security needs to create a KEYRING and integrate the CA USERTRUST cert(s) and the signed Db2 cert
- Network services needs to configure TCPIP and PAGENT for the new port number and the name of KEYRING to use for that port.
- Finally, and almost trivially, the Db2 System DBA needs to update Db2 with the SECPORT
- Then the application DBA or keen developer can go through the steps to update the client configuration to use the Db2 SECPORT.
- As a bonus, you can produce reports to know who is using the regular Db2 open TCPPORT and who is using the SECPORT. And then go around to their desk and tell them to update their computer.
- At the end, once all the clients are updated to use SECPORT, then one can update Db2 system parameter TCPALVER to value SERVER_ENCRYPT which basically means that the Db2 Z server will not accept incoming DDF over the clear TCPPORT, instead it all must come in over the SECPORT.
The certificate process:
The REDPAPER (and others) discuss the options of signing your own certificate or using an established certificate authority.
After some deliberation with people who seem to know more then myself, we decided it is best to have use proper certificate that is signed by our proper (external) certificate signing authority.
Why? The other choice was to “self-sign” our own certificate. Sure, this involved fewer teams but it was more work (for me). And in the end, if this became official, we would need to use proper CA anyways. So rather then “self-sign”, we used our regular established processes to get a “proper” certificate.
You will need to provide the “subject distinguished name” attributes for the certificate.
- OU: OrganizationalUnit
- O: Organization
- L: Locality
- S: StateOrProvinceName
- C: CountryName
- Do not overthink the best value for C, O, OU. Just follow the pattern of existing request from your company. We put the CN as the fully qualified server name for the LPAR. (important later)
The (mainframe) information security team generates an unsigned certificate (using their security tool, RACF, ACF2 or whatever) and then this unsigned certificate is sent to our certificate authority (CA) for signing.
The CA “sign” the “unsigned certificate” (however they do that) and sends back a link to download the “signed” certificate.
Important to note, the signed certificate is often only valid for 1 or 2 years!
Now. at this point, mainframe information security needs to define a KEYRING. The syntax of the instructions for RACF and ACF2 (and others) can be found elsewhere. We decided to have one Db2 certificate and one keyring per LPAR.
Because our security is distinct per LPAR, we gave the Db2 Keyring the same name in every LPAR (easy).
Basically, information security creates a KEYRING, and inserts a few critical certificates.
- The CA ROOT (usertrust) certificate. in ACF2 it is marked as CERTAUTH
- The intermediate certificate. It ACF2 is it is marked CERTAUTH.
- The signed Db2 certificate for the LPAR. In ACF2 it is marked PERSONAL. In ACF2, it is important to make this particular CERTDATA record the “default” for the RING.
Minor digression about looking at certificates and see details.
If you are using a public CA and your local information security should be able to show you ROOT (usertrust) CA certificate because it is publicly known. You could download it to your windows laptop as USERTRUST.CER and double click and then the popup will show you some details. The fact that ‘ISSUED BY’ AND ‘ISSUED TO’ are the same makes it a “root” certificate. Also, you can google the first part of the actual certificate text (the first line) and you will see that copies of this USERTRUST root certificate CA are common knowledge. (it makes sense in the end)
The intermediate and your new Db2 certificate could be examined in the same way and you can see how the chain of “ISSUEDB BY” leads to “ISSUED TO”.
Next step, work with network team and configuring TCPIP and PAGENT.
The TCPIP started task has a PROFILE DD which lists all the ‘ports’ being used on the LPAR. The Db2 subsystem *DIST started task should already be listed for the regular open TCPPORT and the RESYNC PORT (all confirmed by Db2 command DISPLAY DDF DETAIL)
You can look at this list and decide on an available PORT number to be used for the Db2 SECPORT. Any available number can be used for SECPORT. It can be near the existing TCPPORT or 1000 away. It does not matter.
After the port number is decided, the network team essentially updates the TCPIP PROFILE dataset with this port number to be used for SECPORT by the Db2 *DIST started task.
The network team also updates PAGENT. In my shop PAGENT already exists and is running and working.
- The REDPAPER mentioned above goes through pages and pages of details about how to setup PAGENT the first time. Fortunately, I got to skip all those instructions. You probably get to skip that too.
Assuming PAGENT exists and is running and working, then the network team needs to update the TTLS config file with the information of the DB2 *DIST started task name, the PORT number to be used by the SECPORT and the KEYRING name! Other config options are required. But the important items for me are that task name, PORT and keyring!
- After the PAGENT is updated… if you have curiosity and permission, you can look at your PAGENT started task and confirm the config yourself. The started task probably runs PGM=PAGENT with PARM input that points to config DD (I bet the usual DD name is STDENV). Find that DD in the started task JCL and dig into that CTLCARD. It may have a (unnecessarily confusing) series of nested CTLCARDS with config details. When you dig a few level down, you might fine one with TTLSCONFIG and it points to a POLICY CTLCARD somewhere. You can browse that POLICY and see how all the Db2 Started task *DIST will be listed with the LocalPortRange set to the Db2 SECPORT number. This config will reference TTLSEnvironmentActionRef name which then points to the Db2 KEYRING name you defined earlier in mainframe security!
Important trivia about changing security or PAGENT.
If you ever change the KEYRING then it is probably best that the information security issue a “refresh” so the changed rules are reflected inside the currently running started task for the security software.
- This is how it works in ACF2. If they don’t “refresh” then the change is not yet actualized!
- I assume RACF has similar “refresh” requirement
Related, after changing KERYING rules then PAGENT must also be refreshed so PAGENT goes out to the security software and gets the latest details!
- I learned this the hard way because I was “experimenting” to see what happens with expired or invalid certificates inside my KEYRING. Information security promised me that the changes were done but the impact was not felt by application. After investigation, I realized PAGENT had cached the ACF2 rules on start-up and they needed to be refreshed by PAGENT! So PAGENT also needs to be “refreshed” after changes!
Set the SECPORT inside Db2 itself
The system DBA needs to set the SECPORT inside the Db2 itself.
This is subjectively the easiest step in the overall configuration. Details are in the REDPAPER.
Test and Confirm network encryption with Db2
And if all the above ducks are lined up and working then you should have a working encrypted Db2 secure port.
How can one validate?
There are 3 simple client software types
- REST clients
- DB2 CLI / ODBC clients
- Java type 4 JDBC clients (Data Studio, Visual Studio)
Assuming you setup REST for DB2 in the past (and you should have, if not, get to it) then the simplest REST test is from your browser using the default REST service “services” (which lists the REST services available). And the best part of testing the DB2 SECPORT with REST via your browser is that your browser and (windows) operating system default certificate search order probably automatically knows to check with YOUR USERTRUST ROOT CA certificate. YOUR browser does not need to be told about the ROOT USERTRUST certificate. It knows already.
Type something along the lines of this into your browser
where “my.mainframe.servername.com” is my local mainframe fully qualified server name.
- Use the same FQ server name as used when requesting the certificate! (it must match)
In my example above, the port number is 6025. You substitute your SECPORT number
You should get the usual response form this service.
- The response will be the same response you get using HTTP: and the open TCPPORT
Here is my sample call to Db2 REST service called “services” via TPCPORT
(2+3) The IBM Data service CLI client and JAVA type 4 JDBC driver do not use your local machine system defaults for finding certificates.
Why not? The creators of these forms of connection decided to not assume you want to find your certificates in the operating system trust store. You must explicitly give these two connection methods the ROOT CA (usertrust) certificate.
Not using the operating system trust-store is a feature and intentional. Some auditors and security people like that decision. It is a minor pain for you because you must provide this ROOT CA certificate.
The presentations (with links above) go into details about how to configure clients.
The REDPAPER also talks about using GSKIT and configuring client (the method of 2014).
But the presentations (especially Greg Steger) make it very clear that the modern and “easy” method for configuring clients is to forget GSKIT and trust-stores and all that hassle!
Simplified SSL setup! That seems like the future to me. Easy.
In brief, for each of these connection methods. For simplified SSL setup, you just need to explicitly pass parameter for two simple things:
- SSL Connection type is TRUE!
- The location of the ROOT CA (usertrust) certificate
(2) Db2 Data Server Client
As a bonus. When I re-read the presentations about DB2 client software and options. It became clear that the IBM Data Server RUNTIME client is overkill is most situations. The simple DS Driver for ODBC / CLI is probably the “best” Db2 Data Server Client/drive software choice for most cases. And if using the simple DS drive, then use the DB2CLI to config the Db2 Z connection details!
- It is subjectively ‘better’ to use DB2CLI instead of the old DB2CLI.INI or DB2 CLP commands available via the “large” Runtime Client.
Below is an example of using DB2CLI commands to configure a DB2 CLI client and register it with ODBC. The ROOT CA (usertrust) certificate is locally copied to a directory on the machine
db2cli writecfg add –dsn DB2SJD –port 6027 –host <my.mf.server.fqn> –database HCXXB2GW
db2cli writecfg add –dsn DB2SJD –parameter "SecurityTransportMode=SSL"
db2cli writecfg add –dsn DB2SJD –parameter "SSLServerCertificate=C:\mycertdir\cacert.CER"
db2cli registerdsn -add -DSN DB2SJD -SYSTEM
db2cli32 registerdsn -add -DSN DB2SJD -SYSTEM
minor Cavaet. You should use at least V11.1 fixpack 6 DS driver client!
I first used v11.1 fixpack 5 and it failed with the message in the APAR link below. Solution? Next fixpack!
(3) Java type 4 driver
IBM Data Studio (and MS VS Code) use Jave type 4 driver. Again, simple to use SSL
You must set the following properties.
sslConnection = True
sslCertLocation = C:\mycertdir\cacert.CER
- For Data Studio (and MS VS Code) the above properties are set in driver properties in the “optional” tab
After you proved it works… the logical next steps. Update ALL clients:
Logically, once the SECPORT and network encryption is working you should eventually hunt down all the clients that connect remotely to Db2 and have them update their configuration.
At first, it is easy to find these client computer names, because it is everyone!
But after a while, you may think you are done but some stragglers might be using the old clear TCPPORT.
How to find them?
Logically, you might think the accounting trace might contains something useful. But surprising (to me), it does not. There is an AHA-RFE to ask IBM to add something useful to the accounting trace
But, alternatively, and perhaps even better. The STATISTICS trace has a recent V12 enhancement that should apparently show client location details via IFCID 365
Of course, to easily look at this IFCID, it would be helpful if your Db2 monitor knows about this IFCID enhancement.
For Omegamon for DB2, this PTF improves the reporting by this tool for IFCID 365
Alternatively, TCPIP has client location name and actual port number used available via some TCPIP trace output (or so it is claimed by someone from my performance team)
Once all the clients stop using TCPPORT and switch to SECPORT then I can update my DB2 ZPARM for TCPALVER to value SERVER_ENCRYPT which will prevent anyone from using TCPPORT ever again.
And hopefully, at that time, the auditor will be happy (at least for a moment)
What about the Db2 site certificate that expires every year or two?
The signed Db2 certificate (per LPAR in my case) will expire every year or two. Fortunately, it is only used inside the KEYRING. The clients do not have a copy. So updating occurs in the one central location!
Some team (or person) must monitor the Db2 certificate and get a new one every year.
Perhaps the easiest way to minimize risk and manage this change is to do the following
- Get a new Db2 certificate signed by the CA
- Create a new KEYRING
- Update PAGENT for the Db2 to use the new Refresh PAGENT and validate with all client types.
- If there is any issue, then fallback is easy. The old keyring still exists… and the old certificate probably has some days left. So just update PAGENT again to point to the old KEYRING and refresh and then work out the issue