Openfire with MicrosoftSQL2005 database install failing

Openfire Openfire Support
1

We are utilizing OpenFire 3.9.3 running on server 2012r2 and the database (openfire) is on Microsoft SQL2005. We have installed OpenFire with no errors and have gotten to the database settings section in the setup. We have followed the setup guides for OpenFire (http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/install- guide.html) and the Database (http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database .html). However are unable to continue through the setup interface.

We first had issues with the following warnings while creating the SQL Database:

Warning! The maximum key length is 900 bytes.
The index ‘ofRoster_jid_idx’ has maximum length of 2048 bytes. For some combination of large values, the insert/update operation will fail.

Warning! The maximum key length is 900 bytes.
The index ‘ofSASLAuthorized_pk’ has maximum length of 4128 bytes. For some combination of large values, the insert/update operation will fail

The SQL server would not create the tables, We then found the board (https://community.igniterealtime.org/message/242768#242768) with speedy’s solution to change the value of the jid, and principle. This did allow the openfire server to create the tables correctly in the SQL database. However now we are getting the following error in the openfire setup when we click continue:

The Openfire database schema does not appear to be installed. Follow the installation guide to fix this issue.

Below are the follow settings we are using:

Database driver preset: Microsoft SQLserver

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

Database URL: jdbc:jtds:sqlserver://hostname:1433/openfire;appName=jive (note we are inserting our computer’s name, where it says hostname)

We are using the username and password for the SQL database that was created for the database and is also the Database owner.

Any assistance on this issue would be appreciated.

2

Please give this a try.

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 (:slight_smile: after the port number!! very important!

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

here’s 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/

3

I tried the connection to SQL with a different port (we only have 1 instance), but just of the sake of trying. It did change messages, however still failed. I also updated the jtds driver, and tis still failed. We desired that we could either place it on another SQL box or an Oracle Database. We ended up making space and placed the database on SQL2008r2 and it connected first time and works great. We are going to test (it will be a few weeks) if it will work “flawlessly” with SQL2014.