Configuring Adeptia Suite with Secured MySQL on SSL

Issue:

If MySQL DB is configured for SSL communication and used as Backend/ Log db in AIS, then you may encounter SSL related error in the Kernel Application logs.

“WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.”

This is just a warning and wouldn't have any functional impact but can cause a decreased performance due to extensive logging.

Resolution:

To avoid such error messages in the log, the user can perform either of the approaches as per the requirement.

  1. Configure secured MySQL connection in Adeptia Suite
  2. Bypass secured database connection

Configuring Secured MySQL Connection in Adeptia Suite

Pre-requisites:

  1. Ensure that your MySQL Database is configured over SSL.
  2. Have a certificate of MySQL Database Server to be used for configuring a secured connection with Adeptia  Suite.
  3. If you are using Amazon RDS, then ensure you have an updated "rds-combined-ca-bundle" certificate downloaded from this link.

For Linux:

Steps to be performed in Adeptia Suite:

  1. Import the public certificate file (or rds-combined-ca-bundle certificate) in Adeptia's truststore (../AdeptiaServer/ServerKernel/etc/truststore/cacerts) by following below steps.
    • Open the Terminal.
    • Go to the directory where JRE is installed (for example, /mnt/AdeptiaConnect/AdeptiaConnect-2.9/ConnectServer/jre/bin).
    • To import the certificate in Adeptia's truststore, run the below command -
      keytool -import -alias <alias_name> -file <Path_of_SSL_Certificate> -keystore <Path of Truststore file> -storepass <password>
      example -
      keytool -import -alias adeptia -file "/home/ec2-user/localhost.cer"  -keystore "/opt/AdeptiaSuite/AdeptiaSuite-6.9/AdeptiaServer/ServerKernel/etc/truststore/cacerts" -storepass changeit


  2. Once the certificate got imported into the truststore then the DB URLs needs to be changed for the properties mentioned below:

    (i) abpm.jdo.url and log4j.appender.jdbc.url in server-configure.properties file.

    jdbc:mysql://<<HostName>>:<<MySQL Port>>/<<DB NAME>>?useSSL=true&amp;trustCertificateKeyStoreUrl=<<complete path where trustore file is present>>&amp;trustCertificateKeyStorePassword=<<Password for trustore file>>

    Complete DB URL will be,

    • For abpm.jdo.url -jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=true&amp;trustCertificateKeyStoreUrl=file:///opt/AdeptiaSuite/AdeptiaSuite-6.9/AdeptiaServer/ServerKernel/etc/truststore/cacerts&amp;trustCertificateKeyStorePassword=changeit
    • For log4j.appender.jdbc.url -jdbc:mysql://demo-envt.com:3306/LogDatabase?useSSL=true&amp;trustCertificateKeyStoreUrl=file:///opt/AdeptiaSuite/AdeptiaSuite-6.9/AdeptiaServer/ServerKernel/etc/truststore/cacerts&amp;trustCertificateKeyStorePassword=changeit

    DB_URLs.png
     
    (ii) org.quartz.dataSource.aBPM.URL in quartz.properties file.

    jdbc:mysql://<<HostName>>:<<MySQL Port>>/<<DB NAME>>?useSSL=true&trustCertificateKeyStoreUrl=<<complete path where trustore file is present>>&trustCertificateKeyStorePassword=<<Password for trustore file>>

    Complete DB URL will be,

    jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=true&trustCertificateKeyStoreUrl=file:///opt/AdeptiaSuite/AdeptiaSuite-6.9/AdeptiaServer/ServerKernel/etc/truststore/cacerts&trustCertificateKeyStorePassword=changeit


    DB_URLs-1.png

  3. Restart Adeptia Services after performing these changes.

For Windows:

Steps to be performed for Configuring SSL Connection in Adeptia Suite:

  1. Import the public certificate file (or rds-combined-ca-bundle certificate) in Adeptia's truststore (..\AdeptiaServer\ServerKernel\etc\truststore\cacerts) by following below steps.
    • Open Command Prompt as Administrator.
    • Go to the directory where JRE is installed (for example, C:/Program Files/Java/jre8/bin).
    • To import the certificate in Adeptia's truststore, run the below command -
      keytool -import -alias <alias_name> -file <Path_of_SSL_Certificate> -keystore <Path of Truststore file> -storepass <password>
      example -
      keytool.exe -import -alias root -file "D:\SUPPORT\server-cert\server-cert.pem" -keystore "D:\Program Files\Testing\AdeptiaConnect-2.9\ConnectServer\AdeptiaServer\ServerKernel\etc\truststore\cacerts" -storepass changeit


  2. Once the certificate got imported into the truststore then the DB URLs needs to be changed for the properties mentioned below:

    (i) abpm.jdo.url and log4j.appender.jdbc.url in server-configure.properties file.

    jdbc:mysql://<<HostName>>:<<MySQL Port>>/<<DB NAME>>?useSSL=true&amp;trustCertificateKeyStoreUrl=<<complete path where trustore file is present>>&amp;trustCertificateKeyStorePassword=<<Password for trustore file>>

    Complete DB URL will be,

    • For abpm.jdo.url - jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=true&amp;trustCertificateKeyStoreUrl=file:///D:\\Program Files\\AdeptiaConnect-2.9\\ConnectServer\\AdeptiaServer\\ServerKernel\\etc\\truststore\\cacerts&amp;trustCertificateKeyStorePassword=changeit

    • For log4j.appender.jdbc.url - jdbc:mysql://demo-envt.com:3306/LogDatabase?useSSL=true&amp;trustCertificateKeyStoreUrl=file:///D:\\Program Files\\AdeptiaConnect-2.9\\ConnectServer\\AdeptiaServer\\ServerKernel\\etc\\truststore\\cacerts&amp;trustCertificateKeyStorePassword=changeit

    DB_URLs.png
     
    (ii) org.quartz.dataSource.aBPM.URL in quartz.properties file.

    jdbc:mysql://<<HostName>>:<<MySQL Port>>/<<DB NAME>>?useSSL=true&trustCertificateKeyStoreUrl=<<complete path where trustore file is present>>&trustCertificateKeyStorePassword=<<Password for trustore file>>

    Complete DB URL will be,

    jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=true&trustCertificateKeyStoreUrl=file:///D:\\Program Files\\AdeptiaConnect-2.9\\ConnectServer\\AdeptiaServer\\ServerKernel\\etc\\truststore\\cacerts&trustCertificateKeyStorePassword=changeit


    DB_URLs-1.png

  3. Restart Adeptia Services after performing these changes.

 Bypassing the Secured Database Connection

In case, you do not want to configure MySQL secured connection in AIS, then you can simply bypass the secured database connection to avoid warning messages.

To bypass the secured connection, do the following changes in the server-configure.properties file and quartz.properties file.

In server-configure.properties file complete DB URL for mentioned properties will be,

  • For abpm.jdo.url -jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=false
  • For log4j.appender.jdbc.url -jdbc:mysql://demo-envt.com:3306/LogDatabase?useSSL=false

 In quartz.properties file complete DB URL for the mentioned property will be,

  • For org.quartz.dataSource.aBPM.URL-jdbc:mysql://demo-envt.com:3306/BackendDatabase?useSSL=false

Furthermore, this document can be referred for more information on configuring the SSL on MySQL.

Have more questions? Submit a request

0 Comments

Article is closed for comments.