Openfire - MySQL connection security errors

Openfire Openfire Support
1

Hello,

When I setup my Openfire for development I use a MySQL local database. So I set the connection string as follow :

<serverURL>jdbc:mysql://localhost:3306/openfire?rewriteBatchedStatements=true&amp;characterEncoding=UTF-8&amp;characterSetResults=UTF-8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL&amp;serverTimezone=Europe/Paris</serverURL>

but then I get the following warnings in the logs :

Wed Apr 17 18:33:32 CEST 2019 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.

So I update my connection string and add :
AllowPublicKeyRetrieval=True&amp;useSSL=false

But then it breaks the connection and I’m unable to start Openfire again. The errors I get then are :

Database setup or configuration error: Please verify your database settings and check the logs/error.log file for detailed error messages.
java.lang.IllegalArgumentException: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:999)
        at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:649)
        at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:237)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
        at java.lang.Class.newInstance(Class.java:442)
        at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92)
        at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
Caused by: java.sql.SQLException: ConnectionManager.getConnection() failed to obtain a connection after 11 retries. The exception from the last attempt is as follows: java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed
        at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager.java:163)
        at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:989)
        ... 9 more

Do you have any idea of how I could resolve this problem ?

2

So for anyone having the same problem, here is what I did to resolve it:
I saw in my “my.ini” that the authentification plugin activated by default was “caching_sha2_password” and following >this< StackOverflow topic I tried this solution :

ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';

Before this command, my user for openfire was configured with :
image

After the command it was :
image

And it worked for me :sunny:

1 Like