{"type":"doc","content":[{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"This article includes updates for Certificate Manager 8.6.1.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"This article describes how to install and set up the MySQL database, used in ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]},{"text":"Smart ID Certificate Manager","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}},{"type":"link","attrs":{"href":"/wiki/spaces/PUB/pages/65346056"}}]},{"text":" (CM).","type":"text","marks":[{"type":"textColor","attrs":{"color":"#172b4d"}}]}]},{"type":"paragraph","content":[{"text":"For detailed information about how to install MySQL, see the documentation available at ","type":"text"},{"text":"https://www.mysql.com/","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.mysql.com/"}}]},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Prerequisites","type":"text"}]},{"type":"paragraph","content":[{"text":"When MySQL is used for the CMDB database, tables and users must be created before the CM server is installed.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Install the MySQL database according to the instructions for the downloaded MySQL installation package.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Extract the MySQL database scripts ","type":"text"},{"text":"create_db_and_role.sql","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" and ","type":"text"},{"text":"create_cmdb.sql","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":", located at ","type":"text"},{"text":"program_files/install/MySQL","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":", from the server installation zip file, ","type":"text"},{"text":"cm_server_.zip","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":".","type":"text"}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Step-by-step instruction","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Edit settings on the MySQL server","type":"text"}]},{"type":"paragraph","content":[{"text":"The database user created by the ","type":"text"},{"text":"create_db_and_role.sql","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" script will use the default authentication plugin. In MySQL 8.0.4 the default authentication plugin has changed from ","type":"text"},{"text":"mysql_native_password","type":"text","marks":[{"type":"code"}]},{"text":" to ","type":"text"},{"text":"caching_sha2_password","type":"text","marks":[{"type":"code"}]},{"text":".","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Change the password for the lcmreq user, that will be created by the script below. The default password is REQreq01.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To enable interactive mode, login as superuser:","type":"text"}]},{"type":"codeBlock","content":[{"text":"mysql --user=root --password","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Run the script, that creates the CMDB database, the ","type":"text"},{"text":"lcmreq","type":"text","marks":[{"type":"code"}]},{"text":" user and the ","type":"text"},{"text":"cmdb_rw_role","type":"text","marks":[{"type":"code"}]},{"text":" role, from the current interactive mode:","type":"text"}]},{"type":"codeBlock","content":[{"text":"source create_db_and_role.sql >create_cmdb.log","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Check the log file.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Run the script for creating the CMDB tables:","type":"text"}]},{"type":"codeBlock","content":[{"text":"source create_cmdb.sql >create_cmdb.log","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Check the log file.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Edit settings on the CM server","type":"text"}]},{"type":"paragraph","content":[{"text":"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: ","type":"text"},{"text":"http://mariadb.com/kb/en/library/about-mariadb-connector-j/.","type":"text","marks":[{"type":"link","attrs":{"href":"http://mariadb.com/kb/en/library/about-mariadb-connector-j/."}}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When installing the CM server, include the JDBC component to create the MySQL database connection parameters, which are stored in the ","type":"text"},{"text":"cm.conf","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" ","type":"text","marks":[{"type":"strong"}]},{"text":"configuration file.","type":"text"}]}]}]},{"type":"panel","attrs":{"panelType":"note"},"content":[{"type":"paragraph","content":[{"text":"As of MySQL 8.0.4 the ","type":"text"},{"text":"caching_sha2_password","type":"text","marks":[{"type":"code"}]},{"text":" authentication plugin is the default for user authentication. CM is by default configured (in cm.conf) with the ","type":"text"},{"text":"allowPublicKeyRetrieval","type":"text","marks":[{"type":"code"}]},{"text":" 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: ","type":"text"}]},{"type":"paragraph","content":[{"text":"https://dev.mysql.com/doc/refman/8.0/en/upgrading-fromprevious-series.html#upgrade-caching-sha2-password","type":"text","marks":[{"type":"link","attrs":{"href":"https://dev.mysql.com/doc/refman/8.0/en/upgrading-fromprevious-series.html#upgrade-caching-sha2-password"}}]}]}]},{"type":"panel","attrs":{"panelType":"note"},"content":[{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"permitMysqlScheme","type":"text","marks":[{"type":"code"}]},{"text":" to be present in the MySQL connection string.","type":"text"}]},{"type":"paragraph","content":[{"text":"Example:","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"Database.name = jdbc:mysql://localhost:3306/CMDB?permitMysqlScheme&\nallowPublicKeyRetrieval=true","type":"text"}]},{"type":"paragraph","content":[{"text":"For additional information, click this link and navigate to the part 'jdbc:mysql scheme compatibility': ","type":"text"},{"type":"hardBreak"},{"type":"inlineCard","attrs":{"url":"https://mariadb.com/kb/en/about-mariadb-connector-j/"}},{"text":" ","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Secure the connection","type":"text"}]},{"type":"paragraph","content":[{"text":"TLS is enabled by default in MySQL server and it is advised to use TLS for the JDBC connection to the database server.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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: ","type":"text"},{"text":"http://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html","type":"text","marks":[{"type":"link","attrs":{"href":"http://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html"}}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"After completing the guide, add additional JDBC parameters to the ","type":"text"},{"text":"cm.conf","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" ","type":"text","marks":[{"type":"strong"}]},{"text":"file to enable secure connection between CM and the database.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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: ","type":"text"},{"text":"https://mariadb.com/kb/en/using-tls-ssl-with-mariadb-java-connector/","type":"text","marks":[{"type":"link","attrs":{"href":"#"}}]},{"text":". To force the JDBC driver to use TLS, set the JDBC property useSSL to true, see this example:","type":"text"}]},{"type":"codeBlock","content":[{"text":"Database.name = jdbc:://:/?&useSSL=true","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Configure a trust store by specifying a path to a trust store file that contains both root and intermediate certificates. Add the following parameters:","type":"text"}]},{"type":"codeBlock","content":[{"text":"Database.security.1 = trustStore = \nDatabase.security.2 = trustStorePassword = \n;If a PEM certificate is used as truststore\nDatabase.security.1 = serverSslCert = ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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:","type":"text"}]},{"type":"codeBlock","content":[{"text":"Database.security.3 = keyStore = \nDatabase.security.4 = keyStorePassword = ","type":"text"}]}]}]}]}]},{"type":"paragraph","content":[{"text":"If client authentication is enabled, the database password can be removed (or commented out) from ","type":"text"},{"text":"cm.conf","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":". However, the username still needs to be present.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Additional information","type":"text"}]},{"type":"expand","attrs":{"title":"Useful links"},"content":[{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"https://www.mysql.com/"}},{"text":" ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"About the MariaDB JDBC driver","type":"text","marks":[{"type":"link","attrs":{"href":"http://mariadb.com/kb/en/library/about-mariadb-connector-j/"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"https://nexusdoc.atlassian.net/wiki/spaces/PUB/pages/65355548"}},{"text":" ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"https://nexusdoc.atlassian.net/wiki/spaces/PUB/pages/65354780"}},{"text":" ","type":"text"}]}]}]}]}],"version":1}

Browser not supported