After fighting for a few days with connectivity issues with the “built-in” database and client connectivity, I decided to rebuild my OpenFire server (which I do love). This go-around, I will definately use SQL. So I downloaded the latest edition of Microsoft SQL Server Express, version 2017. I installed it, then installed OpenFire, and then I could never get it to connect/authenticate. The log file indicated that it was a login/authentication/password issue. I tried everything but it would never connect. I could not find anything that said 2017 wasn’t compatible so I thought I would write this post. Firewalls all turned off, nothing blocking, can telnet to the SQL server on the right port… just no luck getting Openfire to connect (during the post-install, setup wizard phase). So I uninstalled all of the 2017 SQL pieces and started over with SQL 2014 Express. After Completing the install, creating the database, running the SQL script (again, now on 2014) for OpenFire, I was able to connect just fine during the setup wizard. I imported my users which helped and started puting everything back together. On a side-note, I wish there was a way to export-import the groups as well as the users. There’s no way to add multiple people to a group so you have to add one at at time… which gets very cumbersome.
FWIW, I always struggle with MS SQL Server - by default the installation seems to not offer access via TCP, instead only local access via some under-the-covers mechanism. I then seem to spend an inordinate amount of time trying to work out how to enable TCP access.
I use ms sql express with all my deployments because I’m a terrible admin and can only do my job with a gui! lol I haven’t had any problem…I’ll try to give 2017 a spin and see if I hit any issues.
I have a Openfire 4.3.2 with an SQL2017 Express on second server, both servers are 2012R2.
The account I use to connect form openfire to SQL is local sql account.
When I install SQL I select “SQL Server and Windows Authentication”. once installed I connect using the SQL management studio and create a local SQL account which I use connect form openfire with.
I have also made the users default database the openfire database and in the “user mapping” give the use the dbo schema for the openfire database.
The setup is only in test at the moment but appears to be OK, hope this helps
