Problems with Custom Database Integration Guide in OpenFire

Good day to everyone.

I want to integrate my custom SQL Server database (named OpenFireDB) with the one provided by OpenFire.

I am following the tutorial described in:

#1 http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html

To start OpenFire, I have done the steps explained in:

#2 http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database .html#sqlserver

to see if OpenFire worked with my database with success.

Now, I begin the steps described in #1

I stop OpenFire and open my XML file (conf/openfire.xml)

I follow the steps and my file ends like this:

<?xml version="1.0" encoding="UTF-8"?>

9090

9091

en

org.jivesoftware.database.DefaultConnectionProvider

net.sourceforge.jtds.jdbc.Driver

jdbc:jtds:sqlserver://christian/OpenFireDB;appName=jive

XX

XX

select 1

true

true

5

25

1.0

true

net.sourceforge.jtds.jdbc.Driver

jdbc:jtds:sqlserver://christian/OpenFireDB;appName=jive??user =XX&password=XX

org.jivesoftware.openfire.auth.JDBCAuthProvider

org.jivesoftware.openfire.user.JDBCUserProvider

SELECT password FROM users WHERE username=?

plain

SELECT name,email FROM users WHERE username=?

SELECT COUNT(*) FROM users

SELECT username FROM users

SELECT username FROM users WHERE

user_name

name

user_email

My database contains a table called “USERS” with these columns:

USER_NAME, NORMAL_PASSWORD, UBER_PASSWORD, NAME, USER_EMAIL

NORMAL_PASSWORD is a column to make tests with plain passwords

UBER_PASSWORD is a colum to make tests with encrypted passwords once I am able to connect my database with OpenFire

And that is the reason I have written it in a different from the tutorial in section

Instead of “user_account” which is written in the tutorial, I think that I should write there the table where my users are (USERS)

I have done the same for the sections where “myUsers” table is referred in that guide.

I have even tried not changing the “user_account” table in section to try if it worked; but it did not.

Then, following steps in #1, I start again OpenFire

I click on “Launch Admin”, I log with my admin account and check the Users/Groups section, in Users Summary.

1.- I do not see the user I have in my USERS table, that should be integrated dynamically in OpenFire.

2.- If I add a new user in that interface, it only updates the table “ofUser” of OpenFire, but does not generate that user in my table

What am I doing wrong?

Would you mind explaining me my flaws and telling me how to do this properly, please?

Thank you very much for your Time and have a nice day (^_^)

Hi Christian,

here a quote I sent another user some time ago:

Sadly, you can’t really trust the documentation regarding the external DB integration.

The openfire.xml file isn’t really important. It only has some very basic settings there.

Everything else has to be configured in the system properties in the webinterface.

For the basic settings for authentication you need to configure the following settings:

  • jdbcAuthProvider.passwordSQL
  • jdbcAuthProvider.passwordType
  • jdbcProvider.connectionString
  • jdbcProvider.driver
  • jdbcUserProvider.loadUserSQL

I didn’t configure anything else, as I encountered some problems with it when going to the users/groups overview in the webinterface (no LIMIT is appended to the statement, so it killed my DB )

The documentation is only correct regarding what you can set and how the format should be

First of all, thank you very much for your quick reply, Michael.

When you say that I need to configure those settings, you mean that I need to create those properties inside the Web Interface, in the section:

Server => Server Manager => System Properties

Where there is a list of some properties, and I can add one at the bottom.

Is this what you mean, please ?

Exactly!

Thank you very much again, Michael.

I have been adding those properties in the Web Interface to try them. (I will upload an image as soon as I can)

Unfortunately, I still have not been able to add a new user to my database using the Web Interface, as I think it should work…

I want to be able to add a user using the Web Interface and see that the user has been added in my database.

It is only added to OpenFire database “ofUser” table, instead of my “Users” table.

Oh well. I think I missed the point about adding a user . I do not have expierence in this, I disabled the feature to add users to the db as this is done by our website and shouldn’t be done via openfire.

I’m sorry.

The system does not allow me to upload the image I promised…

I still have not solved the problem.

Hi Michael,

I am trying to setup a external database for my Openfire server too, however even I have placed the Property names and values in “System Properties” and restarted the server, it still uses HSQL database (embedded), is there a way to select which database to use from the web gui? The Openfire I am using is the one emebedded on a Elastix server.

Hi Xybrek,

hmm, maybe you’ll still need something in the openfire.xml config file.

I’ve got the following there:

org.jivesoftware.database.DefaultConnectionProvider

com.mysql.jdbc.Driver

jdbc:mysql://HOST:3306/DB?useUnicode=true&amp;charac terEncoding=UTF-8&amp;characterSetResults=UTF-8

chat

PW

select 1

true

true

5

25

1.0

Finally I was able to connect my Database with OpenFire.

To do so, I modified the following properties in the table “ofProperty”.

Those properties, need to be the same than in “openfire.xml”

openfire.xml

com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://[MyDBIPAddress];databaseName=OPENFIREDB;user=xx;password=xx

org.jivesoftware.openfire.auth.JDBCAuthProvider

org.jivesoftware.openfire.user.JDBCUserProvider

SELECT password FROM USERS WHERE username=?

plain

SELECT name,user_email FROM Users WHERE username=?

SELECT COUNT(*) FROM Users

SELECT username FROM Users

SELECT username FROM Users WHERE

username

name

user_email

DataBase OPENFIREDB => Table ofProperty:

jdbcProvider.driver = com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbcProvider.connectionString = jdbc:sqlserver://[MyDBIPAddress];databaseName=OPENFIREDB;user=xx;password=xx

provider.auth.className = org.jivesoftware.openfire.auth.JDBCAuthProvider

provider.user.className = org.jivesoftware.openfire.user.JDBCUserProvider

jdbcAuthProvider.passwordSQL = SELECT password FROM USERS WHERE username=?

jdbcAuthProvider.passwordType = plain

jdbcUserProvider.loadUserSQL = SELECT name,user_email FROM Users WHERE username=?

jdbcUserProvider.userCountSQL = SELECT COUNT(*) FROM Users

jdbcUserProvider.allUsersSQL = SELECT username FROM Users

jdbcUserProvider.searchSQL = SELECT username FROM Users WHERE

jdbcUserProvider.usernameField = username

jdbcUserProvider.nameField = name

jdbcUserProvider.emailField = user_email

1 Like

Hello,

I have follow the same step as you have define in above answer but i can’t login after setting these 2 property.

provider.auth.className = org.jivesoftware.openfire.auth.JDBCAuthProvider

provider.user.className = org.jivesoftware.openfire.user.JDBCUserProvider

Do you have any idea why this is happening.I have tried with version 3.7 and 3.8.1 also but issue in both.

1 Like

hii jugni,

i am facing the same problem. did you find any solution?

any help will be appriciated…

SQL Server appears to be supported by default… no need to do a “custom integration” now.

1 Like

yes but what if i wan to use my website users wich are already registered in another table of mysql database.

and the all above procedure describe above is working fine but problem is only

i can’t login to admin panel after setting these 2 property.

provider.auth.className = org.jivesoftware.openfire.auth.JDBCAuthProvider

provider.user.className = org.jivesoftware.openfire.user.JDBCUserProvider

hello,

i have the same requirements as you have and follow the same procedure as you mention in this topic…

but my problem is when i cahnge

provider.auth.className = org.jivesoftware.openfire.auth.JDBCAuthProvider

in ofproperty table my admin consol not working … and also cant login with ofuser table’s user accounts…

any clue??

Hi Christian,

I tried to do exactly what you did to integrate openfire with my existing MSSQL database but when I set

provider.auth.className
org.jivesoftware.openfire.auth.JDBCAuthProvider
provider.user.className
org.jivesoftware.openfire.user.JDBCUserProvider

I cannot login to the admin console and users in my database (my existing MSSQL DB) cannot be authenticated.

Could you please share your configuration?

try changing “admin.authorizedJIDs” to “username@servername” in “ofproperty” table.

“username” is the loginname in the custom user database. and “servername” is openfire’s name.

i have spend 3days to make it work.

i didn’t modify the openfire.xml, just modify the ofproperty table. don’t forget to RESTART the database server … and openfire server.

but there is a bug: i can’t use MD5 as passwordencrpyt type. i don’t know why… i am using plain now. seems good for now.

here is my update sql. hope it helps.

  1. 修改openfire的数据库

添加jdbc驱动

INSERT INTO ofproperty VALUES (‘jdbcProvider.driver’, ‘com.mysql.jdbc.Driver’);

指定用户数据库连接

INSERT INTO ofproperty VALUES (‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost:3306/dianzhu_dev?user=root&password=root’);

用户登陆认证类

UPDATE ofproperty SET propValue = ‘org.jivesoftware.openfire.auth.JDBCAuthProvider’ WHERE name = ‘provider.auth.className’;

通过用户名查询密码的sql password 是用户表中的密码字段

INSERT INTO ofproperty VALUES (‘jdbcAuthProvider.passwordSQL’, ‘SELECT plainPassword FROM dzmembership WHERE username= ?’);

密码加密方式 可选:plain(不加密),md5,sha1,sha256,sha512

INSERT INTO ofproperty VALUES (‘jdbcAuthProvider.passwordType’, ‘plain’);

查看用户的类,控制台查看用户/组

UPDATE ofproperty SET propValue = ‘org.jivesoftware.openfire.user.JDBCUserProvider’ WHERE name = ‘provider.user.className’;

查询用户信息

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.loadUserSQL’, ‘SELECT username AS NAME,username FROM dzmembership WHERE username=?’);

查询用户数量

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.userCountSQL’, ‘SELECT COUNT(*) FROM dzmembership’);

查询所有用户

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.allUsersSQL’, ‘SELECT username FROM dzmembership’);

搜索用户

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.searchSQL’, ‘SELECT username FROM dzmembership WHERE’);

指定控制台用户名对应的字段

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.usernameField’, ‘username’);

控制台名称对应的字段

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.nameField’, ‘username’);

电子邮件对应的字段

INSERT INTO ofproperty VALUES (‘jdbcUserProvider.emailField’, ‘email’);

##修改后台管理员

UPDATE ofproperty SET propValue = ‘phiree@yuanfei-pc’ WHERE name = ‘admin.authorizedJIDs’;