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