Set up MySQL in Certificate Manager

This article includes updates for Certificate Manager 8.6.1.

This article describes how to install and set up the MySQL database, used in Smart ID Certificate Manager (CM).

For detailed information about how to install MySQL, see the documentation available at https://www.mysql.com/.

Prerequisites

When MySQL is used for the CMDB database, tables and users must be created before the CM server is installed.

Install the MySQL database according to the instructions for the downloaded MySQL installation package.
Extract the MySQL database scripts create_db_and_role.sql and create_cmdb.sql, located at program_files/install/MySQL, from the server installation zip file, cm_server_<version>.zip.

Step-by-step instruction

Edit settings on the MySQL server

The database user created by the create_db_and_role.sql script will use the default authentication plugin. In MySQL 8.0.4 the default authentication plugin has changed from mysql_native_password to caching_sha2_password.

Change the password for the lcmreq user, that will be created by the script below. The default password is REQreq01.
Change the user host settings in the script to define which host address the user will access the database from. The default value is '%' which indicates that the user may use the database from any host.
To enable interactive mode, login as superuser:
CODE
```
mysql --user=root --password
```
Run the script, that creates the CMDB database, the lcmreq user and the cmdb_rw_role role, from the current interactive mode:
CODE
```
source create_db_and_role.sql >create_cmdb.log
```
Check the log file.
Run the script for creating the CMDB tables:
CODE
```
source create_cmdb.sql >create_cmdb.log
```
Check the log file.

Edit settings on the CM server

The MySQL JDBC driver uses a license that does not allow CM to distribute it. Therefore, CM instead includes and uses the MariaDB JDBC driver, which is fully compatible with MySQL database servers. See this link: http://mariadb.com/kb/en/library/about-mariadb-connector-j/.

When installing the CM server, include the JDBC component to create the MySQL database connection parameters, which are stored in the cm.conf configuration file.

As of MySQL 8.0.4 the caching_sha2_password authentication plugin is the default for user authentication. CM is by default configured (in cm.conf) with the allowPublicKeyRetrieval parameter enabled for MariaDB JDBC driver to facilitate retrieval of MySQL server public key for user password encryption. For more information, see the following link:

https://dev.mysql.com/doc/refman/8.0/en/upgrading-fromprevious-series.html#upgrade-caching-sha2-password

As of Certificate Manager version 8.6.1, the CF server is using MariaDB JDBC driver version 3.0.7. This version of MariaDB requires the parameter permitMysqlScheme to be present in the MySQL connection string.

Example:

SQL

Database.name = jdbc:mysql://localhost:3306/CMDB?permitMysqlScheme&
allowPublicKeyRetrieval=true

For additional information, click this link and navigate to the part 'jdbc:mysql scheme compatibility':
https://mariadb.com/kb/en/about-mariadb-connector-j/

Secure the connection

TLS is enabled by default in MySQL server and it is advised to use TLS for the JDBC connection to the database server.

Configure CM to connect to MySQL using certificate authentication for both server and client certificates. A complete guide on how to enable TLS on the MySQL Server can be found here: http://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html.
After completing the guide, add additional JDBC parameters to the cm.conf file to enable secure connection between CM and the database.
1. Modify the JDBC connection url to force the driver to use TLS. More details regarding TLS with the MariaDB JDBC driver can be found here: https://mariadb.com/kb/en/using-tls-ssl-with-mariadb-java-connector/. To force the JDBC driver to use TLS, set the JDBC property useSSL to true, see this example:
  CODE
```
Database.name = jdbc:<mysql|mariadb>://<host>:<port>/<database>?<other_properties>&useSSL=true
```
2. Configure a trust store by specifying a path to a trust store file that contains both root and intermediate certificates. Add the following parameters:
  CODE
```
Database.security.1 = trustStore = <path_to_jks_or_p12_keystore_file>
Database.security.2 = trustStorePassword = <keystore_password>
;If a PEM certificate is used as truststore
Database.security.1 = serverSslCert = <path_to_pem_file>
```
3. If client authentication is enabled or required on the MySQL server, specify a path to a key store file. To specify a client TLS certificate and key, add the following parameters:
  CODE
```
Database.security.3 = keyStore = <path_to_jks_or_p12_keystore_file>
Database.security.4 = keyStorePassword = <keystore_password>
```

If client authentication is enabled, the database password can be removed (or commented out) from cm.conf. However, the username still needs to be present.

Additional information

Useful links