Replicate-DB2z SSL Configuration

[David_Fergen]

Abstract: 

This knowledge base article takes a look at the client side setup for SSL connections from the Qlik Replicate server to a DB2/Z source endpoint.    It assumes that that DB2 on z/OS (the server side) has already been set up according to IBM documentation and is listening for incoming secure connections.  It also assumes that the network connections (routers and firewalls) between the mainframe and the Replicate server have been configured to support the necessary socket ports and protocols. 

  

Prerequisites: 

The following software packages need to be installed on the Replicate Server in order for the endpoint (client) to complete a secure connection to DB2 for z/OS: 

- DB2 CLI / ODBC V11.1 Driver or above from IBM (ex: v11.1.4fp4a_ntx64_odbc_cli.zip) 

- GSKit 8 or above from IBM (ex: 8.0.55.4-ISS-GSKIT-WinX64-FP004.zip) – note that some DB2 client driver kits come with a subset of GSK, making this prereq optional. 

  

Configuring the Client: 

One or more of the following steps may be required, depending on platform and approach to DB2 DSN setup: 

• Db2dsdriver.cfg – in this example we are creating a DSN named DB2T  (enter your own ip address and port) 

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>  

<configuration> 

   <dsncollection> 

      <dsn alias="DB2T" host="<ipaddress>" name="DSNDB2T" port="<port>"> 

         <parameter name="SecurityTransportMode" value="SSL"/> 

         <parameter name="SSLClientKeystoredb" value="c:\temp\keystore.kdb"/> 

         <parameter name="SSLClientKeystash" value="c:\temp\keystore.sth"/> 

      </dsn> 

   </dsncollection> 

 

   <databases> 

      <database host="<ipaddress>" name="DSNDB2T" port="<port>"> 

         <wlb> 

            <parameter name="enableWLB" value="false"/> 

         </wlb> 

         <acr> 

            <parameter name="enableACR" value="true"/> 

         </acr> 

      </database> 

   </databases> 

</configuration> 

  

• Create the ODBC DSN (windows screenshots, linux also has wizards if desktop is installed, or manual setup of odbc.ini and odbcinst.ini: 

Select IBM DB2 ODBC DRIVER and then Finish 

Insert the Database Name in the Data source name Field and then OK 

Open the just created System DSN, change to Advanced Settings and remove the DBAlias Entry.  

• Create a keystore and add a certificate 

gsk8capicmd -keydb -create -db c:\temp\keystore.kdb -pw ser123 -stash 

• Add a root certificate 

gsk8capicmd -cert -create -db c:\temp\keystore.kdb -pw serv123 -label "SERVCA1" -dn "CN=SERVCA1" 

  

• Verify the keystore 

  

gsk8capicmd -cert -list -db c:\temp\keystore.kdb -pw serv123 

Certificates found 

* default, - personal, ! trusted, # secret key 

-       SERVCA1 

• If using GSK_MS_CERTIFICATE_STORE (Windows Only) 
  • Extract root certificate: 

gsk8capicmd -cert -extract -db c:\temp\keystore.kdb -pw serv123 -label "SERVCA1" -target c:\temp\temp1.arm   

• Add to the MS CERTIFICATE STORE 

gsk8capicmd -cert -add -db GSK_MS_CERTIFICATE_STORE -label "SERVCA1" -file c:\temp\temp1.arm 

• Make reference to the MS CERTIFICATE STORE in the db2dsdriver.cfg configuration: 

<parameter name="SSLClientKeystoredb" value="GSK_MS_CERTIFICATE_STORE"/> 

• Configure the Endpoint 

• Internal Parameters (Advanced Tab) 

additionalConnectionProperties:   

Security=SSL 

SSLClientKeystoredb=GSK_MS_CERTIFICATE_STORE  (or actual kdb file) 

CURSORHOLD=1 

DESCRIBEPARAM=1 

CONCURRENTACCESSRESOLUTION=1 

More Information: 

Additional information can be found at the following links: 

GSK User Guide:   ftp://ftp.software.ibm.com/software/webserver/appserv/library/v80/GSK_CapiCmd_UserGuide.pdf 

Example use case:  https://www.ibm.com/support/knowledgecenter/en/SSYQBZ_9.6.0/com.ibm.doors.configuring.doc/topics/r_e...