Database Connection Encryption deprecated
This guide will help you to setup SSL for your database and configure it for your OX system. Due to the fact that different operating systems and databases are supported by OX the guide might not cover everything needed for a successful setup. Therefore, please use the references below if errors occur. Any suggestion on how to improve the guide are welcome.
- MariaDB and SSL
- Ubuntu and MySQL
- MySQL with user privileges (german)
- Official MySQL guide for SSL setup (JDBC)
Configure your Database
First things first. Before we can configure the Middleware, which is acting as the client, we need to setup the database (or rather the server) to use SSL. Therefore we need certificates.
The database you use makes a huge difference on how you can generate SSL certificates. If you e.g. use the enterprise version of MySQL you can use the parameter 'auto_generate_certs' (see MySQL system variables for details) and don't need to do anything. For all other cases see the next chapters.
MySQL (community edition)
The community edition (since 5.7) ships a tool called 'mysql_ssl_rsa_setup' (see MySQL SSL setup for details). This tool will generate all needed certificates in just one simple step. E.g.
mysql_ssl_rsa_setup --datadir=/etc/mysql/ssl --suffix OX
(Precondition is an existing dir "ssl" in "/etc/mysql")
If you don't have the tool, follow the steps below.
Before we generate the certificates, create a subdirectory in your SQL folder. This will help you to keep track of your SQL installation. After this execute the following commands to generate (self signed)
- A CA certificate
- A server certificate
- A client certificate to later copy to the Middleware
Note: This commands use the 'rsa' command to build a private key that begins with
-----BEGIN RSA PRIVATE KEY-----. This is needed for yaSSL. The library expects the key to begin in this manner and fails if it does not. Therefore the guide uses this approach to generate SSL keys.
mkdir /etc/mysql/ssl cd /etc/mysql/ssl # Generate CA cert and key openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 1095 -key ca-key.pem -out ca-cert.pem # Generate server cert and key openssl req -newkey rsa:2048 -days 1095 -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem # Generate client cert and key openssl req -newkey rsa:2048 -days 1095 -nodes -keyout client-key.pem -out client-req.pem openssl rsa -in client-key.pem -out client-key.pem openssl x509 -req -in client-req.pem -days 1095 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem # Verify the certificates against the self signed CA openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem chown mysql:mysql *
The last command might not be necessary for all databases, but you need for some and is a point of failure. This command too should be run at the client after copying certificates to the client.
Activate SSL in the configuration
This step is the one with the most diversity. E.g. Debian ships MariaDB binaries that where compiled to use yaSSL (or rather with the new name wolfSSL). Therefore the configuration to activate SSL is
ssl on. If the binaries were compiled to use OpenSSL the configuration param would look like this
ssl-cipher=TLSv1.2. On MySQl it might be
ssl=1 or just
ssl. Therefore the configuration showed below might not be working for your system:
(From /etc/mysql/my.cnf) [mysqld] ... ssl ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem
MariaDB with yaSSL
Note: yaSSL does only support TLS in version TLSv1 and TLSv1.1
(From /etc/mysql/mariadb.conf.d/50-server.cnf ) # # * Security Features # # For generating SSL certificates you can use for example the GUI tool "tinyca". # ssl-ca=/etc/mysql/ssl/ca-cert.pem ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem # # Accept only connections using the latest and most secure TLS protocol version. # ..when MariaDB is compiled with OpenSSL: # ssl-cipher=TLSv1.2 # ..when MariaDB is compiled with YaSSL (default in Debian): ssl=on
Afterwards restart your database. E.g.
systemctl restart mysql
Configure your OX
Copy the certificates
Before we can configure the OX Middleware we need to copy some certificates. For the rest of the guide we assume the certificates were copied to
For a two way authentication we need
Note: You can generate the certificates on the client, too, but keep in mind to use the same CA certificate. If you don't, your connections will fail with a generic
Test with local client
Before you configure the Middleware to use SSL you should test if the configuration. Therefore run e.g.:
mysql -uopenexchange -p -h10.50.0.170 --ssl-mode=VERIFY_CA --ssl-ca=/opt/open-xchange/etc/ssl/database/ca-cert.pem --ssl-cert=/opt/open-xchange/etc/ssl/database/client-cert.pem \ --ssl-key=/opt/open-xchange/etc/ssl/database/client-key.pem
It is possible to use a connection without client certificates, too:
$ mysql --protocol=tcp -h localhost -u root -psecret --ssl-ca=/etc/mysql/ssl/ca-cert.pem
Set the certificates
Create key stores
The JDBC connector uses Java KeyStore to manage the different certificates. Therefore we need to create those based on the CA and client certificates. Following commands will create
- A truststore file for the CA certifacte
- A keystore file for the client certificate
# Change to dir beforehand (cd /opt/open-xchange/etc/ssl/database) keytool -importcert -alias MySQLCACert -file ca-cert.pem -keystore truststore -storepass changeit openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:changeit -out client-keystore.p12 keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass changeit -destkeystore keystore -deststoretype JKS -deststorepass changeit # Verify client certificate in key store keytool -list -v -keystore keystore
Set connector properties
To activate SSL you have to set a bunch of properties in the configuration file 'dbconnector.yaml'. For a two way authentication, the file should look like this
com.mysql.jdbc: # The driver properties should be kept at their defaults useUnicode: true characterEncoding: UTF-8 autoReconnect: false useServerPrepStmts: false useTimezone: true serverTimezone: UTC connectTimeout: 15000 socketTimeout: 15000 # SSL/TLS useSSL: true requireSSL: true verifyServerCertificate: true # Deactivate if you just want a server authentication clientCertificateKeyStoreUrl: file:/opt/open-xchange/etc/ssl/database/keystore clientCertificateKeyStorePassword: changeit clientCertificateKeyStoreType: JKS trustCertificateKeyStoreUrl: file:/opt/open-xchange/etc/ssl/database/truststore trustCertificateKeyStorePassword: changeit trustCertificateKeyStoreType: JKS
In the step above we have created an PKCS#12 key store. This can be used instead of the JKS store, too:
clientCertificateKeyStoreUrl: file:/opt/open-xchange/etc/ssl/database/client-keystore.p12 clientCertificateKeyStorePassword: changeit clientCertificateKeyStoreType: PKCS12
For a full property overview please have a look at the official documentation.
Note: Properties for the
configdb will be overwritten by the properties defined in
Note: Based on the underlying JDK there might be problems with the available TLS versions. Newer JDK versions might only use TLSv1.1 or higher and the configured database might only accept TLSv1. The following table outlines the supported TLS versions:
|MariaDB 10.1||For an overview please visit Changes & Improvements in MariaDB 10.1||TLSv1, TLSv1.1, TLSv1.2||2015||7.10.0|
|MariaDB 10.2||For an overview please visit Changes & Improvements in MariaDB 10.2||TLSv1, TLSv1.1, TLSv1.2||2017||7.10.0|
|MySQL 5.6||For an overview please visit MySQL 5.6 Release Notes||TLSv1||2013||7.10.0|
|MySQL 5.7||For an overview please visit MySQL 5.7 Release Notes||TLSv1, TLSv1.1||2015||7.10.0|
Reload properties and certificates in production
The server is capable to reload the JDBC properties as well as the used certificates. To trigger such a rotation the command line tool "reloadConfiguration" is used.
A reload will clear all current unused instances of a 'Connection' held in the connection pools and update the properties used to spawn new connections with the JDBC client. Therefore all newly created connections after a reload will automatically run with the updated properties. Connections that are still used will get marked deprecated, so that these connections will be replaced by updated once after they finished their work.
Change JDBC properties
JDBC properties are stored within the file 'dbconnector.yaml' within the folder '/opt/open-xchange/etc/'. Just add, change or delete a property within the file, save it and afterwards trigger the reload. That's it.
For SSL related JDBC properties have a look at Command Options for Encrypted Connections
Update configDB properties
ConfigDB related properties are still saved within 'configdb.properties'. Those properties are only applied for connections to the configDB and can actually overwrite the general JDBC properties. With 7.10.1 the default set of properties from the 'configdb.properties' were changed. If you upgraded your system you might need to adjust the properties. Since 7.10.1 only following the properties should be set for configDB
- the driver class
- the URL
- the DB user
- the DB users password
All you need to do after editing and saving, is to trigger a 'reloadConfiguration' too. If you only changed configDB related properties, only connections to the configDB will be replaced and updated.
Key Rotation (certificate updates)
Client certificate rotation
To switch client certificates you can either change the URL to new key stores containing the new certificates or you can add those certificates to the existing key stores.
Recreating the key stores
One way to update the client certificates is to simply create new key stores like explained above in the section 'Create key stores'. Once the new certificates are imported in the new key stores, you can change the URLs for those key stores in the 'dbconnector.yaml', see 'Set connector properties'. After saving the file the command line tool 'reloadconfiguration' needs to be run.
Change existing key store
The other way to rotate client related certificates is to add the new certificates to the existing stores. Due to the fact that the JDBC driver reloads the key stores for each new created connection itself, new connections will be configured with those too. Afterwards, the old certificates can be removed with the command
keytool -delete -alias mysqlcacert -keystore truststore
Effectively, this means that after all connections reached 'maxLifeTime', they will be replaced by connections with updated certificates.
The disadvantage of this method is that you can't be sure that after 'maxLifeTime' passed, all connections will have updated certificates. Keep in mind that there is no explicit logic involved by the Open-Xchange server (yet).
Therefore we recommend that after the old certificates were removed from the key stores a 'reloadconfiguration' is run. The Open-Xchange server will discover the changes on the key stores, and then trigger an explicit update of the connections. Advantages of the explicit reload are
- all new created connections will be configured using the new certificates instantly
- all idle connections will be destroyed and replaced by new connections
- connections that are used at the moment of the reload will be destroyed immediately after the connection isn't used anymore
Server certificate rotation
Replacing certificates on the mysql server is not possible without downtimes. The certificates are linked to the configuration of the databases themselves and thus aren't reloadable. Therefore prepare well before switching the certificates to avoid bigger downtimes. Generate and verify new certificates beforehand.
To switch certificates simply replace the paths to the old certificates with the path to the new certificate within your database configuration. Don't overwrite those certificates!
Note: Keep in mind that before you switch the CA, all clients need to be updated. Add the new CA certificate to the trust store and generate new client certificates for the key store.
Once preparation is done restart the database service. E.g.
systemclt restart mysql
Note: Don't forget to remove all old certificates from the trust and the key store of the client.