The included script in Openfire 4.3.2 to create the database schema for Microsoft SQL Server, openfire_sqlserver.sql is wrong. Its code syntax is for MySQL instead of MSSQL, therefore one will not be able to create the database schema required. It will generate lots of errors when executing.
I was fortunate enough to have an older installation of Openfire 4.2.3 with the correct script to use. However, that script does not contain the update to add a column to one of the tables (I don’t remember which), but errors will show up in the Openfire error logs indicating which.
It’s more than that, the syntax is not MSSQL compatible. The script at …\Openfire\resources\database\openfire_sqlserver.sql contains the following syntax:
PRIMARY KEY (username, principal(200)) ==> the (200) is not compatible
MUC Tables # sign is not a compatible mssql syntax
There are a couple of handful more such instances.
I’m thinking maybe this problem occurred with recent SQL version. Nobody on the team is using MS SQL. Aside @speedy, who uses some version of SQL Express (probably older one). So, such issue went unnoticed.
You mentioned that you used 4.2.3 script. I have compared them and they are very similar aside of a few new lines in the latest. So 4.2.3 would fail also.
I am using sql express on one instance and will use real sql 2017 on others. It appears that in Openfire 4.3.2, the included script for openfire_mssql.sql erroneously is an exact copy of script openfire_mysql.sql.
Please forgive me for sticking a big foot in my mouth. It is my error. I made a copy of openfire_mysql.sql then ran it in mssql hence the problems I see. The syntax in 4.3.2. is correct. I will go away in shame now
