How to Use a Remote Database Server with the Harmony Access Point v2.x?

By default, the Harmony Access Point application and the MySQL database server are installed on the same host. However, it’s possible to install the application and the database server on separate hosts too. The steps that are required to complete the setup are explained in this article.

In order to complete the setup, two separate hosts are required: host 1 and host 2. The Harmony Access Point application is installed on the host 1 and the MySQL database server on the host 2.

The MySQL database server major version must be 8.

 Instructions

Please follow the steps below to complete the setup.

Host 2 (Database Host)

1. Create a file install_db.sh with the following content. The script installs MySQL database server and creates the Harmony Access Point database.

   1. By default, the script grants harmony_ap DB user access from all remote hosts. If you want to allow access from host 1 only, update lines 18-20 ('$DBUSER'@'%' => '$DBUSER'@'x.x.x.x') replacing % with the host 1 IP address.

      #!/bin/bash set -e sudo apt update sudo apt install mysql-server --yes echo "MySQL Server installed" >&2 DBUSER=harmony_ap DBSCHEMA=$DBUSER SCHEMA_EXISTS=$(mysql -s -N -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DBSCHEMA'"); if [ -z "$SCHEMA_EXISTS" ]; then echo "Creating database harmony_ap" >&2 DBPASSWORD=$(openssl rand -base64 12) mysql -e \ "create schema $DBSCHEMA; \ alter database $DBSCHEMA charset=utf8mb4 collate=utf8mb4_bin; \ create user '$DBUSER'@'%' identified by '$DBPASSWORD'; \ grant all on $DBSCHEMA.* to '$DBUSER'@'%'; " echo "Database schema is: $DBSCHEMA" >&2 echo "Database user is: $DBUSER" >&2 echo "Database password is: $DBPASSWORD" >&2 else echo "Database $DBSCHEMA already exists. Skipping database creation" >&2 fi

2. Run the script with sudo.

   sudo ./install_db.sh

3. When the script has executed successfully, it prints the database schema, username and password (DATABASE_PASSWORD) to the console. Copy them to a separate file.

   ... Database schema is: harmony_ap Database user is: harmony_ap Database password is: oz0/cZZ77l4e7W2j

4. Enable the mysql service so that it’s automatically started after the host is rebooted.

   sudo systemctl enable mysql

5. By default, the MySQL server accepts connections from localhost only. Update the bind-address parameter in the /etc/mysql/mysql.conf.d/mysqld.cnf configuration to listen on another network interface (or use 0.0.0.0 to listen on all network interfaces).

6. It is also necessary to populate MySQL time zone information tables (see https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html#time-zone-installation), e.g. using the following command as root on the external database host (here assuming a Debian-based distribution like Ubuntu, the location of the time zone data can differ):

   mysql_tzinfo_to_sql /usr/share/zoneinfo/posix | mysql -u root mysql

7. Restart the MySQL server to apply the changes.

   sudo systemctl restart mysql

Host 1

1. Try to establish a connection to the remote database.

   mysql -u harmony_ap -p'<DATABASE_PASSWORD>' -h <DATABASE_HOST> -P 3306 -D <DB_SCHEMA>

   • If the connection fails, please check your firewall configuration. Host 1 must be able to connect to port 3306 of host 2.

2. Install the Harmony Access Point application by following the installation guide. When prompted for database connection information, use the database host name (or IP) and schema and credentials from the above database setup step 3.

Enable Secure Database Connection

By default, the connection between the Harmony Access Point application and the MySQL server is unencrypted. With a couple of additional steps, encryption can be enabled.

Host 2

1. Open the /etc/mysql/mysql.conf.d/mysqld.cnf configuration file for editing and add the following configuration at the end of the file:

   ssl-cert=server-cert.pem ssl-key=server-key.pem ssl-ca=ca.pem require_secure_transport=ON

   • The keys defined in the configuration are located in the /var/lib/mysql/ directory.

     1. server-cert.pem - identifies the server public key certificate.

     2. server-key.pem - identifies the server private key.

     3. ca.pem - identifies the Certificate Authority (CA) certificate.

2. Restart the MySQL server to apply the changes.

   sudo systemctl restart mysql

3. Copy the /var/lib/mysql/ca.pem file to host 1.

Host 1

1. Store the ca.pem file copied from host 2 in a file.

2. Import the ca.pem file in the /etc/harmony-ap/tls-truststore.jks file.

   sudo keytool -importcert -alias mysql_root -file ca.pem -keystore /etc/harmony-ap/tls-truststore.jks -storepass <tls_truststore_password>

   1. The truststore password can be found from the /etc/harmony-ap/tomcat-conf/server.xml file in the truststorePass property.

   2. Note: If the CA certificate is a certificate chain that consists of multiple certificates (e.g., root certificate + intermediate certificate), all the certificates in the chain must be added to the /etc/harmony-ap/tls-truststore.jks file separately. In other words, store the certificates in separate files and run the import command for each file.

3. Create a new key and certificate for the MySQL client or alternatively, use the client-key.pem and client-cert.pem files located in the /var/lib/mysql/ directory on host 2.

4. Create a new pkcs12 container for the client key and certificate.

   openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -out keystore.p12 -name "mysql_client" -passout pass:<pkcs12_password>

5. Create a new JKS keystore using the pkcs12 container.

   • Note: The keystore and the private key must have the same password. Using different passwords is not supported.

     sudo keytool -J-Dkeystore.pkcs12.legacy -importkeystore -alias mysql_client -srckeystore keystore.p12 -srcstoretype pkcs12 -destkeystore /etc/harmony-ap/mysql-keystore.jks -srcstorepass <pkcs12_password> -deststoretype JKS -deststorepass <mysql_keystore_password> sudo chown harmony-ap:harmony-ap /etc/harmony-ap/mysql-keystore.jks sudo chmod 751 /etc/harmony-ap/mysql-keystore.jks

6. Open the Harmony Access Point configuration file /etc/harmony-ap/domibus.properties for editing and update the domibus.datasource.url property values. After the update, they should look like this:

   domibus.datasource.url=jdbc:mysql://${domibus.database.serverName}:${domibus.database.port}/${domibus.database.schema}?sslMode=VERIFY_CA&clientCertificateKeyStoreUrl=file:///etc/harmony-ap/mysql-keystore.jks&clientCertificateKeyStoreType=jks&clientCertificateKeyStorePassword=<mysql_keystore_password>

   • If the password contains special characters, they must be URL encoded.

7. Restart the Harmony Access Point application: sudo systemctl restart harmony-ap.

Host 2 (optional)

The previous configuration steps enabled TLS connection between the MySQL server and MySQL clients. However, if mutual TLS authentication (mTLS) is required, one additional step is needed on host 2.

The MySQL server must be configured to verify the issuer (ISSUER) or the subject (SUBJECT) of client certificates. The allowed value for the harmony_ap user must be updated to the mysql database:

For example, if the client-key.pem and client-cert.pem files located in the /var/lib/mysql/ directory on host 2 are used, the allowed ISSUER value is /CN=MySQL_Server_8.0.30_Auto_Generated_CA_Certificate.

The connection can be manually tested on host 1:

 Related articles