Same old integration question

I have been reading through the forum 4 hours today and 3 hours yesterday finding myself very frustrated. Finally I am willing to take the flaming and actually post.

I have looked at every page in the documentation on itegrating with a custom database and do no see the specifications I am confused on. I have VBulletin and Joomla with a database of a few thousand users. No LDAP yet, because I am scared of messing with what currently works well (or messing up rather).

Nowhere in the documentation does it state how or where to specify what the external database’s address, port or even how to map the schema. Sure there are documents explaining the schema mapping for openfire but jiveUser is not the table name where my userdata is kept in my database that Joomla uses and even the field I use is not username as openfire is calling for, mine is user_name. So where do I tell open fire to look for this? If I could just get a simple answer on this I am sure I could use that answer generically to get the password, email address and groups all done this way. It’s the simple questions that so many seem to have had in all my forum reading hereyet some of the simple ones are the posts with no replies. ^^

On the documentation page I know it gives examples of configurations but nothing specific on how to call for a field from a different table in a different database that has a different schema. Please a little direction, I have come a very long way the past 7 days. Now I know how to use PUTTY!!!

Hi igotnotime,

I wonder which document you’re referring at. Is it http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html ?

If you want to connect to your custom database, the doc shows the part you need to add and modify in your conf/openfire.xml:

<jive>
  ...
  <jdbcProvider>
    <driver>com.mysql.jdbc.Driver</driver>
    <connectionString>jdbc:mysql://localhost/dbname?user=username&amp;password=secret</connectionString>
  </jdbcProvider>
  ...
</jive>

To use a different address, port and database name, you change what’s within the . Say that you need to use MySQL and set the address=192.168.0.3, port=3333 and databasename=myopenfire, then:

<connectionString>jdbc:mysql://192.168.0.3:3333/myopenfire?user=username&amp;password=secret</connectionString>

Which part of the doc bellow that you think doesn’t work for schema mapping? :

<jive>
  ...
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
    <user>
      <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    </user>
  </provider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM user_account WHERE username=?</passwordSQL>
     <passwordType>plain</passwordType>
  </jdbcAuthProvider>
  <jdbcUserProvider>
     <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL>
     <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL>
     <allUsersSQL>SELECT username FROM myUser</allUsersSQL>
     <searchSQL>SELECT username FROM myUser WHERE</searchSQL>
     <usernameField>username</usernameField>
     <nameField>name</nameField>
     <emailField>email</emailField>
  </jdbcUserProvider>
   ... </jive>

If your user table is jomla_account, the password field is pass, the username field is user_name then what you want to do is to replace with:

<passwordSQL>SELECT pass FROM jomla_account WHERE user_name=?</passwordSQL>

You should be able to imply from above that myUser is the table name that replaces jiveUser.

Hi,

you may really post a problem if you think that the issue should be obvious, spending “4 hours today and 3 hours yesterday” makes we wonder whether you have got a lot of time or no time. (;

Of course Jivesoftware does not document how VBulletin or Joomla store their users, but the database guide Aznidin did mention is very helpful if you know this.

Do you know now all features of Putty like X11 tunneling, port forwarding and key mappings or just the basic Putty usage?

LG

Believe me I am not trying to be rude, I am very happy that this system has become open and have told everyone on my level how impressive it’s features are, nor am I trying to waste anyone’s time, just am not at the proper computer at the moment to check this.

Yes that was that documentation I was referring to. Here is where I am lost. … I deleted all tables this setup, then set the setup option to false in config file. I then start openfire and begin the setup process, when it asks for the SQL server I am planning to use, I do not enter the info for the database I just setup to use in Openfire? In other if I am using my existing Joomla database for authentication and registration I would enter that database info during the setup at step 3?

Step 3 >> http://images.howtoforge.com/images/installing_openfire_and_spark/image008.jpg

I was under the impression that there would be the database for openfire then in addition to that would be the second database (the external) that I get auth data from. Is this totally off-base then? Does install matter, I can do anything during install and simply correct in the openfire.xml file or no?

If this openfire.xml file needs this information prior to running the install do I need to un-install and do over?

Last question is how do I post code in this forum I couldn’t find the tags? I do appreciate the quick response and will eagerly try this as soon as I get to my computer tonight. No worries I will post results for future noobs like me.

LG I foremost want to thank you for all your posts! I have learned soooooo much from them in the past few days before I am even running a live server. In reply I DO think the answer HAS to be simple and obvious, I only blame myself for not ‘getting it’ yet. I used to think I was pretty quick at the very least getting something half @55ed. Further, I do completely understand that it is not JS’s fault or responsibility for me not understanding the documentation enough to come up with the solution on my own, and know that the schema is laid very nicely in the docs. In fact I have the schema printed and laying next to my keyboard at my desk. I am appreciative, honestly. I really put off finally posting, usually the replies are useless with something or other about using Google (which I did also but not much out there considering this is so newly open).

As far as Putty (this is funny) Before I started reading here I only knew how to log in and type TOP. Honest to God! Now I am moving files, using PICO and NANO, changing permissions, installing and even moving database tables. I have come a VERY long way this past week. So even though I am very stuck right at the moment, I am happy I started learning about Openfire because I feel like I have learned so much so quickly. You know what I am finding I really like? -su root LOL

Hi,

Here is where I am lost. … I deleted all tables this setup, then set the setup option to false in config file.

is odd. The documentation does not mention do delete something or to set setup=false in openfire.xml.

You need a working setup, likely with the option “Store users and groups in the server database. This is the best option for simple deployments.”. Then you need to

  1. Stop Openfire.

  2. Edit conf/openfire.xml in your Openfire installation folder as described below using your favorite editor.

  3. Restart Openfire.

There is no option to configure your remote database during setup, the option to configure LDAP was added some months ago. In former times also LDAP users had to run a normal setup and edit then openfire.xml.

I can do anything during install and simply correct in the openfire.xml file or no?

This should be the case, but as Openfire caches remote users in it’s local database it does not work always.

Last question is how do I post code in this forum I couldn’t find the tags?

here goes the code Remove the spaces around code and do not add a / to the 2nd code tag.

LG

I am trying to figure out the forum nested reply layout here. I have tried editing the xml file I constantly get 1 of 3 problems. I have had error 404, error 500 and then the lovely install step 1 again. This was from a working & running fresh install. I really feel like that is where I am messing up something. The question of whether or not there are in fact 2 databases running was not cleared I don’t think. If I setup to run on an existing database (because of performance issues as described in the install process) it then asks for the database info. I enter the database info for the database that is empty I have setup for openfire. Since I am getting user auth from another database does that make this one irrelevant? I assumed that for all the configuration options on the admin screens that it needed it’s own database correct? Perhaps it is merely a config file problemso here is what I have with only IP and database login changed.

<?xml version="1.0" encoding="UTF-8"?> <!--
    This file stores bootstrap properties needed by Openfire.
    Property names must be in the format: "prop.name.is.blah=value"
    That will be stored as:
        <prop>
            <name>
                <is>
                    <blah>value</blah>
                </is>
            </name>
        </prop>     Most properties are stored in the Openfire database. A
    property viewer and editor is included in the admin console.
-->
<!-- root element, all properties must be under this element -->
<jive>   <adminConsole>     <!-- Disable either port by setting the value to -1 -->      <port>9090</port>      <securePort>9091</securePort>   </adminConsole>    <admin>     <!-- Use this section to define users that will have admin privileges. Below,
          you will find two ways to specify which users are admins. Admins will
          have access to the admin console (only local users) and may have also access
          to other functionalities like ad-hoc commands. -->      <!-- By default, only the user with the username "admin" can login
         to the admin console. Alternatively, you can specify a comma-delimitted
         list usernames that should be authorized to login to the admin console
         by setting the <authorizedUsernames> field below. -->      <!-- <authorizedUsernames></authorizedUsernames> -->      <!-- Comma-delimitted list of bare JIDs. The JIDs may belong to local
         or remote users. -->      <!-- <authorizedJIDs></authorizedJIDs> -->   </admin>    <locale>en</locale>    <!-- Network settings. By default, Openfire will bind to all network interfaces.
      Alternatively, you can specify a specific network interfaces that the server
      will listen on. For example, 127.0.0.1. This setting is generally only useful
       on multi-homed servers. -->    <!--
    <network>
        <interface></interface>
    </network>
    -->    <connectionProvider>
    <className>org.jivesoftware.database.DefaultConnectionProvider</className>
  </connectionProvider>   <database>
    <defaultProvider>
      <driver>com.mysql.jdbc.Driver</driver>       <serverURL>jdbc:mysql://111.111.111.111:PORT/OPENFIREDATABASENAME</serverURL>       <username>OPENFIREDATABASELOGINNAME</username>       <password>OPENFIREDATABASEPASSWORD</password>       <minConnections>5</minConnections>       <maxConnections>15</maxConnections>       <connectionTimeout>1.0</connectionTimeout>
    </defaultProvider>
  </database> <jdbcProvider>
<driver>com.mysql.jdbc.Driver</driver>
<connectionString>jdbc:mysql://111.111.111.111:PORT/FORUMDATABASENAME?user=FORUMDATABASELOGINNAME&amp;password=FORUMDATABASEPASSWORD</connectionString>
</jdbcProvider>
<provider>
<auth>
<className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
</auth>
<user>
<className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
</user>
</provider>
<jdbcAuthProvider>
<passwordSQL>SELECT password FROM vbpaknamuser WHERE username=?</passwordSQL>
<passwordType>md5</passwordType>
</jdbcAuthProvider>
<jdbcUserProvider>
<loaduserSQL>SELECT username, email FROM vbpaknamuser WHERE username=?</loaduserSQL>
<userCountSQL>SELECT COUNT(*) FROM vbpaknamuser</userCountSQL>
<allUsersSQL>SELECT username FROM vbpaknamuser</allusersSQL>
<searchSQL>SELECT username FROM vbpaknamuser WHERE</searchSQL>
<usernameField>username</usernameField>
<nameField>username</nameField>
<emailField>email</emailField>
</jdbcUserProvider>
  <setup>true</setup>
</jive>

Ok I am getting close. For any other noobs it really helps to download the Windows version and run your .xml file from that as you can see quickly if you have any formatting errors in it.

The current status is it is running on the server, I am able login as admin via an xmpp client. Now I am just to the auth failures. Checking again, if anyone can guess from my config file why it won’t auth my account that is on my other database please feel free to be critical of my efforts. In theory when this is done, I can log in with my account name from the joomla and forum database, and search all other users on this external database correct? I can’t yet login with my own account only the admin account for openfire, when i search I get nothing. when I try to login with my external account details, I get an Auth error in Psi. I can feel it though I am getting very close.

My new openfire.xml file is below

<?xml version="1.0" encoding="UTF-8"?> <!--
    This file stores bootstrap properties needed by Openfire.
    Property names must be in the format: "prop.name.is.blah=value"
    That will be stored as:
        <prop>
            <name>
                <is>
                    <blah>value</blah>
                </is>
            </name>
        </prop>     Most properties are stored in the Openfire database. A
    property viewer and editor is included in the admin console.
-->
<!-- root element, all properties must be under this element -->
<jive>   <adminConsole>     <!-- Disable either port by setting the value to -1 -->      <port>9090</port>      <securePort>9091</securePort>   </adminConsole>    <admin>     <!-- Use this section to define users that will have admin privileges. Below,
          you will find two ways to specify which users are admins. Admins will
          have access to the admin console (only local users) and may have also access
          to other functionalities like ad-hoc commands. -->      <!-- By default, only the user with the username "admin" can login
         to the admin console. Alternatively, you can specify a comma-delimitted
         list usernames that should be authorized to login to the admin console
         by setting the <authorizedUsernames> field below. -->      <!-- <authorizedUsernames></authorizedUsernames> -->      <!-- Comma-delimitted list of bare JIDs. The JIDs may belong to local
         or remote users. -->      <!-- <authorizedJIDs></authorizedJIDs> --> admin, igotnotime
  </admin>    <locale>en</locale>    <!-- Network settings. By default, Openfire will bind to all network interfaces.
      Alternatively, you can specify a specific network interfaces that the server
      will listen on. For example, 127.0.0.1. This setting is generally only useful
       on multi-homed servers. -->    <!--
    <network>
        <interface></interface>
    </network>
    -->    <connectionProvider>     <className>org.jivesoftware.database.EmbeddedConnectionProvider</className>   </connectionProvider>    <jdbcProvider>     <driver>com.mysql.jdbc.Driver</driver>      <connectionString>jdbc:mysql://111.111.111.111:PORT/EXTERNALDBNAME?user=EXTERNALDBUSERNAME&amp;password=EXTERNALDBPASSWORD</connectionString>   </jdbcProvider>    <provider>     <auth>       <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>     </auth>      <user>       <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>     </user>   </provider>    <jdbcAuthProvider>     <passwordSQL>SELECT password FROM vbpaknamuser WHERE username=?</passwordSQL>      <passwordType>md5</passwordType>   </jdbcAuthProvider>    <jdbcUserProvider>     <loaduserSQL>SELECT username, email FROM vbpaknamuser WHERE username=?</loaduserSQL>      <userCountSQL>SELECT COUNT(*) FROM vbpaknamuser</userCountSQL>      <allUsersSQL>SELECT username FROM vbpaknamuser</allUsersSQL>      <searchSQL>SELECT username FROM vbpaknamuser WHERE</searchSQL>      <usernameField>username</usernameField>      <nameField>username</nameField>      <emailField>email</emailField>   </jdbcUserProvider>    <setup>true</setup> </jive>

Hi,

I would change “” and use the md5 value as password to try this.

Did you enable the debug log, maybe there is something useful in it.

LG

Just tried this still no go. A couple days ago I seen the term Salt when people were discussing MD5. At that point I wasn’t even running yet, so I passed it over. However the Joomla User Table a few rows down from the md5 hash has a field labeled Salt with 3 characters in it. Each use has different 3 characters but could this be affecting anything?

I am able to login to the server (all remote now) using Psi but only as the admin so I think it is reading from the database that openfire seems to be using. I should be able to login from the account on the other database though or no? Everytime I remove that database that setup the jive, muc and pubsub tables I get the install prompts like the first time I run. So it seems this database is required. I am just lost on the two database thing. Does anyone use 2 databases? If so could you please show me the config file so I can see what i am doing wrong?

I don’t know how to enable the debug log but will look in the admin page.

Ok here is debug, on an attempt to connect using accounttest user from the other database through Psi.

2007.11.24 16:37:44 XMPPCallbackHandler: NameCallback: accounttest
               2007.11.24 16:37:44 SASLAuthentication: SaslException
               javax.security.sasl.SaslException:
               DIGEST-MD5: IO error acquiring password [Caused by java.io.IOException:
               org.jivesoftware.openfire.user.UserNotFoundException: accounttest]
               at com.sun.security.sasl.digest.DigestMD5Server.validateClientResponse(Unknown Source)
               at com.sun.security.sasl.digest.DigestMD5Server.evaluateResponse(Unknown Source)
               at org.jivesoftware.openfire.net.SASLAuthentication.handle(SASLAuthentication.java:280)
               at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:156)
               at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandler.java:132)
               at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived(AbstractIoFilterChain.java:570)
               at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
               at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
               at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
               at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimpleProtocolDecoderOutput.java:58)
               at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecFilter.java:162)
               at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(AbstractIoFilterChain.java:299)
               at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilterChain.java:53)
               at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceived(AbstractIoFilterChain.java:648)
               at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java:240)
               at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(ExecutorFilter.java:284)
               at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
               at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
               at java.lang.Thread.run(Unknown Source)
               Caused by: java.io.IOException: org.jivesoftware.openfire.user.UserNotFoundException: accounttest
               at org.jivesoftware.openfire.net.XMPPCallbackHandler.handle(XMPPCallbackHandler.java:75)

|

Hi,

does the users in the remote database contain upper char characters? This may cause this exception.

LG

Actually LG I appreciate the time and effort you spent trying to help me. I have given up on it now, but am grateful. I just decided to outsource the whole thing. I posted a job for a freelancer to just upgrade us to an LDAP server and convert our existing user database into an LDAP system. Once we are LDAP’ed up I will try again using this option. Thank you though.

Speaking of, if there is a system admin here who is reliable and capable of installing and setting up LDAP based on our Vbulletin forum userbase… LOL

Yes in fact probably half of the usernames on the table do indeed have upper case letters. Perhaps adding the table fields (chatname & chatpass) I could pull the auth data from these fields, then add the option for users of the vb forum to edit this manually in their profile? would that be a work around if both these new table fields were in plain text?

Here is my thing. I have the .xml file setup with what appears to be proper information. I set up a database just for testing. Credentials can be seen in the image if you want to try it. No matter what i do I can not seem to authorize to an external database. i hope it is something that i moronically missed but I have been trying to get it for so long with nothing for results except bad password/username in spark. In the image below I am running a local windows openfire for testing and… well you all should get it by looking at the image.

Here is the screenshot. Please tell me I missed small punctuation point somewhere.

Hi,

do you still get the same exception?

Could you try it with an external HSQLDB? I did create a very small database which works for me without problems.

LG

I know this is an old topic, however I’m having the same problem.

The problem is that Openfire cannot authenticate users against the Joomla user database since it requires the SQL query to return either a plain-text password, MD5 hash or SHA-1 of the password.

Joomla passwords are stored as the MD5 hash of the clear-text password and a concatenated seed. Since you are only given the username and nothing else, there is no way to return a clear text password, or hash of any kind since you cannot guess the password or its direct MD5 hash based on the saved MD5 hash of the seeded password.

What would be the recommened way to integrate with such a database from Openfire? I’m thinking of writing a Openfire plugin but am not sure if it should be registered as a component, IQhandler or Package Interceptor?

Thanks in advance.

BTW: if you could configure the SQL query to return true or false then it would cater for any database authentication? So would just editing OpenFire source be more viable?