OpenFire integrates an external database

Question

Hi, I find it difficult to integrate my existing OpenFire installation with an existing database.

I have 2 databases, namely (e.g. goals)

• db_mainsite
• db_openfire

Inside my db_mainsite, I have a table called tbl_user where there are 2 columns, namely gw_userunique and gw_password (VARCHAR 255, however using SHA-1 hashing algo).

Both databases are located on the same machine (server), having the same physical location.

In my conf / openfire.xml I set the following lines

<jive> ... <jdbcProvider> <driver>com.mysql.jdbc.Driver</driver> <connectionString>jdbc:mysql://localhost/db_mainsite?user=username&amp;password=secret</connectionString> </jdbcProvider> <provider> <auth> <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> </auth> </provider> <jdbcAuthProvider> <passwordSQL>SELECT password FROM tbl_user WHERE gw_userunique=?</passwordSQL> <passwordType>sha1</passwordType> </jdbcAuthProvider> ... </jive> 

Unfortunately, whenever I try to log in using the username + password stored in db_mainsite, it always fails.

I also have a restart of OpenFire.

Can someone tell me what is wrong?

Greetings

Answer 1

no need to change the configuration file, just run the script in your openfire database (confirmed in 3.10.3):

 ## add jdbc drive INSERT INTO `ofproperty` VALUES ('jdbcProvider.driver', 'com.mysql.jdbc.Driver'); ## external membership database connection INSERT INTO `ofproperty` VALUES ('jdbcProvider.connectionString', 'jdbc:mysql://youripaddress:3306/db_mainsite?user=root&password=root'); ## auth UPDATE `ofproperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE NAME='provider.auth.className'; ## search password INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordSQL', 'SELECT plainPassword FROM dzmembership WHERE id= ?'); ## encrypted type：plain，md5，sha1，sha256，sha512 INSERT INTO `ofproperty` VALUES ('jdbcAuthProvider.passwordType', 'plain'); ## displyed in admin console UPDATE `ofproperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE NAME='provider.user.className'; ## uyser info in admin console INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.loadUserSQL', 'SELECT username AS NAME,concat(username,''_'',nickname) FROM dzmembership WHERE id=?'); ## user amount INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM dzmembership'); ## all users INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.allUsersSQL', 'SELECT id FROM dzmembership'); ## search INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.searchSQL', 'SELECT id FROM dzmembership WHERE'); ## username displayed in console INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.usernameField', 'username'); ## id displayed in console INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.nameField', 'id'); ## email displayed in console INSERT INTO `ofproperty` VALUES ('jdbcUserProvider.emailField', 'email'); ##admin username INSERT INTO `ofproperty` VALUES ('admin.authorizedJIDs', ' 13cb2932-e855-4c3e-8e54-a58e0135802d@ipaddress '); UPDATE ofproperty SET propValue='ipaddress' WHERE NAME='xmpp.domain'