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 IBM iAccess for Windows and IBM Access Client Solutions (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.
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:
Obtain the Certificate Authority certificate from your IBM i administrator.
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.
Enter any name e.g. MYAS400 in the Alias text field.
Click the Add Certificate button.
To delete the certificate from the keystore:
Select the checkbox for the desired certificate.
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:
Select the existing agent and select the Configuration panel.
Select the plus icon to add the property useSSL and set its value to true.
Add secure=true to the sourceDbUrl property.
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
Import the IBM i's CA Certificate using cwbcossl (iAccess Certificate Authority Downloader).
Open a PC command prompt and enter the command CWBCOSSL. The following dialog will appear.
You can either
Enter the hostname of the IBM i system and select Start CA download from...
When prompted, enter credentials for the IBM i system. The user does not need any special privileges.
When prompted, answer Yes to the message Are you sure you want to trust all certificates issued by this Certificate Authority?
OR
Enter a descriptive name for Certificate Authority text label and select Store CA from file..
Browse to the certificate file and select Open.
You will receive the following message: Are you sure you want to trust all certificates issued by this Certificate Authority. Select Yes.
You will receive the following message: Key Database password Prompt. Type ca400 if the default password is still being used and select OK.
You can verify that the host servers are enabled for SSL connectivity by selecting SSL in the Verify Connections area.
For details, see the IBM document Using CWBCOSSL to Import Certificate Authority into the PC Key databases of Access for Windows.
The following method (using iKeyMan) is no longer available in recent versions of i Access Client Solutions.
Import the IBM i's CA Certificate using iKeyMan:
Open the iKeyMan application using one of the following methods:
Select Windows Start>Run, and type cwbuisxe.exe /ikeyman
Select Windows Start>Programs>IBM System i Access for Windows (or IBM Access Client Solutions)>IBM Key Management
Open the IBM System i Access for Windows folder on the desktop and select IBM Key Management
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\
When prompted for a password, type ca400
Use the pulldown in the middle of the panel to change from Personal Certificates to Signer Certificates. Then select Add...
Browse to the certificate.
Enter a label name for the certificate; this can be any descriptive name.
See the IBM technical document Importing a Third Party Certificate Authority to IBM Key Management for details.
To verify SSL connectivity with i Access Client Solutions:
Create an ODBC data source, enter the system name and select Connection Options to choose SSL. Then use the ODBC DSN in an application such as odbctest.
When configuring the source or destination in the SQDR Data Replicator Manager, use the SSL keyword. For example, a typical iAccess or ACS connection string is
System=MYSYS;CommitMode=1; DefaultLibraries={QGPL,SQDR};SSL=1;AllowDataCompression=1
To verify SSL connectivity with i Access for Windows:
In System i Navigator select the properties of the connection (or create a new connection)
Select the Secure Sockets panel and select the checkbox Use Secure Sockets layer (SSL) for connection.
Select the Verify SSL Connection button to verify connectivity.
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).
Enter WRKTCPSTS *CNN (or enter WRKTCPSTS and select 3. Work with IPv4 Connection Status )
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.
Use F14 to display local ports as numeric; verify that all connections are using SSL ports (448 or 947x).
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.
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.