I have an openfire database in SQL 2008R2 and Openfire version 4.7.5.
We are going to migrate the OS to Windows 2022 Server and for this we want to take advantage and move the installation to a new server and migrate the database to a new instance in SQL 2017.
What I did was:
Backup the database and restore it to the new SQL instance. I created the same SQL user and the same password.
I changed the openfire.xml to the new instance, restarted the service and got the following error:
Podem dar-me uma ajuda para o que devo fazer?
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: The SELECT permission was denied on the object 'ofID', database 'Openfire', schema 'dbo'.
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:1062)
at org.jivesoftware.openfire.XMPPServer.start(XMPPServer.java:706)
at org.jivesoftware.openfire.XMPPServer.<init>(XMPPServer.java:255)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at java.lang.Class.newInstance(Unknown Source)
at org.jivesoftware.openfire.starter.ServerStarter.start(ServerStarter.java:92)
at org.jivesoftware.openfire.starter.ServerStarter.main(ServerStarter.java:56)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.exe4j.runtime.LauncherEngine.launch(LauncherEngine.java:84)
at com.exe4j.runtime.WinLauncher$2.run(WinLauncher.java:90)
Caused by: java.sql.SQLException: The SELECT permission was denied on the object 'ofID', database 'Openfire', schema 'dbo'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:505)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:1029)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.jivesoftware.openfire.XMPPServer.verifyDataSource(XMPPServer.java:1054)
... 15 more
Halting server...
Server halted
Hi Guus!
Thanks for your reply, I’ve got past that. I didn’t actually have all the necessary permissions.
I just restarted the service and the setup and then restarted again and it worked.
However, I now have a new problem: I can’t log in to the Admin Console. I get the following message:
“Login failed: make sure your username and password are correct and that you’re an admin.”
My user is guaranteed to be admin with grant to enter in the console
Did you happen to use a different value for the XMPP domain name in the new server? That will cause this (and possibly other) issues.
If you changed the XMPP domain name of your server, then you’ll probably need to update some records in the database. For this particular problem, have a look at this record:
SELECT * FROM ofProperty WHERE name = 'admin.authorizedJIDs'
That will hold values that use the old XMPP domain. Update that to match the new XMPP domain, then restart Openfire. I think that this will fix this particular issue (but you might run into others - changing the domain of a system is not recommended).
Hello
Comparing the values, they are exactly the same
But let me tell you, at the moment I’ve only transferred the DB from the SQL server. The Openfire installation is the same.
Openfire is on Host1.lidu.extranet and xmpp server host1.lido.private and the DB has been transferred from the DATA01.lidu.intra (with SQL2008R2 server) to DATA02.lidu.intra.(with SQL2017 server)
I’m assuming that the XMPP domain that is being used is equal to the hostname of the server, which is a common setup.
In that case, the admin.authorizedJIDs will probably include a record that has: diogo_maia@OLDHOSTNAME. This needs to be modified to be diogo_maia@NEWHOSTNAME.
I’m sorry, but I don’t understand.
If there is no change to the Openfire installation, which is still in the same version and on the same server, only the database changes instance. This change doesn’t make sense to me because the hosts are exactly the same.
Good evening,
I’m breaking the elephant into pieces.
I’ve now managed to log in to openfire, but only on localhost on port 9090 with the admin account.
But I wanted to do it only through port 9091, as it was. What do I have to do?
I’m really sorry for my ignorance on this subject, but this job has fallen into my hands because the person who used to do it is no longer with us.
Hi Guus!
I’ve solved the problem. In other words, the transfer of the database to the new server is now complete. Basically, when running the setup, I had to force the XMPP server and the Server HostName (FQDN). and Restart the service.
At this stage I was able to log in, but only on localhost on port 9090. Then, when I analyzed openfirexml, I came across this extra code compared to the original (which I commented out and then got everything working normally). Which of these parameters is preventing https (port 9091)?
