Stelo Technical Documents

SQDR Plus: Configuring Db2 LUW for SSL

Last Update: 17 April 2024
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL044

Abstract

In the four tier architecture model for SQDR, IBM Db2 for Linux, UNIX & Windows (Db2 LUW) is used on tier 2 to provide a staging database for incremental changes retrieved from the source database by the Capture Agent. In addition, it is typically used for the control database of the SQDR service running on tier 3.

Another usage is to function as a passthrough gateway to provide indirect access to internal Db2 and Informix servers, when tier 3 is running on a remote system e.g. a cloud service, as described in the scenarios in the technical document Distributed Network Examples. When used for this purpose, it is desirable to encrypt communications using SSL.

It may also be desirable to enable SSL when Db2 LUW is the source system (tier 1).

This technical document provides details on configuring SSL for Db2 LUW using a self-signed certificate, or using a certificate issued by a public Certificate Authority or an in-house corporate certificate server, and the corresponding configuration changes on the SQDR client.

• Db2 LUW (tier 1 or tier 2) - enable SSL (self-signed procedure)
  
• Db2 LUW (tier 1 or tier 2) - enable SSL (certificate request)
  
• Configuring SQDR Plus (tier 2) to connect to Db2 LUW host (tier1)
  
• Connecting to SQDR Plus (tier 2) from SQDR (tier3)
  
• On the client (tier 3) - refreshing the connection to tier 2 staging database (typical)
• Db2 LUW (tier 2) - enable gateway
  
• On the client (tier 3) - using tier 2 as a passthrough gateway to tier 1
  
• Troubleshooting
  

Solution

Db2 LUW (tier 1 or tier 2) - enable SSL (self-signed procedure)

The following procedure is sufficent when using Db2 LUW with SQDR Plus and StarSQL/Windows; however, if you plan to connect to Db2 LUW with StarSQL/UNIX or have other encryption needs, we recommend using the Certificate Signing Request (CSR) procedure below.

Use IBM GSKit (Global Security Kit) to

• Create a keystore
  
• Create a self-signed certificate in the keystore
• Export the certificate for use by the client

Then configure Db2 to use the certificate, enable SSL communications, and restart Db2.

To enforce TLS 1.2, use the following Db2 command:

db2 update dbm cfg using ssl_versions TLSv12

See the example Linux shell script or Windows batch file for performing these steps.

Db2 LUW (tier 1 or tier 2) - enable SSL (certificate request)

A more secure method of configuring SSL on Db2 LUW is to create a CSR (Certificate Signing Request) that is sent to a Certificate Authority (CA), which issues the server certificate. The certificate is then installed in the local certificate store. The CA may be a public Internet Certificate Authority, an corporate internal CA such as a Microsoft Certificate Server, or (in this simplified example), the local GSKit environment.

Use IBM GSKit (Global Security Kit) to

• Create a keystore
• Create a CA certificate
• Export the CA certificate for use by a client
  
• Create a certificate request
• Sign the certificate (this step is typically done by the CA rather than GSKit)
• Import the signed certificate into the keystore
• Export the certificate for use by the client

Then configure Db2 to use the certificate, enable SSL communications, and restart Db2.

See the example Linux shell script or Windows batch file for performing these steps.

Configuring SQDR Plus (tier 2) Agent to connect to Db2 LUW host (tier 1)

After SSL has been configured on the tier 1 host as documented above, obtain the following

• Listening Port for SSL (e.g. 50448)
• Export of the CA (Certficiate Authority) certificate (e.g. cacert.arm)

Using SQDR Control Center, select Manage Certificates from the Database menu and add the CA certificate.

When creating the Agent, select the Use SSL checkbox.

To change an existing Agent created with a non-SSL connection, edit the configuration and change the sourceDbPort (e.g. from 50000 to 50448) and the sourceDbUrl (changing the port number and appending the property sslConnection=true).

SQDR Plus (tier 2)

Configure tier 3 to tier 2 communication to use SSL:

Examine the agentODBCString property in SQ_PROPERTIES in the tier 1 source database; this contains the connection string that the SQDR service on tier 3 uses to connect to the Tier 2 staging database (SQDRPn).

Modify the connection string to use SSL and supply that string as the value for clientODBCString in the Agent configuration (use the Plus sign to add the clientODBCString property). For example, you may change:
DRIVER={StarSQL 32};HostName=mytier2.mydomain.com;Port=50000;
Server=SQDRP0;PkgColId=STARSQL
TO
DRIVER={StarSQL 32};HostName=mytier2.mydomain.com;Port=50448; Server=SQDRP0;PkgColId=STARSQL;Netlib=SQSSL.DLL

After saving the configuration and restarting the agent, the agentODBCString property in SQ_PROPERTIES in the tier 1 source database will contain the new connection string.

On the client (tier 3) - refreshing the connection to tier 2 staging database (typical)

After making the change to the connection string in the previous section, use Data Replicator Manager on tier 3 to refresh any existing source (so SQDR retrieves the revised connection string) by making any change to the source. For instance, you can change the notification address on the Advanced panel, click OK, and then change it back to the original value.

Db2 LUW (tier 2) - enable gateway

To configure a gateway to the tier 1 source system:

db2 catalog tcpip node dbserv remote dbserv server 50000
db2 catalog database mydb as mydb at node dbserv authentication SERVER

 

On the client (tier 3) - using tier 2 as a passthrough gateway to tier 1

• To configure a source using StarSQL and SSL, use a connection string like this:

HostName=mytier2;Netlib=SQSSL.DLL;Port=50448;Server=mydb;PkgColID=SQDR

• To use the IBM Db2 ODBC driver, use IBM GSKit to create a keystore and import the certificate exported on tier 2. Here is a sample batch file:

set GSK=C:\Program Files\ibm\gsk8\bin\gsk8capicmd_64

set DIR=C:\SSL
set ARM=mydbserver.arm
set KEYSTORE=keystore
set PASSWORD=mypassword
set LABEL=SelfSigned

mkdir %DIR%
cd %DIR%

"%GSK%" -keydb -create -db "%KEYSTORE%.kdb" -pw "%PASSWORD%" -stash

"%GSK%" -cert -add -db "%KEYSTORE%.kdb" -pw "%PASSWORD%" -label "%LABEL%" -file %ARM% -format ascii -fips

Use a connection string like this:

Hostname=mytier2;port=50448;Database=mydb;Protocol=TCPIP;
Authentication=SERVER;SECURITY=SSL;
Ssl_client_keystoredb=C:\SSL\keystore.kdb; Ssl_client_keystash=C:\SSL\keystore.sth

Troubleshooting

Some common errors that may appear in db2diag.log when using GSKit are:

GSKit Error 408: bad password for the keystore (The SSLClientKeystoreDBPassword value is wrong)

GSKit Error 414: incorrectly formatted certificate was received from the partner (the Certificiate Authority's certificate has not been added to the certificate store).

For more information, see the IBM documents Configuring SSL for IBM Data Server Driver for ODBC and CLI and Common GSKit errors.