Ok, problem solved!!!
For those of you who are looking to connect to a remote MySQL database with different fields etc… this is how i done it. First of all i will show you the config file then explain where i was going wrong and the things that confused me 
in /opt/openfire/conf/server.xml
<provider>
<auth>
<className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className&g t;
</auth>
<user>
<className>org.jivesoftware.openfire.user.JDBCUserProvider</className&g t;
</user>
</provider>
<jdbcProvider>
<driver>com.mysql.jdbc.Driver</driver>
<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>
</jdbcProvider>
<jdbcAuthProvider>
<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>
<passwordType>plain</passwordType>
</jdbcAuthProvider>
<jdbcUserProvider>
<loadUserSQL>SELECT first_name,email_address FROM users WHERE account=?</loadUserSQL>
<userCountSQL>SELECT COUNT(*) FROM users</userCountSQL>
<allUsersSQL>SELECT account FROM users</allUsersSQL>
<searchSQL>SELECT account FROM users WHERE</searchSQL>
<usernameField>account</usernameField>
<nameField>first_name</nameField>
<emailField>email_address</emailField>
</jdbcUserProvider>
Ok, the first section: <provider> you must have both <auth> and <user> in this section, its well documented and most likely you already have it.
The next and important section:
<jdbcProvider>
<driver>com.mysql.jdbc.Driver</driver>
<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>
</jdbcProvider>
Now this confused me a little bit, i didn;t find any hard reference to this being essential i was always changing the values further in the config file like this:
<database>
<defaultProvider>
<driver>com.mysql.jdbc.Driver</driver>
<serverURL>jdbc:mysql://ip.ip.ip.ip:3306/netusers</serverURL>
<username>remoteadmin</username>
<password>123456</password>
<minConnections>5</minConnections>
<maxConnections>15</maxConnections>
<connectionTimeout>1.0</connectionTimeout>
</defaultProvider>
</database>
But actually this seems to be only for the default database from what i can make out, although, intrestingly when i tried to connect through spark and the parameters were correct i was getting invalid username/password but when i made then i deliberatley changed details so they were wrong and then i was getting “unable to connect”, so clearly they were being used. So you must have:
<jdbcProvider>
<driver>com.mysql.jdbc.Driver</driver>
<connectionString>jdbc:mysql://ip.ip.ip.ip/netusers?user=remoteadmin&passw ord=123456</connectionString>
</jdbcProvider>
For your connection and don’t forget by default the section between the “user” and “password” only has “&” (what i mean by default is that is what seems to be shown on the support pages) and it must be “& a m p ;” ( i have but spaces in between the & and ; becuase the forum keeps removing it, just so you know there are no spaces! ) with the semicolon!!
Ok, moving on…
<jdbcAuthProvider>
<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>
<passwordType>plain</passwordType>
</jdbcAuthProvider>
This is the query openfire will run against the table to pick up the username or in my case the account number. Some pages suggest that it should be left at SELECT password FROM user_account WHERE username=? i might have read it wrong on some posts i was reading but nothing indicated that i should change this line although it made sense to me, so i did and presto !http://www.igniterealtime.org/community/images/emoticons/wink.gif!
Ok finally…
<jdbcUserProvider>
<loadUserSQL>SELECT first_name,email_address FROM users WHERE account=?</loadUserSQL>
<userCountSQL>SELECT COUNT(*) FROM users</userCountSQL>
<allUsersSQL>SELECT account FROM users</allUsersSQL>
<searchSQL>SELECT account FROM users WHERE</searchSQL>
<usernameField>account</usernameField>
<nameField>first_name</nameField>
<emailField>email_address</emailField>
</jdbcUserProvider>
Here is pretty self explanitory, once you have setup the above setting jdbcUserProvider is easy just enter the fileds accordingly. So “users” is the table that my users are in so after FROM it should be you table name ( in my case “users” ), and everything prior to FROM is the field names and in my case first_name and email_address.
Before you scream shouting your using plain password how insecure is that!! i just wanted to get the thing working, by default all passwords are stored in MD5 and i have another issue realting to that but my guess is this will solved very easily by changing:
<passwordType>plain</passwordType>
to
<passwordType>md5</passwordType>
and changing my query string from:
<passwordSQL>SELECT decrypted_password FROM users WHERE account=?</passwordSQL>
to
<passwordSQL>SELECT encrypted_password FROM users WHERE account=?</passwordSQL>
When trying to setup this up i had two errors:
org.jivesoftware.util.JiveGlobals.loadSetupProperties(JiveGlobals.java:774)
java.io.IOException: Error on line 49 of document : The reference to entity “password” must end with the ‘;’ delimiter. Nested exception: The reference to entity “password” must end with the ‘;’ delimiter.
and
org.jivesoftware.openfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvide r.java:254) Exception in JDBCAuthProvider
java.sql.SQLException: The url cannot be null
by following the about this solved my problem. Ive yet to login as admin, but i assume that by transferring the admin account accross to the remote server i should be ok.
Good Luck - I hope you don’t mind the lengthy post admins
