StarQuest Technical Documents

Configuring SQDR Cloud Services

Last Update: 19 June 2017
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL027

Abstract

This technical document describes configuring SQDR Plus and SQDR on a virtual machine running on a cloud service as Microsoft Azure or Amazon Web Services (AWS), connecting to an in-house IBM i (iSeries) server that has been configured as described in Preparing IBM i for use with SQDR Cloud Services.

Solution

Prerequisites

You will need the following:

• Text file containing the IBM i's Certificate Authority (CA) certificate in Base64-encoded ASCII data format.
• Hostname or IP address of the firewall/router that will be forwarding iAccess/Java Toolbox traffic to the IBM i server.

Configuring SQDR Plus (tier 2)

Open the Certificate Management screen of SQDR Control Center by one of the following methods:

• Select Manage Certficates from the Database menu item.
• Select Manage Certficates from the Add Database wizard.
• Point a browser window to http://127.0.0.1:8080/SQDRManager/?sqdr.option=cert (or http://tier2-hostname:8080/SQDRManager/?sqdr.option=cert if you are accessing the SQDR Control Center remotely)

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.

After adding the Certificate Authority certificate, create an agent to the IBM i server, specifying the IP address or hostname of the router for Host Name, and selecting the Use SSL checkbox. The Database name should be the database name (RDB) of the IBM i source system.

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 (tier 3)

• Install iAccess for Windows or iAccess Client Solutions Windows Application Package and the latest service pack.
• Use Notepad or other text editor to edit C:\Windows\System32\drivers\etc\hosts and add a line:
  <public-IP address of the router> <as400name>
• Import the IBM i's CA Certificate using iKeyMan:
  1. 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>IBM Key Management:
  • Open the IBM System i Access for Windows folder on the desktop and select IBM Key Management:
  2. 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\
  3. When prompted for a password, type ca400
  4. Use the pulldown in the middle of the panel to change from Personal Certificates to Signer Certificates. Then select Add...
  5. Browse to the certificate
  6. 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.

• Create an iAccess connection
  1. Open System i Navigator and add a connection to the IBM i, specifying the AS/400 name that you used in etc\hosts for System. Note the Verify Connection button will not succeed.
  2. Right-click on the connection and select Properties.
  3. Select the Secure Sockets panel and select the checkbox Use Secure Sockets layer (SSL) for connection.

• Use ODBC Administrator to configure an ODBC data source; the IBM i system name will appear as an option in the drop-down menu for System. Make sure that Use Compression is enabled on the Performance tab.

• A typical connection string for an SQDR source looks like this:

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