Cannot get Openfire to connect to MSSQL instance

Doug_Starkey · December 16, 2013, 7:41pm

Okay, maybe I’ll get a bad name starting off like this, but I am so frustrated I’m ready to rip out Openfire and actually PAY for an IM tool.

I came in this morning and all my Spark clients were reporting that they couldn’t log in; bad username & password. Since all the logins are automatic, I knew my users were not mistyping their passwords.

I tried to log in to the console and was similarly rejected.

I started searching the forums and found this link: http://community.igniterealtime.org/thread/35034

I followed the advice about changing the entry near the end of openfire.xml file, “true”. I changed that to false which forced me to go through the setup wizard again. But when I get to the part of the wizard where I try to connect to my MSSQL database, it will not connect.

Now, it’s been a couple of years since I set this up, so I’m a little rusty. But I re-read the manual and put it in the information as the manual indicated. Just for ease of discussion here are some of the pertinent details of our setup:

Server is a Windows Server 2008 R2 with MS SQL 2008 running.

The server name is BOB.

The instance name is OPENFIRE.

The database name is Openfire

I have a dbo use “openfireadmin” with a password that I have verified.

So, I’m in the “Database Settings - Standard Connection” window.

I choose “Microsoft SQLServer” from the “Database Driver Presets”

JDBC Driver Class: net.sourceforge.jtds.jdbc.Driver

Database URL: jdbc:jtds:sqlserver://BOB/OPENFIRE;appName=Openfire

Username: openfireadmin

Password:…

Minimum Connections: 5

Maximum Connections: 100

Connection Timeout: 1.0 Days

When I click continue, I always get the message, “A connection to the database could not be made. View the error message by opening the ‘\logs\error.log’ file, then go back to fix the problem.”

Consulting the error log, it appears to be rejecting over the log in, WHICH I KNOW TO BE VALID!!!

2013.12.16 09:56:00 org.jivesoftware.util.log.util.CommonsLogFactory - Prototype

java.sql.SQLException: Cannot open database “openfire” requested by the login. The login failed.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:602)

at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:344)

at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)

at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConn ectionBuilder.java:39)

at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)

at org.logicalcobwebs.proxool.Prototyper.sweep(Prototyper.java:102)

at org.logicalcobwebs.proxool.PrototyperThread.run(PrototyperThread.java:44)

I have even tried using the “sa” log in. I get the same result. This is SOOO exceptionally frustrating. Particularly when the program was working fine yesterday and is now, suddenly, unable to function.

I’m no SQL wizard. I can follow instructions pretty well, though. If anyone has any suggestions, I’m open to it.

I have read through every message I could find here at the Ignite Realtime community. I’ve tried various suggestions:

Set a “port” for the SQL instance and changed the URL to: jdbc:jtds:sqlserver://BOB:2433/OPENFIRE;appName=Openfire (based on this thread: http://community.igniterealtime.org/message/198297#198297 )

Also tried with appName=jive, which was the default. Same results.

I don’t know what else to try. Based on what I’m reading, this all should work.

This is a critical tool for our business and I can’t really afford for it to be down much longer. I hope someone here can help.

Jason60 · December 16, 2013, 9:54pm

hmm… if you re-ran the initial setup, then it likely created a new password for the openfire database user. I believe the setup creates and sets a random password. If so, then your Openfire install is now trying to connect to your database using the same openfire database user, but a different password… causing it to not connect.

You may want to back things up before trying anything further… since things sometimes tend to get worse before better!

Now, it doesn’t sound like you needed to reset openfire into the setup mode. It sounds like your openfire froze/locked, and usually a restart of the services or at worst, the server, woudl of gotten it back up and running. Not sure if you tried that or not…

If you do not have a lot of users, it may be simpler and faster to just set things up fresh instead of trying to guess at what went wrong. If you choose to go this way, after making a backup!!! – go ahead and blow out the old openfire database and start fresh.

log into your database server and do DROP DATABASE openfire;

That will kill it out permanentely, saving nothing.

You will then want to re-run the database setup so that openfire sets things up fresh. http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database .html

This will create a brand new openfire database on your SQL Server.

Now, re-run the setup on the openfire admin pages like you did, setting things to point back at your SQL Server. This should have openfire create the database user over again and set a new password. After this, things should be like a brand new fresh install of Openfire.

If you do end up blowing it all out and starting fresh, I would recommend grabbing the latest version of Openfire (3.8.2 as of writing this) and using that… there’s been a lot of improvements and bugfixes in the recent versions.

If you are trying to save the data, perhaps this message in this thread may be helpful: http://community.igniterealtime.org/message/234157#234157

I do not know the outcome of that users’s problem, however in that post I detaild some possible steps for a MySQL user to backup and possibly restore his data after creating a fresh database. It may not work at all, I have not tried it… but if you really really need to save the data, (after a backup!!!) you can try the equivelant SQL Server steps.

Doug_Starkey · December 17, 2013, 4:43pm

Jason wrote:

hmm… if you re-ran the initial setup, then it likely created a new password for the openfire database user. I believe the setup creates and sets a random password. If so, then your Openfire install is now trying to connect to your database using the same openfire database user, but a different password… causing it to not connect.

I don’t recall anything in the setup for creating an “openfire database user” or setting a random password. If it did such a thing, how are you supposed to know what the password is?

Now, it doesn’t sound like you needed to reset openfire into the setup mode. It sounds like your openfire froze/locked, and usually a restart of the services or at worst, the server, woudl of gotten it back up and running. Not sure if you tried that or not…

Oh, yes. I restarted the service, stopped & restarted the database. I think the “root” cause was that we had a key IT employee leave and so, as a security measure, we reset our administrative password. However, because we so rarely have to mess with Openfire, it didn’t occur to me that it might cause Openfire to be unable to log into the LDAP server. But once the reset was done, there was no way to get in to the Openfire Admin console to change the settings. And I’m too ignorant of the structure of Openfire to have a clue if there was some backend file or table I could edit that would have fixed it. But we’re too far gone to go back at this point.

If you do not have a lot of users, it may be simpler and faster to just set things up fresh instead of trying to guess at what went wrong. If you choose to go this way, after making a backup!!! – go ahead and blow out the old openfire database and start fresh.

I DO have a lot of users (well, to me it’s a lot… almost 100). But all our users are populated from Active Directory, So, recreating the user list shouldn’t be too difficult. But, as I said, it’s been a few years since we initially set up Openfire, so I don’t know that I can remember all the settings. I guess I can re-read the manual…

log into your database server and do DROP DATABASE openfire;

Okay, so I made a backup. I blew away the database using the “DROP DATABASE” command as you instructed. I installed the latest version (I had already downloaded it).

I then followed the instructions for creating a new database as outlined in the manual (your link http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database .html )

Now, re-run the setup on the openfire admin pages like you did, setting things to point back at your SQL Server. This should have openfire create the database user over again and set a new password. After this, things should be like a brand new fresh install of Openfire.

Okay, this is where things seem to diverge. There is NOTHING on the setup to set up a new user. When you launch the Openfire Server and then click the “Launch Admin” button, you get taken to a web page. First you are asked to pick a language. Next you are asked to enter, “Server Settings”:

Domain: (I used the server name, “BOB”)

Admin Console Port: 9090 (default)

Secure Admin Console Port: 9091 (default)

Next you’re asked to choose, “Standard Database Connection” or “Embedded Database”. I chose “Standard Database Connection”.

Now we get to the Database Settings - Standard Connection window. And here is where it falls apart.

But now, at least my error is different:

2013.12.17 07:54:24 org.jivesoftware.util.log.util.CommonsLogFactory - Prototype

java.sql.SQLException: Network error IOException: Connection refused: connect

at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:385)

at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)

at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConn ectionBuilder.java:39)

at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)

at org.logicalcobwebs.proxool.Prototyper.sweep(Prototyper.java:102)

at org.logicalcobwebs.proxool.PrototyperThread.run(PrototyperThread.java:44)

Caused by: java.net.ConnectException: Connection refused: connect

at java.net.PlainSocketImpl.socketConnect(Native Method)

at java.net.PlainSocketImpl.doConnect(Unknown Source)

at java.net.PlainSocketImpl.connectToAddress(Unknown Source)

at java.net.PlainSocketImpl.connect(Unknown Source)

at java.net.SocksSocketImpl.connect(Unknown Source)

at java.net.Socket.connect(Unknown Source)

at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.lang.reflect.Method.invoke(Unknown Source)

at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:3 04)

at net.sourceforge.jtds.jdbc.SharedSocket.(SharedSocket.java:255)

at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:310)

… 8 more

Jason60 · December 17, 2013, 8:57pm

hmm, ok, we’re getting close. You are still getting a Connection Refused error, indicating either your SQL Server is rejecting the connection (remote access turned off, and/or firewall on) OR, you have your database config wrong in openfire.

See my attached screenshot for some clarifications.

Basically, use you SA account when you are on the databse setup page - this will be the user Openfire uses to create it’s own special database user. Basically, openfire creates it’s own user and secret password during the initial setup. So, after the initial setup, it no longer uses the SA account, but rather it’s own. I do not know of a way to get that password, nor should you really need it. If you really need to manipulate the tables manually, you can simply do so from the SA account or create another account and add privlidges to access the openfire database.

For your hostname of the databse. If the database is on a different server, then put that server’s ip address or hostname there. Make sure the hostname resolves on your network, if it doesn’t or you are unsure, set it to the ip address instead. The [database-name] should get replaced with the databse you setup during the database guide… so usually it’s called openfire.

So you Database URL will look similar:

jdbc:jtds:sqlserver://192.168.254.13/openfire;appName=jive

if that does not connect still, then you likely have another problem. I do not use SQL Server myself, we use MySQL over here for Openfire. Maybe one of the other SQL Server guys knows something we do not (jdbc driver out of date, etc). But, if nothing on that system has changed, then likely it’s just a config issue with openfire.

For the AD integration, we don’t use that over here so I’m not of much help in that regrds. However, I know a lot of people on the forums do use AD integration, so perhaps try searching for a good walkthough on how to configure that.

speedy · December 18, 2013, 5:03pm

You have to set your sql instance port from dynamic to something static. If its the only instance on the server, than you can use the default port of 1433. If its not the only sql instance, than you may want to use something else, like 2433.

then your connector setting should go like this. notice the ( after the port number!! very important!

jdbc:jtds:sqlserver://SERVER_NAME:PORT#:/INSTANCE_NAME;appName=DATABASE_NAME

heres mine

jdbc:jtds:sqlserver://server1:2433:/sqlexpress;appName=openfire

Hope this helps

Also, you may want to update the driver that is used for openfire. you can download it here.

http://jtds.sourceforge.net/