FairCom DB Notify

Open a command prompt or console.

Change to the <faircom>\Tools\certman\ directory.

Create the CA certificate and key files. Run createcacert.py with appropriate argument values:

python createcacert.py --certManagementFolder Certs --outCertFile ca.crt --outKeyFile ca.key --singleFile False --months 120 --bits 2048 --commonName "Root CA" --org MyCompany --country US --state MyState --location MyCity --unit CompanyGroup

The output indicates where the files were saved.

Successfully created and saved 2 of 2 files:
  <faircom>/tools/certman/Certs/_ca_key/ca.key
  <faircom>/tools/certman/Certs/Expires_On_<future date>/ca.crt

Copy the ca.crt file into the current directory:

copy <faircom>/tools/certman/Certs/Expires_On_<future date>/ca.crt .

Create a server certificate file for the FairCom DB server. Run createservercert.py with appropriate argument values:

python createservercert.py --certManagementFolder Certs --outCertFile server.pem --singleFile True --months 48 --bits 2048 --cipher sha256 --commonName MyServer --altName localhost 127.0.0.1 ::1

--altName should include all hostnames and IP addresses you use when connecting to this server. This example shows three addresses, each separated by a space character.

--commonName is typically set to the hostname of the server.

Some settings, such as --org, are not included here because they were saved when the CA certificate was created, and future commands will use the saved settings.

Append the CA certificate to the server certificate file:

type ca.crt >> server.pem

Create a client certificate for the admin user. Run createclientcert.py with the appropriate argument values:

python createclientcert.py --certManagementFolder Certs --singleFile True --months 24 --bits 2048 --outCertFile admin_client.pem --commonName admin

--commonName designates which FairCom account is associated with this client certificate.

Append the CA certificate to the client certificate file:

type ca.crt >> admin_client.pem

Create a client certificate for each user. Run createclientcert.py with the appropriate argument values:

python createclientcert.py --certManagementFolder Certs --outCertFile john_doe_client.pem --singleFile True --months 12 --bits 2048 --commonName JohnDoe

--commonName designates which FairCom account is associated with this certificate.

Append the CA certificate to the client certificate file:

type ca.crt >> john_doe_client.pem

To test your TCP/IP TLS connection, deactivate the Shared Memory Protocol even if you are connecting with TCP/IP internally. This is currently required for using TLS with client authentication. If the Shared Memory protocol is activated, the Shared Memory is automatically used when possible. Deactivate Shared Memory by commenting out its keyword in the <faircom>\config\ctsrvr.cfg file.

; Communication protocols
; COMM_PROTOCOL           FSHAREMM

Deny shared memory for SQL also by adding the following line:

SQL_OPTION NO_SHARED_MEMORY

Activate SSL in the FairCom MQ broker by uncommenting the SSL and x509 support in the<faircom>\config\ctsrvr.cfg file.

;Here is where you can activate (un-comment)SSL 
SUBSYSTEM COMM_PROTOCOL SSL {

;This is the file name in your server's directory
SERVER_CERTIFICATE_FILE server.pem

;For SSL you can specify (un-comment) a debug log file name
DEBUG_LOG ssl.log

;Here you can restrict access to SSL ONLY. 
SSL_CONNECTIONS_ONLY YES

;Require clients to provide a x509 certificate
VERIFY_CLIENT_CERTIFICATE YES

;Use x509 client certificate for database authentication
x509_AUTHENTICATION YES

;Use the SUBJECT:CN from the client's certificate as their username
x509_PATH CN

;SSL_CIPHERS AES256-SHA256:AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:AES256-GCM-SHA384
}

Save your changes to ctsrvr.cfg.

If using Linux/Unix, or macOS, update <faircom>\server\stopserver to stop the server when using TLS.

#!/bin/sh

echo Stopping the FairCom Database Engine...
CTSSL_CLIENT_CERTIFICATE=admin_client.pem
export CTSSL_CLIENT_CERTIFICATE
CTSSL_CLIENT_KEY=admin_client.pem
export CTSSL_CLIENT_KEY

cd ../tools
./ctstop -AUTO none none "FCEDGEMQ@localhost^fssltcp"

Copy server.pem, admin_client.pem, and john_doe_client.pem to the <faircom>\server\ directory.

Restart FairCom MQ. 

The log message "Initialized SSL support." is written to CTSTATUS.FCS.

Check that the server's configuration file (ctsrvr.cfg) allows only SSL connections.

SSL_CONNECTIONS_ONLY YES

Launch two different CMD shells and run these two commands in each of the CMD shells to set environment variables in both. These enable client certificate-based authentication.

set CTSSL_CLIENT_CERTIFICATE=admin_client.pem 
set CTSSL_CLIENT_KEY=admin_client.pem

(optional) Run the following to enable SSL-related diagnostic messages:

Set CTSSL_DEBUG_LOG=ssl.log

The CA certificate MUST be available to the client process as the file ca.crt in the working directory. Copy the CA certificate to the file named ca.crt in the<faircom>\tools\ folder.

Copy <faircom>\tools\certman\Certs\Expires_On_<future date>\ca.crt <faircom>\tools\ca.crt

Verify that TLS connections are working correctly for the ctixmg, ctadmn, and ctixmb tools, and check each tool's connection type.

Verify connections

In the first CMD shell, from the FairCom MQ <faircom>\tools\ folder, run each of the following command lines one at a time to check each tool's connection type.

These commands append ^fssltcp to the server name to request an SSL-enabled connection for an ISAM connection. In these examples, you may need to change FCEDGEMQ to match the ctsrvr.cfg SERVER_NAME value of your MQ server.

Be sure to use quotes on the command line.

It is not necessary to send user/password because the TLS certificate supplies the necessary credentials.

ctixmg ADMIN ADMIN "FCEDGEMQ@localhost^fssltcp"
ctadmn ADMIN ADMIN "" "FCEDGEMQ@localhost^fssltcp"
ctixmg none none "FCEDGEMQ@localhost^fssltcp"

If you get an error such as "Could not initialize c-tree Plus" or "Could not logon to server", the configuration is not configured correctly. If you get a menu or are asked a question, the x509 connection is working.

Check the connection type

While one tool is running in the first CMD shell, run ctadmn in the second CMD shell from the FairCom MQ tools folder where ca.crt is present.

ctadmn none none "" "FCEDGEMQ@localhost^fssltcp"

                **** FairCom(R) Server Administration Utility ****

                         1. User Operations
                         2. Group Definitions
                         3. File Security
                         4. Monitor Clients
        Enter your choice (1-10), or 'q' to quit>> 4

Enter 4 to choose Monitor Clients.

Monitor Clients:

                         1. List Attached Clients
                         2. Kill Client

  Enter your choice (1-2), or 'q' to return to previous menu>> 1

Enter 1 to choose List Attached Clients:

Look at the NodeName of each client that is listed to find the ctixmg, ctadmn, or ctixmb tool you are currently running in the first CMD shell.

You might have to press enter a few times because our tool might be last on the list after other internal MQ threads.

The following examples are outputs for the ctixmg and ctadmn tools, with the items of interest bolded.

UserID: ADMIN                      NodeName: ctixmg
Task 20                            Communications: FSSLTCP
    Memory: 235K       Open Files: 3      Logon Time:   0:06
    Tran Time:   --    Rqst Time:   0:06  NoRequest  Rqst# 196 OPNRFIL

UserID: ADMIN                      NodeName: ctadmn
Task 34                            Communications: FSSLTCP
    Memory: 288K       Open Files: 9      Logon Time:   7:38
    Tran Time:   --    Rqst Time:   7:38  NoRequest  Rqst# 196 OPNRFIL

Communications: FSSLTCP, as in these examples, indicates the TCP/IP connection is using TLS.

Note: The following protocols are some of the more common TCP/IP connections you might observe:

F_TCPIP	indicates an unencrypted ISAM TCP/IP connection.
FSSLTCP	indicates an SSL/TLS-enabled ISAM TCP/IP connection.
SQL_TCPIP  indicates an unencrypted SQL TCP/IP connection.
SQL_SSLTCP  indicates an SSL/TLS-enabled ISAM TCP/IP connection.

Enter q to return to the ctadmn main menu.

In the first command shell, kill the tool that is running by pressing ctrl-c,. Launch the next tool and check its connection type until all three tools are verified.

In the second CMD shell running the ctadmn tool, add the JonDoe user to your Server/Broker's database.

User Operations:
                  1. Add New User
                  2. Remove Existing User
                  3. List Users
Enter your choice (1-9), or 'q' to return to previous menu>> 1

Enter 1, Add New User.

Enter User Id >> Jon Doe
Enter User Description >> Jon Doe

Answer the remaining prompts as you prefer. After the last prompt, press q to return to the main menu. Press q again to exit ctadmn.

Set two environment variables to establish the user JonDoe with x509 authentication.

set CTSSL_CLIENT_CERTIFICATE=john_doe_client.pem 
set CTSSL_CLIENT_KEY=john_doe_client.pem

Run the ctixmg tool again, without a user or password, to verify that the JonDoe user is correctly configured with a TLS connection. This should launch without a "Could not initialize c-tree Plus" error. You may need to change FCEDGEMQ to match your SERVER_NAME value.

ctixmg none none "FCEDGEMQ@localhost^fssltcp"

With the FairCom DB installation, edit the <faircom>\config\dbnotifyconnections.json file to configure a secure connection between your FairCom DB server and the FairCom MQ Broker:

1. Set the faircomServerName to  FCEDGEMQ. This is the SERVER_NAME of the MQ broker that is set in the /config/ctsrvr.cfg file in the FairCom MQ installation.

2. Enable tls and x509_authentication.

3. At tls, set the certificateFilename with the name of the client-side certificate file that you previously created (server.pem in this tutorial).

4. At x509_authentication, set certificateFilename with the FairCom ADMIN user certificate key you previously created (admin_client.pem in this tutorial).

   {
     "logLevel":"development",
     "directConnectionsToFairComMq":[
       {
         "brokerConnectionName":"brokerCtree",
         "faircomServerName":"FCEDGEMQ",
         "brokerHostname":"localhost",
         "reconnectFrequencySeconds":15,
         "tls":{
           "certificateFilename":"server.pem",
           "enabled":true,
           "x509_authentication":{
             "certificateFilename":"admin_client.pem",
             "enabled":true
           }
         }
       }
     ]
   }

Edit the <faircom>\config\dbnotify\ctreeSQLcustmast.json file to configure the DB notification criteria.

When the DB Server enables the FairCom DB Notify service at startup, it looks in the config\dbnotify folder for any .json files. Each .json file is expected to contain the necessary information related to one database file to be monitored. A separate .json file is needed for each database file you want to monitor.

{
  "databaseName":"ctreeSQL",
  "tableName":"custmast",
  "ownerName":"admin",
  "publishMqttMessages":[
    {
      "topic": "topic1",
      "persistenceDatabaseName":"faircom",
      "persistenceOwnerName":"admin",
      "persistenceTableName":"mqtt_msg_topic1",
      "brokerConnectionName":"brokerCtree",
      "triggers":["delete"],
      "QoS":0,
      "recordFilter":"!stricmp(cm_custstat,\"CA\")",
      "includedFields":[
        "cm_custnumb",
        "cm_custzipc",
        "cm_custcity"
      ],
      "includePrimaryKey":"never"
    },
    {
      "topic": "topic1",
      "persistenceDatabaseName":"faircom",
      "persistenceOwnerName":"admin",
      "persistenceTableName":"mqtt_msg_topic1",
      "brokerConnectionName":"brokerCtree",
      "triggers":["insert","update"],
      "QoS":0,
      "recordFilter":"atoi(cm_custnumb) >= 1000",
      "includePrimaryKey":"never",
      "tagChanges":"forEachField"
    }
  ]
}

The "topic": "topic1" on "brokerConnectionName":"brokerCtree" that you set up when you configured your connection is used for published messages.

Copy certificates to the <faircom>\server\ folder.

FairCom DB Notify acts as a client of the FairCom MQ Broker, so we need to copy the client-side certificates to the FairCom DB server folder.

1. Copy the server.pem file (SSL client-side certificate) into your FairCom DB server folder.

   > copy .\<YourWorkFolder>\server.pem .\<faircom>\server\server.pem

2. Copy the admin user certificate file into the FairCom DB server folder (x509 authentication):

   > copy .\<YourWorkFolder>\admin_client.pem  .\<faircom>\server\admin_client.pem
   

Switch to the machine that FairCom MQ is running on and use MQ Explorer to check if the topics are already there:

If you have already run FairCom DB Notify without x509 in this environment, "topic1" and "FairComServer/NotificationPlugin/Errors" topics are already created and will appear in this list.

1. In API Explorer, in the Select API drop-down list choose MQ API.

2. In the JSON Actions drop-down list select listTopics then click Send request () to execute the listTopics action.

   The API Response will list all topics that are subscribed to in this broker.

   

   In this example, "topic1" is not on the server.

Create topics "topic1" and "FairComServer/NotificationPlugin/Errors" if they do not exist in the FairCom MQ broker.

1. In API Explorer, click Upload Set of Json files ()

   

2. Select and upload createErrorTopic.json and createTopic1.json found in the <faircom>\config\x509.auth.demo\ folder.

   

3. In Uploaded Actions select and execute the API request for each action.

   

4. Execute listTopics again to show those topics.

   

   Note: FairCom DB will support the automatic creation of topics with x509 in the future.

On the machine that FairCom DB is installed on, restart the FairCom DB Server.