How to Use a Remote Database Server with the Harmony Access Point v1.4.0?

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 1

1. Install the Harmony Access Point application by following the installation guide until section 2.5. Do not complete the tasks defined after section 2.5 - they will be completed later.

2. Once the Access Point installation process has successfully completed, run the following SQL command:

   1. mysql -e "SELECT USER_NAME, USER_PASSWORD FROM harmony_ap.TB_USER;"

   2. The response should look like this:

      1. +-----------+--------------------------------------------------------------+ | USER_NAME | USER_PASSWORD | +-----------+--------------------------------------------------------------+ | harmony | $2a$10$Pr4IoRjmZC4ctIRFILFCo.6Fr03m/DOb6gZItV7M/eDSasQQuaaJ6 |

3. Copy the USER_NAME and USER_PASSWORD values to a file. They will be needed during the next steps.

Host 2

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.

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

2. Run the script with sudo.

   1. sudo ./install_db.sh

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

   1. . . 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.

   1. sudo systemctl enable mysql

5. By default, the MySQL server allows connections from localhost only. Update the bind-address parameter in the /etc/mysql/mysql.conf.d/mysqld.cnf configuration file to allow connections from remote hosts. Use the IP address of host 1 as value if you want to allow connections from host 1 only. Instead, if you want to allow connection from any remote server, use 0.0.0.0 as value.

6. Restart the MySQL server to apply the changes.

   1. sudo systemctl restart mysql

Host 1

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

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

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

2. Open the Harmony Access Point configuration file /etc/harmony-ap/domibus.properties for editing and update the following properties.

   1. Before updating the properties, copy the values of the domibus.datasource.xa.property.password and domibus.datasource.password properties to a separate file. Store the file for future use.

   2. domibus.database.serverName=<DATABASE_HOST> domibus.database.port=<DATABASE_PORT> domibus.datasource.xa.property.password=<DATABASE_PASSWORD> domibus.datasource.password=<DATABASE_PASSWORD>

3. Add the allowPublicKeyRetrieval=true URL parameter to the domibus.datasource.url and domibus.datasource.xa.property.url property values. After the change, the values should look like this:

   1. domibus.datasource.url=jdbc:mysql://${domibus.database.serverName}:${domibus.database.port}/${domibus.database.schema}?allowPublicKeyRetrieval=true&useSSL=false domibus.datasource.xa.property.url=jdbc:mysql://${domibus.database.serverName}:${domibus.database.port}/${domibus.database.schema}?pinGlobalTxToPhysicalConnection=true&useSSL=false&allowPublicKeyRetrieval=true

4. Create a file create_db_schema.sh with the following content. The script generates the Harmony Access Point database schema and database user on Host 2.

   1. #!/bin/bash set -e if [[ $# -ne 2 ]]; then echo "Illegal number of parameters" >&2 exit 2 fi AUSER=$1 HASHEDPASSWORD=$2 DBUSER=harmony_ap HOST=$(grep "domibus.database.serverName" /etc/harmony-ap/domibus.properties | cut -d'=' -f2 | head -n 1) PORT=$(grep "domibus.database.port" /etc/harmony-ap/domibus.properties | cut -d'=' -f2 | head -n 1) if [[ -z $HOST || -z $PORT ]]; then echo 'Database connection details not found' exit 1 fi DBUSER=$(grep "domibus.datasource.xa.property.user" /etc/harmony-ap/domibus.properties | cut -d'=' -f2 | head -n 1) DBPASSWORD=$(grep "domibus.datasource.xa.property.password" /etc/harmony-ap/domibus.properties | cut -d'=' -f2 | head -n 1) if [[ -z $DBUSER || -z $DBPASSWORD ]]; then echo 'Existing database credentials not found' exit 1 fi URL=jdbc:mysql://$HOST:$PORT/harmony_ap SCHEMA_EXISTS=$(mysql -u $DBUSER -p$DBPASSWORD -h $HOST -P $PORT -s -N -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='harmony_ap'"); if [ -n "$SCHEMA_EXISTS" ]; then echo "Database harmony_ap already exists. Create schema" >&2 /opt/harmony-ap/liquibase/liquibase.sh \ --classpath=/opt/harmony-ap/lib/mysql-connector-java-8.0.26.jar \ --driver=com.mysql.cj.jdbc.Driver \ --url=$URL \ --username=$DBUSER \ --password=$DBPASSWORD \ --changeLogFile=/opt/harmony-ap/db/db.changelog.xml \ --searchPath=/ \ update mysql -u $DBUSER -p$DBPASSWORD -h $HOST -P $PORT -e "INSERT INTO harmony_ap.TB_USER (ID_PK, USER_NAME, USER_PASSWORD, USER_ENABLED, USER_DELETED, DEFAULT_PASSWORD) \ VALUES ('1', '$AUSER', '$HASHEDPASSWORD', 1, 0, 0); INSERT INTO harmony_ap.TB_USER_ROLES (USER_ID, ROLE_ID) VALUES ('1', '1');" echo "Database schema harmony_ap succesfully created" >&2 else echo "Database harmony_ap doesn't exist yet. Skipping schema creation" >&2 fi

5. Run the script with sudo giving the USER_NAME and USER_PASSWORD as parameters. Note that the USER_PASSWORD parameter must be inside single quotes.

   1. sudo ./create_db_schema.sh <USER_NAME> '<USER_PASSWORD>' # For example sudo ./create_db_schema.sh harmony '$2a$10$Pr4IoRjmZC4ctIRFILFCo.6Fr03m/DOb6gZItV7M/eDSasQQuaaJ6'

6. Stop the local MySQL database server and disable it. Since a remote database server is used, the local database server can be disabled. Removing the local database server is not possible, because the harmony-ap package requires it to be installed.

   1. sudo systemctl stop mysql sudo systemctl disable mysql

7. Complete the tasks defined in the Harmony Access Point installation guide sections 2.6 and 2.7.

8. After starting the Access Point application, check the application log (/var/log/harmony-ap/catalina.out) for errors.

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:

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

   2. 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.

   1. 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.

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

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

   3. 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.

   1. 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.

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

   2. 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.xa.property.url and domibus.datasource.url property values. After the update, they should look like this:

   1. domibus.datasource.xa.property.url=jdbc:mysql://${domibus.database.serverName}:${domibus.database.port}/${domibus.database.schema}?pinGlobalTxToPhysicalConnection=true&useSSL=true&allowPublicKeyRetrieval=true&verifyServerCertificate=true&requireSSL=true&clientCertificateKeyStoreUrl=file:///etc/harmony-ap/mysql-keystore.jks&clientCertificateKeyStoreType=jks&clientCertificateKeyStorePassword=<mysql_keystore_password> domibus.datasource.url=jdbc:mysql://${domibus.database.serverName}:${domibus.database.port}/${domibus.database.schema}?allowPublicKeyRetrieval=true&useSSL=true&verifyServerCertificate=true&requireSSL=true&clientCertificateKeyStoreUrl=file:///etc/harmony-ap/mysql-keystore.jks&clientCertificateKeyStoreType=jks&clientCertificateKeyStorePassword=<mysql_keystore_password>

   2. 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