Auth and groups against existing mysql table? (phpbb users)

Hello,

I would like to use openfire with data from a phpbb db.

I found for example 2 threads, sadly offered both no working example :confused:

The first is a bit older:

Today it is possible to set password to a MD5 type, so this shouldn’t be a problem anymore.

Then a newer Thread, the autor seem to have a working solution - but sadly he didn’t show how:

There are some other threads, also in other forums, but sadly no where a working solution

I tested with a local testinstallation, so it’s not the setup on my webserver

[/code]

Server starts - but no login possible, not @ the admin console neither with a jabber client.

The error log:

2008.06.19 23:15:13 org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvide r.java:253) Exception in JDBCAuthProvider

java.sql.SQLException: The url cannot be null

at java.sql.DriverManager.getConnection(DriverManager.java:554)

at java.sql.DriverManager.getConnection(DriverManager.java:207)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvid er.java:239)

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

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

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

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

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

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1093)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

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

at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)

at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)

at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)

at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)

at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)

at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)

at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:206)

at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)

at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)

at org.mortbay.jetty.Server.handle(Server.java:324)

at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)

at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843 )

at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)

at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)

at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)

at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)

at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)[/ code]

Best Regards,

mFuSE

Hi,

I just experienced the same Issue with Integration of Openfire to phpBB3 (even the same Error-Messages). If anyone knows what to do I’m really interested in this as well as i want to serve about 9.000 Users with Openfire and phpBB3.

But what I’m wondering about is that you say that it is possible to set MD5 passwords. MD5 is (by default) a very small part of the password stored in phpbb_users as you can read here: http://www.phpbb.com/community/viewtopic.php?f=71&t=585387#p3226381 - may be this could already be the reason?

Regards,

Chris

Hi,

sounds like a possible fault. So i’ll test it with phpbb 2.0 which should work …

Hi,

i tried something different:

http://www.phpbb.com/community/viewtopic.php?f=71&t=585387&st=0&sk=t&sd=a&start= 45#p3436095

edit: and got it work!

… sady there is one bug flaw … which is also described here: http://www.igniterealtime.org/community/message/142198

There seem to be no solution so far?

But everything else work just perfect!

Usermapping, Groupmapping … just perfect!

There have to be a solution for the “usernames with illegal characters” problem? xD

So far - my working solution with a custom user database (phpbb 3.x) (phpbb 3.x standard md5 hashing mod required!) and an own database for openfire:

<?xml version="1.0" encoding="UTF-8"?> <jive>   <adminConsole>     <port>9090</port>      <securePort>9091</securePort>   </adminConsole>    <admin>     <authorizedUsernames>admin,mfuse,123456</authorizedUsernames>   </admin>    <locale>de</locale>    <jdbcProvider>     <driver>com.mysql.jdbc.Driver</driver>      <connectionString>jdbc:mysql://localhost/phpbb?user=root</connectionString>   </jdbcProvider>    <database>     <defaultProvider>       <driver>com.mysql.jdbc.Driver</driver>        <serverURL>jdbc:mysql://localhost:3306/firedb</serverURL>        <username>root</username>        <password></password>        <testSQL>select 1</testSQL>        <testBeforeUse>true</testBeforeUse>        <testAfterUse>true</testAfterUse>        <minConnections>5</minConnections>        <maxConnections>15</maxConnections>        <connectionTimeout>1.0</connectionTimeout>     </defaultProvider>   </database>    <provider>     <auth>       <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>     </auth>      <user>       <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>     </user>      <group>       <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>     </group>   </provider>    <jdbcAuthProvider>     <passwordSQL>SELECT user_password FROM phpbb_users WHERE username_clean=?</passwordSQL>      <passwordType>md5</passwordType>   </jdbcAuthProvider>    <jdbcUserProvider>     <loadUserSQL>SELECT username_clean,user_email FROM phpbb_users WHERE username_clean=?</loadUserSQL>      <userCountSQL>SELECT COUNT(*) FROM phpbb_users</userCountSQL>      <allUsersSQL>SELECT username_clean FROM phpbb_users</allUsersSQL>      <searchSQL>SELECT username_clean FROM phpbb_users WHERE</searchSQL>      <userGroupsSQL>SELECT group_name FROM phpbb_groups WHERE group_id in (SELECT group_id FROM phpbb_user_group WHERE user_id in (SELECT user_id FROM phpbb_users WHERE username=?))</userGroupsSQL>      <usernameField>username_clean</usernameField>      <nameField>username</nameField>      <groupField>group_name</groupField>      <emailField>user_email</emailField>   </jdbcUserProvider>    <jdbcGroupProvider>     <groupCountSQL>SELECT count(*) FROM phpbb_groups where group_name != 'BOTS'</groupCountSQL>      <allGroupsSQL>SELECT group_name FROM phpbb_groups where group_name != 'BOTS'</allGroupsSQL>      <userGroupsSQL>SELECT group_name FROM phpbb_groups WHERE group_id in (SELECT group_id FROM phpbb_user_group WHERE user_id in (SELECT user_id FROM phpbb_users WHERE username=?))</userGroupsSQL>      <descriptionSQL>SELECT group_desc FROM phpbb_groups WHERE group_name=?</descriptionSQL>      <loadMembersSQL>SELECT username FROM phpbb_users WHERE user_id in (SELECT user_id FROM phpbb_user_group WHERE group_leader=0 and group_id in (SELECT group_id FROM phpbb_groups WHERE group_name != 'BOTS' and group_name=?))</loadMembersSQL>      <loadAdminsSQL>SELECT username FROM phpbb_users WHERE user_id in (SELECT user_id FROM phpbb_user_group WHERE group_leader=1 and group_id in (SELECT group_id FROM phpbb_groups WHERE group_name != 'BOTS' and group_name=?))</loadAdminsSQL>   </jdbcGroupProvider>    <connectionProvider>     <className>org.jivesoftware.database.DefaultConnectionProvider</className>   </connectionProvider>    <setup>true</setup> </jive>

semi solution @ my last edit