Help me! MySql Custom User Table Integration

Openfire Openfire Support
#1

Hello!

I’m trying to customize the table of users login Openfire, read the following topic,http://community.igniterealtime.org/message/161839 but I can notmake a user from another database table data to be acceptedlog-in the spark. In my case the integration is with the users table,moodle, so that users would like moodle could connect to my xmppserver, below is my config.xml

<?xml version="1.0" encoding="UTF-8"?>
<jive> <adminConsole> <port>9090</port>  <securePort>9091</securePort> </adminConsole>  <locale>pt_BR</locale>  <connectionProvider> <className>org.jivesoftware.database.DefaultConnectionProvider</className> </connectionProvider>  <database> <defaultProvider> <driver>com.mysql.jdbc.Driver</driver>  <serverURL>jdbc:mysql://localhost:3306/open</serverURL>  <username>root</username>  <password></password>  <minConnections>5</minConnections>  <maxConnections>15</maxConnections>  <connectionTimeout>1.0</connectionTimeout> </defaultProvider> </database>  <setup>true</setup>  <jdbcProvider> <driver>com.mysql.jdbc.Driver</driver>  <connectionString>jdbc:mysql://localhost/moodle?user=root&amp;password=</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 MD5(CONCAT(password,'n0royB`BlUvI2Gc[@Udnq%4}A,uV>!')) FROM mdl_user WHERE username=?</passwordSQL>  <passwordType>plain</passwordType> </jdbcAuthProvider>  <jdbcUserProvider> <loadUserSQL>SELECT CONCAT(firstname,' ',lastname),email FROM mdl_user WHERE username=?</loadUserSQL>  <userCountSQL>SELECT COUNT(*) FROM mdl_user</userCountSQL>  <allUsersSQL>SELECT username FROM mdl_user</allUsersSQL>  <searchSQL>SELECT username FROM mdl_user WHERE</searchSQL>  <usernameField>username</usernameField>  <nameField>CONCAT(firstname,' ',lastname)</nameField>  <emailField>email</emailField> </jdbcUserProvider>  <admin> <authorizedUsernames>admin,testuser</authorizedUsernames>
</admin> </jive>

When trying to log it appears the following message: “Username or password is incorrect”

could someone help me?

now thanks to all!

#2

Do not use XML file…

look at my posts and u will find the answer

#3

Hello! I looked into your posts and followed what you wereindicating, using the table ofproprety entering the values, but stilldid not work. Below is how my table:

INSERT INTO ofproperty (name, propValue) VALUES

(’ jdbcUserProvider.loadUserSQL’, ‘SELECT CONCAT(firstname,’’ ‘’,lastname),email FROM mdl_user WHERE username=?’),

(‘admin.authorizedJIDs’, ‘admin@camilla-pc,testuser@camilla-pc’),

(‘jdbcAuthProvider.passwordSQL’, ‘SELECT MD5(CONCAT(password,’‘n0royB`BlUvI2Gc[@Udnq%4}A,uV>!’’)) FROM mdl_user WHERE username=?’),

(‘jdbcAuthProvider.passwordType’, ‘plain’),

(‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost/moodle?user=root&password=’),

(‘jdbcProvider.driver’, ‘com.mysql.jdbc.Driver’),

(‘jdbcUserProvider.allUsersSQL’, ‘SELECT username FROM mdl_user’),

(‘jdbcUserProvider.emailField’, ‘email’),

(‘jdbcUserProvider.loadUserSQL’, ‘SELECT CONCAT(firstname,’’ ‘’,lastname),email FROM mdl_user WHERE username=?’),

(‘jdbcUserProvider.nameField’, ‘CONCAT(firstname,’’ ‘’,lastname)’),

(‘jdbcUserProvider.searchSQL’, ‘SELECT username FROM mdl_user WHERE’),

(‘jdbcUserProvider.userCountSQL’, ‘SELECT COUNT(*) FROM mdl_user’),

(‘jdbcUserProvider.usernameField’, ‘username’),

(‘passwordKey’, ‘8F0df078P26V5Kf’),

(‘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’, ‘1317305763394’),

(‘xmpp.auth.anonymous’, ‘true’),

(‘xmpp.domain’, ‘camilla-pc’),

(‘xmpp.session.conflict-limit’, ‘0’),

(‘xmpp.socket.ssl.active’, ‘true’);

#4

hi

instead of

(‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost/moodle?user=root&password=’),

use

‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost/moodle?user=root&password=’),

i suggest you to not use root account (not mandatory…you can use any other account that has equal privileges for the schema)

#5

changed, but still no login.

#6

a) Have u tried logging into admin console of openfire with the user “admin”, “testuser” as specified in

(‘admin.authorizedJIDs’, ‘admin@camilla-pc,testuser@camilla-pc’),

b) Normal users has to be logged in through XMPP Client…

c) First confirm whether you are able to log in into admin console of openfire with above users