IBM Support

Connecting from WebSphere for z/OS to DB2 using the type 4 JDBC Driver over SSL

How To


Summary

This document describes the steps to enable SSL communication from WebSphere Application Server for z/OS to DB2 using type 4 JDBC driver.

The program flow is illustrated as:

WebSphere Application Server for z/OS (Servant region) -> SSL -> (AT-TLS) PolicyAgent -> DDF -> DB2

where the (AT-TLS) configuration is set up for inbound connections to Distributed Data Facility (DDF).




Objective

1. Create the DB2 JDBC Provider
2. Configure the Datasource
3. Execute the RACF commands to install certificates on the WebSphere Application Server for z/OS Servant region keyring
4. Run the sample application to connect to DB2 over SSL
5. Configure mutual authentication (optional)
6. SSL and Client Authentication configured with JDBC properties (alternate configuration)

Environment

This document assumes the setup for Pagent address space has already been done, and that
SSL and ClientAuthentication to DDF outside of WebSphere for z/OS has been completed.
In this example, DB2 is listening on port 448 for SSL, and port 449 for SSL with Client Authentication.
The Keyring, HandShakeRole, and ClientAuthType are in bold to emphasize the differences between SSL only on port 448 and SSL with Client Authentication on port 449.

For reference:
---------------------------------------------------------------------------------
TTLSRule DB2BRule448                               
{                                                  
   LocalPortRange           448                    
   JobName                  DB2BDIST               
   Direction                Inbound                
   TTLSGroupActionRef       DB2AGroup              
   TTLSEnvironmentActionRef DB2ServerSSLEnvironment
}                                                  

TTLSEnvironmentAction       DB2ServerSSLEnvironment         
{                                                          
   TTLSKeyRingParms                                        
   {                                                       
       Keyring              DB2DDFRING                     
   }                                                       
   HandShakeRole            Server                         
   TTLSCipherParms                                         
   {                                                       
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA   
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA   
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA256
       V3CipherSuites       TLS_RSA_WITH_AES_128_CBC_SHA   
       V3CipherSuites       TLS_RSA_WITH_AES_128_CBC_SHA256
   }                       
}    
---------------------------------------------------------------------------------
and
---------------------------------------------------------------------------------
TTLSRule DB2BRule449
{
   LocalPortRange           449
   JobName                  DB2BDIST
   Direction                Inbound
   TTLSGroupActionRef       DB2AGroup
   TTLSEnvironmentActionRef DB2ClientCertSSLEnvironment
}

TTLSEnvironmentAction      DB2ClientCertSSLEnvironment
{
   TTLSKeyRingParms
   {
       Keyring              DB2DDFRING
   }
   HandShakeRole            ServerWithClientAuth
   TTLSCipherParms
   {
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA
       V3CipherSuites       TLS_RSA_WITH_AES_256_CBC_SHA256
       V3CipherSuites       TLS_RSA_WITH_AES_128_CBC_SHA
       V3CipherSuites       TLS_RSA_WITH_AES_128_CBC_SHA256
   }
   TTLSEnvironmentAdvancedParms
   {
       ClientAuthType SAFCheck
   }
}            
---------------------------------------------------------------------------------    

Steps

1. Create the DB2 JDBC Provider
a. In the administrative console, create the JDBC Provider using the following path:
JDBC -> JDBC Providers
Set your scope (server scope)
JDBC -> JDBC Providers -> New
Database Type = DB2
Provider Type = DB2 Universal JDBC Provider (for JDBC 3.0)
                              or 
                              DB2 Using IBM JCC Driver (for JDBC 4.0)
Implementation Type = Connection Pool Datasource or XA data source
b. Create the environment variable
Environment -> WebSphere Variables
DB2_JCC_DRIVER_PATH = absolute path to the directory containing db2jcc4.jar
or
DB2UNIVERSAL_JDBC_DRIVER_PATH = absolute path to the directory containing db2jcc.jar
For reference:
2. Configure the datasource
a. In the administrative console, create the Datasource using the following path:
JDBC -> Datasources
Data source name  = a unique Datasource name ( DB2JCC)
JNDI name = a unique JNDI name  (jdbc/DB2JCC)

b. Select an existing JDBC Provider (from Step 1)

Driver Type = 4
Database Name = DB2 database name
Server name = DB2 hostname
Port Number = DB2 SSL port (448)
c. Edit the custom properties to enable SSL for the datasource
Data sources > DB2JCC > Custom properties
sslConnection=true
securityMechanism=13
Numeric values can be found for property db2.jcc.securityMechanism
Select the Security mechanism referencing:
Security under the IBM Data Server Driver for JDBC and SQLJ
In this example, the following value was used for the securityMechanism.
13 ENCRYPTED_USER_PASSWORD_AND_DATA_SECURITY
Note:  Any changes above require a restart of the server.
3. Execute the RACF commands to install certificates on the WebSphere Application Server  for z/OS Servant region keyring
a.  Upload the DB2 Signer certificate to a dataset (SIGNER.DER) and add it to the RACF database
RACDCERT CERTAUTH ADD('SIGNER.DER') TRUST withlabel('DB2Signer')
b. Issue the RACF command to connect the DB2Signer certificate and any intermediate signer certificates to the WebSphere Application Server Servant region keyring.
RACDCERT ID(ASSR1) CONNECT(RING(WASKeyring2048) LABEL('DB2Signer') CERTAUTH)
c. List the contents of the Servant region keyring to confirm the DB2Signer was connected with owner CERTAUTH and usage CERTAUTH
RACDCERT LISTRING(WASKeyring2048) ID(ASSR1)
Digital ring information for user ASSR1:
Ring:                                                                 
     >WASKeyring2048<                                                 
Certificate Label Name             Cert Owner     USAGE      DEFAULT  
--------------------------------   ------------   --------   -------  
WebSphereCA2048                    CERTAUTH       CERTAUTH     NO     
DB2Signer                                     CERTAUTH       CERTAUTH     NO     <---- Certificate that signed the personal certificate
4. Install the diagnostic DB2TestEAR.ear
a. Install the diagnostic DB2TestEAR.ear
Applications -> WebSphere Applications -> Install
Choose the defaults when installing the application

b. Make sure the resource reference in the application points to the JNDI name of your datasource.
You can locate it at:
Enterprise Applications > DB2TestEAR > Resource references
Resource Reference = jdbc/MyDB
Target Resource JNDI Name  = jdbc/DB2JCC
4. Run the sample application to connect to DB2 over SSL

Invoke the application
http://host:port/DB2TestWAR
and click link
DB2TestConnectionServlet
which will attempt to get a connection using the datasource defined in WebSphere that you mapped to the resource reference.
The main page will appear as:
DB2TestWAR Main Page
Successful sample output
DB2TestWAR Successful Connection
Failing sample output
With no trace enabled and a Servant region keyring is missing the correct signer certificate, the Servant region will show an error similar to:
Trace: 2020/05/28 23:54:51.638 02 t=8C6E00 c=UNK key=P8 tag= (13007004)        
  SourceId: com.ibm.ws.ssl.core.WSX509TrustManager                             
  ExtendedMessage: CWPKI0022E: SSL HANDSHAKE FAILURE: 
A signer with SubjectDN "CN=www.ibm.com, O=IBM,  ST=New York, C=US"
was sent from target host:port "DB2_HOSTNAME:443". 
The signer may need to be added to local truststore "safkeyring:///WASKeyring2048".....
5. Configure mutual authentication (optional)
a.  Select an existing JDBC Provider (from Step 1)

Driver Type = 4
Database Name = DB2 database name
Server name = DB2 hostname
Port Number = DB2 SSL port (449)
b. Edit the custom properties to enable SSL with Client Certificate Security for the datasource,
Data sources > DB2JCC > Custom properties
sslConnection=true
securityMechanism=18
where the following value used for the securityMechanism is:
18 TLS_CLIENT_CERTIFICATE_SECURITY
c. Upload the DB2 Personal certificate to a dataset (PERSONAL.DER) and add it to the RACF database
RACDCERT ID(ASSR1) ADD('Personal.DER') TRUST withlabel('DB2Personal') PASSWORD('XXXXXX')

The personal certificate should be a password protected pkcs12 file containing the private key.
d. Issue the RACF command to connect the DB2Personal certificate to the WebSphere Servant region's keyring.
RACDCERT ID(ASSR1) CONNECT(LABEL('DB2Personal') RING(WASKeyring2048))
e. List the contents of the Servant region keyring to confirm the DB2Personal certificate was connected with cert owner userid and usage PERSONAL
RACDCERT LISTRING(WASKeyring2048) ID(ASSR1)
Digital ring information for user ASSR1
Ring:                                                                      
     >WASKeyring2048<                                                      
Certificate Label Name             Cert Owner     USAGE      DEFAULT       
--------------------------------   ------------   --------   -------       
WebSphereCA2048                    CERTAUTH       CERTAUTH     NO          
DB2Signer                                     CERTAUTH       CERTAUTH     NO          
DB2Personal                                 ID(ASSR1)      PERSONAL     NO   <--- Personal certificate used for mutual authentication.
If the personal certificate is owned by the same userid as the Servant region keyring, WebSphere should have no issues accessing the personal certificate and its private key with the default setup of:
READ access to FACILITY classes
IRR.DIGTCERT.LIST
and
IRR.DIGTCERT.LISTRING
This setup is sufficient when WebSphere for z/OS is using a different RACF database than Pagent,DDF and DB2, as the personal certificate will be installed or mapped under a different userid on the remote RACF database.
f. RACF commands for personal certificate owned by a different userid then servant region userid.
If WebSphere for z/OS, Pagent, DDF and DB2 share the same RACF database, and the DB2 personal certificate is owned by a userid (DB2USER)  that is different from the Servant region userid (ASSR1), it will be necessary to issue the following RACF commands to allow the Servant region userid access to its own keyring and to the private key of the personal certificate.
RACDCERT LISTRING(WASKeyring2048) ID(ASSR1)
Digital ring information for user ASSR1
Ring:                                                                      
     >WASKeyring2048<                                                      
Certificate Label Name             Cert Owner            USAGE           DEFAULT       
--------------------------------   ------------             --------           -------       
WebSphereCA2048                    CERTAUTH            CERTAUTH     NO          
DB2Signer                                     CERTAUTH            CERTAUTH     NO          
DB2Personal                                 ID(DB2USER)      PERSONAL     NO   <--- Personal certificate used for mutual authentication.
------------
//Create the RDATALIB class for keyring WASKeyring2048 owned by user ASSR1
RDEFINE RDATALIB ASSR1.WASKeyring2048.LST UACC(NONE)
//Allow ASSR1 to access the keyring owned by ASSR1
PERMIT ASSR1.WASKeyring2048.LST CLASS(RDATALIB) ID(ASSR1) ACC(CONTROL)
//Allow ASSR1 to access the private key of DB2USER
PERMIT ASSR1.WASKeyring2048.LST CLASS(RDATALIB) ID(DB2USER) ACC(CONTROL)
//Refresh the RDATALIB class
SETR RACLIST(RDATALIB) REFRESH
---------
g.  Confirm that the DB2Personal certificate is accessible by the administrative console.
SSL certificate and key management > Keystores and certificates > NodeDefaultKeyStore > Personal certificates
If the Personal certificate is not visible after restarting the server, then revisit the FACILITY classes
IRR.DIGTCERT.LIST or IRR.DIGTCERT.LISTRING, or RDATALIB settings.
h. Set the default personal certificate (DB2Personal) to be used for outbound mutual authentication.
SSL certificate and key management > Manage endpoint security configurations
In the outbound tree, select the
cell/cluster/node/server scoped SSL Configuration for the server that will use this default personal certificate for outbound mutual authentication.

In this example it is: bnode1(NodeDefaultSSLSettings)

Select the Update Certificate Alias list to repopulate the drop-down menu from RACF
Select the personal certificate (DB2Personal) in the drop-down for
Certificate alias in keystore
g. Confirm the default personal certificate is set on the admin console.
SSL certificate and key management > Manage endpoint security configurations
The default personal certificate will show up after the SSL configuration name
In this example: bnode1(NodeDefaultSSLSettings,DB2Personal)
Retry the DB2TestEAR application to confirm that DB2 is accessible using mutual authentication.

6. SSL and Client Authentication configured with JDBC properties (alternate configuration)
 
In steps 1 - 5, the WebSphere server is managing the setup of the SSL using the SSL Configuration. 
The signer certificate and personal certificate used for outbound SSL and client authentication are set at the server level using the clientKeyAlias, or based on outbound host, protocol and port using an Dynamic outbound SSL Configuration.   
 
The JDBC driver provides properties that allow you to set up a truststore at the datasource level, which might be needed in scenarios where there are many applications running in a WebSphere server and each application needs its own personal certificate for outbound client authentication. 
 
If each application has a resource reference, unique datasource, unique keyring, and a unique personal certificate on the keyring, then mapping the application to a the unique datasource allows each application to have a unique personal certificate to connect to Db2. 
 
When setting up the truststore using JDBC properties, the SSL connections are not managed by the application server, but by the JDBC driver. 
a. Create a new datasource keyring (DSKeyring) and connect the DB2 certificates to the keyring.
RACDCERT ADDRING(DSKeyring) ID(ASSR1)
RACDCERT ID(ASSR1) CONNECT(RING(DSKeyring) LABEL('DB2Signer') CERTAUTH)
RACDCERT ID(ASSR1) CONNECT(LABEL('DB2Personal') RING(DSKeyring))
RACDCERT LISTRING(DSKeyring) ID(ASSR1)

b. List the contents of the WebSphere server keyring and datasource keyring
RACDCERT LISTRING(DSKeyring) ID(ASSR1) 
Digital ring information for user ASSR1:
Ring:                                                                 
     >DSKeyring<                                                      
Certificate Label Name             Cert Owner     USAGE      DEFAULT  
--------------------------------   ------------   --------   -------  
DB2Signer                          CERTAUTH       CERTAUTH     NO     
DB2Personal                        ID(ASSR1)      PERSONAL     NO     
c. Update the Db2 jcc properties for SSL. 
Resources > JDBC > Data sources > DataSource > Custom properties
sslConnection="true"
securityMechanism="13"
sslTrustStoreLocation="safkeyring:///DSKeyring"
sslTrustStoreType="JCERACFKS"
sslTrustStorePassword="password"
Retry the DB2TestEAR application to confirm that Db2 is accessible using SSL.
d. Update the Db2 jcc properties for SSL with client authentication. 
Resources > JDBC > Data sources > DataSource > Custom properties
sslConnection="true"
securityMechanism="18"
sslTrustStoreLocation="safkeyring:///DSKeyring"
sslTrustStoreType="JCERACFKS"
sslTrustStorePassword="password"
Note that when specifying safkeyring:///DSKeyring the keyring owner is the address space owner (ASSR1) which is the equivalent of specifying safkeyring://ASSR1/DSKeyring.  If the keyring is owned by another user, update the user in safkeyring://NEWUSER/DSKeyring and follow the prior steps to setup RDATALIB access.  The password for sslTrustStorePassword must be literal string "password". 
 
Retry the DB2TestEAR application to confirm that Db2 is accessible using mutual authentication.

Additional Information

References:

Document Location

Worldwide

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS7K4U","label":"WebSphere Application Server for z\/OS"},"ARM Category":[{"code":"a8m50000000Cd9aAAC","label":"J2C-ConnectionPooling-JDBCDrivers"},{"code":"a8m50000000CcyMAAS","label":"Security-\u003ESSL"}],"ARM Case Number":"","Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"All Version(s)","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
19 September 2022

UID

ibm16234264

Page Feedback