Openfire mysql integration

Openfire Openfire Dev
1

I am having a problem with integrating external mysql database with openfire server v3.7.I am relative new to openfire server but have been given the task to integrate openfire server to existing system.So Please help me in this problem.Below is the error that i am getting while trying to log in to openfire after doing the changes as specified in the documentation of the openfire server.

Exception:

java.lang.UnsupportedOperationException

at org.jivesoftware.openfire.user.JDBCUserProvider.createUser(JDBCUserProvider.jav a:173)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.createUser(JDBCAuthProvider.jav a:404)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.authenticate(JDBCAuthProvider.j ava:175)

at org.jivesoftware.openfire.auth.AuthFactory.authenticate(AuthFactory.java:176)

at org.jivesoftware.openfire.admin.login_jsp._jspService(login_jsp.java:149)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:530)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1216)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:50)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:425)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:494)

at org.eclipse.jetty.server.session.SessionHandler.handle(SessionHandler.java:182)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:93 3)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:362)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:867 )

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)

at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:245)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:126)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113)

at org.eclipse.jetty.server.Server.handle(Server.java:334)

at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559)

at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.j ava:1007)

at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747)

at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209)

at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406)

at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:4 62)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436)

at java.lang.Thread.run(Unknown Source)

The XML file of my setup is as follows:

The XML file is as below:

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

9090

9091

en

org.jivesoftware.database.EmbeddedConnectionProvider

true

com.mysql.jdbc.Driver

jdbc:mysql://localhost/tdb?user=root&password=dbadmin</co nnectionString>

org.jivesoftware.openfire.auth.JDBCAuthProvider

org.jivesoftware.openfire.user.JDBCUserProvider

SELECT password FROM t_userinfo_basic WHERE username=?

plain

SELECT name,email FROM t_userinfo_full WHERE username=?

SELECT COUNT(*) FROM t_userinfo_full

SELECT username FROM t_userinfo_full

SELECT username FROM t_userinfo_full WHERE

username

name

email

vivek, sandy

2

It looks to me like Openfire is attempting to create the user you’re logging in with (org.jivesoftware.openfire.user.JDBCUserProvider.createUser), and that’s failing because (if I’ve read the JDBC auth code correctly) user creation and editing is disabled when you’re using an external auth mechanism.

Are you trying to log in with a user that doesn’t exist? Normally that would be OK, since IIRC Openfire defaults to automatically creating users it doesn’t recognize when they try to log in, but it won’t work with external auth. Probably wouldn’t be very difficult to modify the code to add that capability, though.

If the user does exist, then it appears that Openfire thinks it doesn’t for some reason.

3

Hi Vivek

Do not try to do that with configuring through XML file

configure them in the ofproperty table
it will work
and

make sure the userid MUST not have whitespaces

shafi

1 Like
4

Thanks for your answer. I tried to enter manually the fields into ‘ofproperty’ table which openfire had created but it is still not working.

Hence i have attached below the values from the tables used for the purpose.Please can you read it and let me know any discrepancies if it exits.

mysql> SELECT * FROM ofproperty;

name | propValue

---------------------------------±-------------------------------------------

admin.authorizedUsernames | vivek198723, mandymoore

|

emailField | email

|

jdbcAuthProvider.passwordSQL | SELECT password FROM t_userinfo_basic WHERE username=? |

|

jdbcAuthProvider.passwordType | plain

|

jdbcProvider.connectionString | jdbc:mysql://localhost/tdb?user=root&password=dbadmin |

|

jdbcProvider.driver | com.mysql.jdbc.Driver

|

jdbcUserProvider.allUsersSQL | SELECT username FROM t_userinfo_full

|

jdbcUserProvider.loadUserSQL | SELECT name,email FROM t_userinfo_full WHERE username=? |

|

jdbcUserProvider.searchSQL | SELECT username FROM t_userinfo_full WHERE

|

jdbcUserProvider.userCountSQL | SELECT COUNT(*) FROM t_userinfo_full

|

nameField | name

|

passwordKey | NYD5RRk2d08zzm0

|

provider.admin.className | org.jivesoftware.openfire.admin.DefaultAdminProvider |

|

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

|

provider.group.className | org.jivesoftware.openfire.group.DefaultGroupProvider |

|

provider.lockout.className | org.jivesoftware.openfire.lockout.DefaultLockOutProvider |

|

provider.securityAudit.className | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider |

|

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

|

provider.vcard.className | org.jivesoftware.openfire.vcard.DefaultVCardProvider |

|

update.lastCheck | 1313586075385

|

usernameField | usernameField

|

xmpp.auth.anonymous | true

|

xmpp.domain | igpub900

|

xmpp.session.conflict-limit | 0

|

xmpp.socket.ssl.active | true

25 rows in set (0.11 sec)

mysql> select * from t_userinfo_basic;

| id | USERNAME | password | phonenumber | operator | createddate | status | phnoverified_date |

| 1000 | vivek198723 | vivek1987 | 900000000 | NULL | 2011-08-17 19:10:36 | ACTIVE | 2011-08-17 19:10:36 |

4 rows in set (0.13 sec)

mysql> select * from t_userinfo_full;

| id | name | username | firstname | lastname | relationshipstatus | relationshipwith | dateofbirth | gender | lookingfor | street |city | state | country | pincode | friendscount | fanscount | messagecount | photoscount | profileviewcount | requestscount | rating | email | phonetype | usertype | lastupdated | lastaccessed | status | message | iggold | emailverified_date | aboutme |

| 1000 | vivek | vivek198723 | VIVEK | NADAR | NULL | NONE | 2011-08-17 19:07:01 | MALE | NONE | NULL | NULL | NULL | INDIA | NULL | 1 |2 | 2 | 2 | 5 | 5 | 5 | NULL | NULL | NULL | NULL | NULL | NULL | 0 | NULL | NULL |

6 rows in set (0.05 sec)

5

Hi Vivek

I could see few issues here

First of change the DefaultGroupProvider to JDBCGroupProvider

and i didnt see any group releated properties here

what is the xmpp domain in your ofPropety table 


after u enter that

Only the users specified in authorisedJIDs will be able to login through ADMIN CONSOLE of Openfire

other users need to be checked only through xmpp client

authorizedJID MUST be of the name “username@domain”

Please correct these and let me know

1 Like
6

hi knight rider,

thanks for your reply

according to your reply i have following doubts,

  1. Firstly i havent entered any group related details as i didnot want to integrate any group related data as of now. Will do that after my basic authentication and user login starts working.]

  2. Secondly my domain name is igpub900 which as told i have updated to the admin.'authorizedUsernames' property of the ofproperty table. But i still cannot login to the admin cobnsole of the openfire.

But now my client software can login using the username and password in my external table. So thanks you very much for making that work!

So the only problem is that the admin console login is not working. Hence i have attached the ofproperty table details here please see it and let me know if there is any problem.

My external table structure is the same as given above.

mysql> INSERT INTO ofproperty SET name=‘admin.authorizedUsernames’,propValue=‘vivek198723@igpub900, mandymoore@igpub900’;

Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM ofproperty;

| name | propValue

|

±---------------------------------±-------------------------------------------

|
admin.authorizedUsernames | vivek198723@igpub900, mandymoore@igpub900

|

| emailField | email

|

| jdbcAuthProvider.passwordSQL | SELECT password FROM t_userinfo_basic WHERE username=?

| jdbcAuthProvider.passwordType | plain

| jdbcProvider.connectionString | jdbc:mysql://localhost/tdb?user=root&password=dbadmin

| jdbcProvider.driver | com.mysql.jdbc.Driver

| jdbcUserProvider.allUsersSQL | SELECT username FROM t_userinfo_full

| jdbcUserProvider.loadUserSQL | SELECT name,email FROM t_userinfo_full WHERE username=?

| jdbcUserProvider.searchSQL | SELECT username FROM t_userinfo_full WHERE

| jdbcUserProvider.userCountSQL | SELECT COUNT(*) FROM t_userinfo_full

| nameField | name

| passwordKey | NYD5RRk2d08zzm0

| provider.admin.className | org.jivesoftware.openfire.admin.DefaultAdminProvider

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

| provider.group.className | org.jivesoftware.openfire.group.DefaultGroupProvider

| provider.lockout.className | org.jivesoftware.openfire.lockout.DefaultLockOutProvider

| provider.securityAudit.className | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider

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

| provider.vcard.className | org.jivesoftware.openfire.vcard.DefaultVCardProvider

| update.lastCheck | 1313586075385

| usernameField | usernameField

| xmpp.auth.anonymous | true

| xmpp.domain | igpub900

| xmpp.session.conflict-limit | 0

| xmpp.socket.ssl.active | true

±---------------------------------±-------------------------------------------

25 rows in set (0.00 sec)

7

Are you attempting to log in to the admin console with the full JID, or just the node portion? That is, are you using “user@server”, or just “user”?

I just tried it on my admin console, and, despite the “admin.authorizedUsernames” requiring the full JID, trying to log in to the admin console with the whole thing fails–I have to use only the node (user) portion.

My *guess *is that it escapes the user name you put in that field, and assumes the local server is your domain, so it ends up attempting to log you in as something like “user\40domain@domain” if you use the whole JID.

8

Hi vivek

it is admin.authorizedJIDs=mandymoore@igpub900

**not the other one (admin.authorizedUsernames) **

after updating just restart your openfire server
it will work

**after this enter mandymoore as username and password and login from admin console
**

**Shafi
**

1 Like
9

Hi knight rider,

I have been able to login now.Thank you for your help.Bye!

10

hi paul,

i tried to log in with my usernames only.please read the topic to get the answer for why your setup didnt work.thank you.

11

Hi Knight Raider,

I m still facing issue with connectin external db.

Whe I also tried to enter manually the fields into ‘ofproperty’ table which openfire had created but it is still not working. The ‘user’ table which I have is as below:

mysql> select * from user;

±-------±--------------------------±---------±-------±---------±---------- -----------+

| userid | email | password | status | userType | token |

±-------±--------------------------±---------±-------±---------±---------- -----------+

| 1 | admin@aijazsuri | 123 | online | admin | mbniwhezfhwbcqhwrcqq |

and “ofproperty” table as :

mysql> select * from ofproperty;

±---------------------------------------------±------------------------------- ---------------------------------+

| name | propValue |

±---------------------------------------------±------------------------------- ---------------------------------+

| admin.authorizedJIDs | admin@aijazsuri |

| admin.authorizedUsernames | admin |

| cache.KrakenRegistrationCache.maxLifetime | -1 |

| cache.KrakenRegistrationCache.min | -1 |

| cache.KrakenRegistrationCache.size | -1 |

| cache.KrakenRegistrationCache.type | optimistic |

| cache.KrakenSessionLocationCache.maxLifetime | -1 |

| cache.KrakenSessionLocationCache.min | -1 |

| cache.KrakenSessionLocationCache.size | -1 |

| cache.KrakenSessionLocationCache.type | optimistic |

| emailField | email |

| jdbcAuthProvider.passwordSQL | SELECT password FROM user WHERE email=? |

| jdbcAuthProvider.passwordType | plain |

| jdbcProvider.connectionString | jdbc:mysql://localhost/webrtc?user=root&password= |

| jdbcProvider.driver | com.mysql.jdbc.Driver |

| jdbcUserProvider.allUsersSQL | SELECT email FROM user |

| jdbcUserProvider.loadUserSQL | SELECT userid,email FROM user WHERE email=?

|

| jdbcUserProvider.searchSQL | SELECT userid FROM user WHERE |

| jdbcUserProvider.userCountSQL | SELECT COUNT(*) FROM user |

| nameField | name |

| passwordKey | Eh325UvPE9lOnov |

| provider.admin.className | org.jivesoftware.openfire.admin.DefaultAdminProvider |

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

| provider.lockout.className | org.jivesoftware.openfire.lockout.DefaultLockOutProvider |

| provider.securityAudit.className | org.jivesoftware.openfire.security.DefaultSecurityAuditProvider |

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

| provider.vcard.className | org.jivesoftware.openfire.vcard.DefaultVCardProvider |

| update.lastCheck | 1388656517701 |

| usernameField | userid |

| xmpp.auth.anonymous | true |

| xmpp.domain | aijazsuri |

| xmpp.session.conflict-limit | 0 |

| xmpp.socket.ssl.active | true |

Exception observed:

java.lang.UnsupportedOperationException

But when I am connecting through a browser based xmpp sample client it is connecting all clients irrespective of whether user is present in “user” table or not


Please tell if I am setting any wrong property.

Thanks,

Aijaz