Configuring SSL to DB2 for i Source

When configuring an Agent to a DB2 for i source, the Add a Database wizard includes the Use SSL checkbox. The panel (and the database menu) have links to a page that lets the user manage the certificates used to establish SSL connections to a DB2 for i host. It is necessary to both select the Use SSL checkbox and add a Certificate Authority certificate from the DB2 for i host.

The instructions below describe using a CA certificate file supplied by an IBM i administrator (see Configuring SSL on DB2 for i) and importing it into the keystore used by SQDR Plus (using SQDR Control Center) and into the keystore used by iAccess (using the iKeyMan application).  Alternatively, you can use the Download button of the Secure Sockets panel of the properties of a connection in System i Navigator to retrieve and install the certificate directly from the IBM i host. Be sure to exit and restart System i Navigator before using the Verify SSL Connection button. Once the certificate is imported into the iKeyMan keystore, you can use the iKeyMan application to extract it as a Base64-encoded ASCII text file for importing into SQDR Plus Control Center.

Configuring SQDR Plus for SSL

The Certificate Management screen (http://127.0.0.1:8080/SQDRManager/?sqdr.option=cert) allows you to add and delete certificates from the keystore used by SQDR Plus.

The keystore file is stored in C:\ProgramData\StarQuest\sqdrplus (Windows) or /var/sqdrplus (Linux).

To add a certificate to the keystore:

  1. Obtain the Certificate Authority certificate from your IBM i administrator.

  2. The certificate is a text file; open it in Notepad or other text editor and copy and paste the entire certficate (including  -----BEGIN CERTIFICATE----- and -----END CERTIFICATE-----) into the contents window.

  3. Enter any name e.g. MYAS400 in the Alias text field.

  4. Click the Add Certificate button.

To delete the certificate from the keystore:

  1. Select the checkbox for the desired certificate.

  2. Select the Delete Certificate button.

To change an existing agent to use SSL, add the Certificate Authority certificate as described above and make the following changes to the agent's configuration:

  1. Select the existing agent and select the Configuration panel.

  2. Select the plus icon to add the property useSSL and set its value to true.

  3. Add secure=true to the sourceDbUrl property.

Configuring SQDR for SSL (StarSQL)

Specify an SSL connection when configuring the source in the SQDR Data Replicator Manager. For instance, a typical StarSQL connection string is

Server=MYRDB;HostName=myas400;Netlib=SQSSL.DLL;Port=448;IsolationLevel=2;PkgColID=SQDR

Configuring SQDR for SSL (iAccess)

Import the IBM i's CA Certificate using iKeyMan:

  1. Open the iKeyMan application using one of the following methods:

  1. Select the Open button (or select Open.. from the Key Database File menu) and select the cwbssldf.kdb file located in C:\Users\Public\Documents\IBM\Client Access\

  2. When prompted for a password, type ca400

  3. Use the pulldown in the middle of the panel to change from Personal Certificates to Signer Certificates. Then select Add...

  4. Browse to the certificate

  5. Enter a label name for the certificate; this can be any descriptive name.

See the IBM technical document How to Import a CA Certificate for System i Access for Windows Using IBM Key Management for details.

  1. In System i Navigator select the properties of the connection (or create a new connection)

  2. Select the Secure Sockets panel and select the checkbox Use Secure Sockets layer (SSL) for connection.  

  3. Select the Verify SSL Connection button to verify connectivity.

 

Specify an SSL connection when configuring the source in the SQDR Data Replicator Manager. For instance, a typical iAccess connection string is

System=MYSYS;CommitMode=1; DefaultLibraries={QGPL,SQDR};SSL=1;AllowDataCompression=1

Verifying that SSL is being

To verify that SSL is being using, use WRKTCPSTS on the IBM i host or netstat on the SQDR Plus system to view the ports being used for connections between the SQDR system and the IBM i host.

Secure connections will be using ports in the range 947x (for Java Toobox connections from SQDR Plus and iAccess connections from SQDR) and port 448 for DRDA (StarSQL or DB2 Connect) connections from SQDR.  If you see connections from the SQDR system in the range 847x or port 446, those connections have not been configured to use SSL.  You may also see transient connections for as-svrmap (server mapper - port 449).

WRKTCPSTS

  1. Enter WRKTCPSTS *CNN  (or enter WRKTCPSTS and select  3.  Work with IPv4 Connection Status )

  2. Look for connections from the IP address of the SQDR system: select F15 Subset and enter the IP address of the SQDR system as the Remote internet address to limit the display to connections from the SQDR system.

  3. Use F14 to display local ports as numeric; verify that all connections are using SSL ports (448 or 947x).

netstat

Windows: netstat -n | findstr AS400_IP_address

Linux: # nestat -an | grep AS400_IP_address

For more information see the IBM documentation for  Port numbers for host servers and server mapper.

TROUBLESHOOTING

SQLState: 08001
Message:  The application requester cannot establish the connection. (Remote host closed connection during handshake)
Vendor:   -99999

This indicates that the server certificate on the IBM i host has not been assigned to the applications.

 

Unable to find valid certification path to requested target
PKIX path Building failed

This indicates the local keystore is missing or it does not contain the IBM i's CA certificate. This error has also been observed after adding a certificate from a second IBM i system to an existing keystore file; this situation can be resolved by restarting the SQDR Jetty service.