Server is been running fine no issues. One of my admins downloaded the latest Spark client 3.0.1 and he upgraded his client before we push it to the rest of the users. It was operational. OF runs on a vm on a vmware host we had to do maintenance and had to shut down all vms. Competed maintenance all vm’s back on line. My spark client reconnected and said something about the cert could not be self signed or something to that effect. Yes, I should have been more eagle eyed before I accepted it. However it connected and all of the users are listed and its working.
Now when my other admin or myself try to login into the web console it keeps telling us that the login failed and to try again
I fairly certain that possibly the cert is not the correct one and it will not let us login.
Any direction on how to correct this would be appreciated.
Unless you have a very specific setup that uses certificate-based-authentication, the certificate being self-signed is unlikely to be the root cause of your problem.
If before the reboot, you did not have this warning about self-signed certificates, then it might be a(nother) symptom of the larger issue that you are experiencing.
Without any records or logs, it is hard to determine what is going on. Problems like these could occur if Openfire was not properly restored after the reboot: if it no longer recognizes the administrative users, and it no longer has a certificate that it used to have, maybe those settings either were not saved correctly, or, when the VM came back online, were removed/replaced somehow?
got the logs and I see this error over and over but nothing on the cert issue.
2023.02.01 08:14:18 ERROR [TaskEngine-pool-96]: org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='mholloway@of.jiffytrip.c' at line 1
java.sql.BatchUpdateException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='mholloway@of.jiffytrip.c' at line 1
at jdk.internal.reflect.GeneratedConstructorAccessor61.newInstance(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]
at java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[?:?]
at com.mysql.cj.util.Util.handleNewInstance(Util.java:192) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.util.Util.getInstance(Util.java:167) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.util.Util.getInstance(Util.java:174) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:853) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:435) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:795) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) ~[commons-dbcp2-2.9.0.jar:2.9.0]
at org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:241) ~[commons-dbcp2-2.9.0.jar:2.9.0]
at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.purgeItems(DefaultPubSubPersistenceProvider.java:1897) [xmppserver-4.7.1.jar:4.7.1]
at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider.access$000(DefaultPubSubPersistenceProvider.java:50) [xmppserver-4.7.1.jar:4.7.1]
at org.jivesoftware.openfire.pubsub.DefaultPubSubPersistenceProvider$1.run(DefaultPubSubPersistenceProvider.java:256) [xmppserver-4.7.1.jar:4.7.1]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) [?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID='mholloway@of.jiffytrip.c' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1098) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchSerially(ClientPreparedStatement.java:832) ~[mysql-connector-java-8.0.28.jar:8.0.28]
... 12 more
If you have backups, maybe restore them to see if the problem persists?
As I don’t know what’s causing the problem, and I’m reluctant to suggest symptom management, but I’m not sure what else to suggest than trying to reset the admin password(s).
I posted to soon and got to the wrong logs. below is the snippet from when it was rebooted and as it starts to come up.
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at jdk.internal.reflect.GeneratedConstructorAccessor17.newInstance(Unknown Source) ~[?:?]
at jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:?]
at java.lang.reflect.Constructor.newInstance(Constructor.java:490) ~[?:?]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:89) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.NativeSession.connect(NativeSession.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:949) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:819) ~[mysql-connector-java-8.0.28.jar:8.0.28]
... 23 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
at java.net.PlainSocketImpl.socketConnect(Native Method) ~[?:?]
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:412) ~[?:?]
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:255) ~[?:?]
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:237) ~[?:?]
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) ~[?:?]
at java.net.Socket.connect(Socket.java:609) ~[?:?]
at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:156) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:63) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.NativeSession.connect(NativeSession.java:120) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:949) ~[mysql-connector-java-8.0.28.jar:8.0.28]
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:819) ~[mysql-connector-java-8.0.28.jar:8.0.28]
... 23 more
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Routing Servers Cache
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Routing Components Cache
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Routing Users Cache
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Routing AnonymousUsers Cache
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Routing User Sessions
2023.01.30 22:07:43 ^[[32mINFO ^[[m [main]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Roster
Loads all the good stuff and then this is where the cert goes south. From the logs is seems that the SSL handshake failed.
2023.01.30 22:07:58 ^[[32mINFO ^[[m [socket_c2s-thread-2]: org.jivesoftware.util.cache.CacheFactory - Created cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Locked Out Accounts
2023.01.30 22:07:58 ^[[32mINFO ^[[m [socket_c2s-thread-2]: org.jivesoftware.util.cache.CacheFactory - Created local-only cache [org.jivesoftware.util.cache.DefaultLocalCacheStrategy] for Bookmarks By Value
**2023.01.30 22:07:59 ^[[33mWARN ^[[m [socket_c2s-thread-7]: org.jivesoftware.openfire.nio.ConnectionHandler - Closing connection due to exception in session: (0x00000009: nio socket, server, /192.168.30.185:26115 => /192.168.20.24:5222)**
javax.net.ssl.SSLHandshakeException: SSL handshake failed.
at org.apache.mina.filter.ssl.SslFilter.messageReceived(SslFilter.java:536) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1300(DefaultIoFilterChain.java:49) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceived(DefaultIoFilterChain.java:1128) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFilterAdapter.java:122) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(DefaultIoFilterChain.java:650) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.filterchain.DefaultIoFilterChain.fireMessageReceived(DefaultIoFilterChain.java:643) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.polling.AbstractPollingIoProcessor.read(AbstractPollingIoProcessor.java:539) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.polling.AbstractPollingIoProcessor.access$1200(AbstractPollingIoProcessor.java:68) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.polling.AbstractPollingIoProcessor$Processor.process(AbstractPollingIoProcessor.java:1222) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.polling.AbstractPollingIoProcessor$Processor.process(AbstractPollingIoProcessor.java:1211) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.core.polling.AbstractPollingIoProcessor$Processor.run(AbstractPollingIoProcessor.java:683) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:64) ~[mina-core-2.1.3.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:829) [?:?]
**Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: certificate_unknown**
at sun.security.ssl.Alert.createSSLException(Alert.java:131) ~[?:?]
at sun.security.ssl.Alert.createSSLException(Alert.java:117) ~[?:?]
at sun.security.ssl.TransportContext.fatal(TransportContext.java:340) ~[?:?]
at sun.security.ssl.Alert$AlertConsumer.consume(Alert.java:293) ~[?:?]
at sun.security.ssl.TransportContext.dispatch(TransportContext.java:186) ~[?:?]
at sun.security.ssl.SSLTransport.decode(SSLTransport.java:172) ~[?:?]
at sun.security.ssl.SSLEngineImpl.decode(SSLEngineImpl.java:681) ~[?:?]
at sun.security.ssl.SSLEngineImpl.readRecord(SSLEngineImpl.java:636) ~[?:?]
at sun.security.ssl.SSLEngineImpl.unwrap(SSLEngineImpl.java:454) ~[?:?]
at sun.security.ssl.SSLEngineImpl.unwrap(SSLEngineImpl.java:433) ~[?:?]
at javax.net.ssl.SSLEngine.unwrap(SSLEngine.java:637) ~[?:?]
at org.apache.mina.filter.ssl.SslHandler.unwrap(SslHandler.java:774) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.filter.ssl.SslHandler.unwrapHandshake(SslHandler.java:710) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.filter.ssl.SslHandler.handshake(SslHandler.java:596) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.filter.ssl.SslHandler.messageReceived(SslHandler.java:355) ~[mina-core-2.1.3.jar:?]
at org.apache.mina.filter.ssl.SslFilter.messageReceived(SslFilter.java:517) ~[mina-core-2.1.3.jar:?]
... 15 more
**2023.01.30 22:08:01 ^[[33mWARN ^[[m [socket_c2s-thread-5]: org.jivesoftware.openfire.nio.ConnectionHandler - Closing connection due to exception in session: (0x0000000D: nio socket, server, /192.168.30.223:64388 => /192.168.20.24:5222)**
**javax.net.ssl.SSLHandshakeException: SSL handshake failed.**
Time stamp correlates about the time this vm was turned on.
Unfortunately this was not back up but moving forward it will be.
I believe that the certificate issues are a red herring.
The first error in your last comment suggests that Openfire is having trouble accessing its database. That would prevent you from loading most settings, including those that define who is an administrator.
If the database connection is failing, I would have expected a lot more to go wrong, unless maybe you’re using something like LDAP or AD?
I think you my have hit it on the nail. I did not install OF but we are using MySQL. We did try to use LDAP at first but it did not behave the way wanted to. So we reverted to creating users and groups. The first post I made with the logs it did not have much in it but does give me more info. As it was this user who installed OF and MySQL and I don’t know if he used the root user for the db connection or his user that has root privileges on this box.
But your eagle eye gives me a good starting point.
Wanted to add to this from the logs of my spark client.
Jan 30, 2023 10:08:47 PM org.jivesoftware.spark.util.log.Log error
**SEVERE: Exception in Login:**
**org.jivesoftware.smack.SmackException$SmackWrappedException: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: Self Signed certificate**
** at org.jivesoftware.smack.AbstractXMPPConnection.setCurrentConnectionExceptionAndNotify(AbstractXMPPConnection.java:687)**
at org.jivesoftware.smack.AbstractXMPPConnection.notifyConnectionError(AbstractXMPPConnection.java:987)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.access$4100(XMPPTCPConnection.java:131)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.parsePackets(XMPPTCPConnection.java:1167)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.access$700(XMPPTCPConnection.java:916)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader$1.run(XMPPTCPConnection.java:939)
at java.lang.Thread.run(Unknown Source)
Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: Self Signed certificate
at sun.security.ssl.Alerts.getSSLException(Unknown Source)
at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)
at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)
at sun.security.ssl.Handshaker.processLoop(Unknown Source)
at sun.security.ssl.Handshaker.process_record(Unknown Source)
at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.proceedTLSReceived(XMPPTCPConnection.java:733)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.access$1400(XMPPTCPConnection.java:131)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.parsePackets(XMPPTCPConnection.java:990)
... 3 more
Caused by: java.security.cert.CertificateException: Self Signed certificate
at org.jivesoftware.sparkimpl.certificates.SparkTrustManager.doTheChecks(SparkTrustManager.java:132)
at org.jivesoftware.sparkimpl.certificates.SparkTrustManager.checkServerTrusted(SparkTrustManager.java:93)
at sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(Unknown Source)
... 14 more
Jan 30, 2023 10:08:55 PM org.jivesoftware.spark.util.log.Log error
SEVERE: Exception in Login:
org.jivesoftware.smack.SmackException$SmackWrappedException: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Certificate not in the TrustStore
at org.jivesoftware.smack.AbstractXMPPConnection.setCurrentConnectionExceptionAndNotify(AbstractXMPPConnection.java:687)
at org.jivesoftware.smack.AbstractXMPPConnection.notifyConnectionError(AbstractXMPPConnection.java:987)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.access$4100(XMPPTCPConnection.java:131)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.parsePackets(XMPPTCPConnection.java:1167)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.access$700(XMPPTCPConnection.java:916)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader$1.run(XMPPTCPConnection.java:939)
at java.lang.Thread.run(Unknown Source)
**Caused by: javax.net.ssl.SSLHandshakeException: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Certificate not in the TrustStore**
at sun.security.ssl.Alerts.getSSLException(Unknown Source)
at sun.security.ssl.SSLSocketImpl.fatal(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.Handshaker.fatalSE(Unknown Source)
at sun.security.ssl.ClientHandshaker.serverCertificate(Unknown Source)
at sun.security.ssl.ClientHandshaker.processMessage(Unknown Source)
at sun.security.ssl.Handshaker.processLoop(Unknown Source)
at sun.security.ssl.Handshaker.process_record(Unknown Source)
at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
at sun.security.ssl.SSLSocketImpl.performInitialHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.proceedTLSReceived(XMPPTCPConnection.java:733)
at org.jivesoftware.smack.tcp.XMPPTCPConnection.access$1400(XMPPTCPConnection.java:131)
at org.jivesoftware.smack.tcp.XMPPTCPConnection$PacketReader.parsePackets(XMPPTCPConnection.java:990)
... 3 more
Caused by: java.security.cert.CertificateException: java.security.cert.CertPathValidatorException: Certificate not in the TrustStore
They odd part is that only my co-worker and myself got the cert error when spark tried to reconnect. No other user got the error. I am using spark client version 3.0.0 whilst my co-worker is using client 3.0.1 and the rest of the users at at 2.9.4