FairCom DB Notify

Copy certificates

1. Copy the 'ctree_ssl.pem' file (server-side certificate) you generated above into your Broker's server folder:

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

   Note: The 'ctree_ssl.key' file does not need to be specified because it is already embedded in 'ctree_ssl.pem'.

2. Copy the 'ctsrvr.pem' file (client-side certificate) into your client Apps folder. In this tutorial, we will use our ./tools folder in our Broker's package.

   > copy .\<YourWorkFolder>\ctsrvr.pem .\<faircom>\tools\ctsrvr.pem

3. Copy the user certificate and key files you created above. Remember, we made an 'admin' and a 'JonDoe' user. Copy these files into the ./tools folder.

   > copy .\<YourWorkFolder>\admin.pem  .\<faircom>\tools\admin.pem
   > copy .\<YourWorkFolder>\admin.key  .\<faircom>\tools\admin.key
   > copy .\<YourWorkFolder>\JonDoe.pem .\<faircom>\tools\JonDoe.pem
   > copy .\<YourWorkFolder>\JonDoe.key .\<faircom>\tools\JonDoe.key

Disable shared memory if testing the client on the same computer as the server.

Deactivate the Shared Memory Protocol even if you are connecting with TCP/IP, internally. If the Shared Memory protocol is activated, the Shared Memory is automatically switched when possible. To test your TCP/IP TLS/SSL it is best to deactivate Shared Memory by commenting out its keyword in the 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 by editing FairCom MQ Broker's ctsrvr.cfg file in the <faircom>\config folder.

Uncomment the SSL and x509 support.

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

;This is the file name in your server's directory
SERVER_CERTIFICATE_FILE ctree_ssl.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-SHA38:DHE-RSA-AES256-SHA256:AES256-GCM-SHA384
}

Update "stopserver" in the <faircom>\server folder to stop the server using SSL.

#!/bin/sh

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

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

First, restart Faircom MQ. 

The message "Initialized SSL support." appears in CTSTATUS.FCS.

Check that in the server's configuration file (ctsrvr.cfg), only SSL connections are allowed.

SSL_CONNECTIONS_ONLY YES

Set environment variables to set up the SSL user as 'admin'.

set CTSSL_CLIENT_CERTIFICATE=admin.pem 
set CTSSL_CLIENT_KEY=admin.key

Append ^fssltcp to the server name to request an SSL-enabled connection for an ISAM connection.

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

Be sure to use quotes on the command line.

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

Run ctadmn to check that the communication is using SSL.

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

Select 4 to "Monitor Clients".

Monitor Clients:

                         1. List Attached Clients
                         2. Kill Client

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

Select 1 to "List Attached Clients":

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

UserID: ADMIN                             NodeName:                           
Task 19                                   Communications: FSSLTCP          
<<<<< SEE SSL   
         Memory: 163K       Open Files: 3      Logon Time:   2:51
         Tran Time:   --    Rqst Time:   2:49  NoRequest  Rqst# 196 
OPNRFIL

If you see Communications: FSSLTCP  as in this example, it means that the TCP/IP connection is using SSL.

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-enabled ISAM TCP/IP connection.
SQL_TCPIP  indicates an unencrypted SQL TCP/IP connection.
SQL_SSLTCP  indicates an SSL-enabled ISAM TCP/IP connection.

Enter “q” to return to the ctadmn main menu.

Add the 'Jon Doe' user into 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

Select option 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 specify the user (JonDoe) with x509 authentication.

set CTSSL_CLIENT_CERTIFICATE=JonDoe.pem 
set CTSSL_CLIENT_KEY=JonDoe.key

Run your apps, without a user or password.

ctixmg none none "FCEDGEMQ@localhost^fssltcp"

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.

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.

4. At "x509_authentication", set "certificateFilename" and "privateKeyFilename" with the FairCom ADMIN user certificate key and private key you previously created.

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

Encrypt the dbnotifyconnections.json file for secure connection and communication.

1. Rename dbnotifyconnections.json to dbnotifyconnections.cfg

2. Execute "ctcmdset dbnotifyconnections.cfg" to get an encrypted dbnotifyconnections.set file.

3. Rename dbnotifyconnections.set to dbnotifyconnections.json

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

When the DB Server enables the 'dbnotify' 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":[
    {
      "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"
    },
    {
      "persistenceDatabaseName":"faircom",
      "persistenceOwnerName":"admin",
      "persistenceTableName":"mqtt_msg_topic1",
      "brokerConnectionName":"brokerCtree",
      "triggers":["insert","update"],
      "QoS":0,
      "recordFilter":"atoi(cm_custnumb) >= 1000",
      "includePrimaryKey":"never",
      "tagChanges":"tagEachField"
    }
  ]
}

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 ctsrvr.pem file (SSL client-side certificate) into your FairCom DB server folder.

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

2. Copy 'admin' user certificate and key files into the FairCom DB server folder (x509 authentication):

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

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.

Restart the FairCom DB Server.