powered by Jive Software

Custom auth database

Hi

i’m trying to use my own database to authenticate users.

This is my user database(the database name is “frisr”):

CREATE TABLE IF NOT EXISTS `salons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL,
  `phone` int(11) DEFAULT NULL,
  `connected` int(11) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

And this is what i’ve inserted into the ofProperty:

--
-- Table structure for table `ofProperty`
-- CREATE TABLE IF NOT EXISTS `ofProperty` (
  `name` varchar(100) NOT NULL,
  `propValue` text NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8; --
-- Dumping data for table `ofProperty`
-- INSERT INTO `ofProperty` (`name`, `propValue`) VALUES
('passwordKey', 'XXXX'),
('xmpp.socket.ssl.active', 'true'),
('xmpp.domain', 'frisr.dk'),
('provider.admin.className', 'org.jivesoftware.openfire.admin.DefaultAdminProvider'),
('provider.lockout.className', 'org.jivesoftware.openfire.lockout.DefaultLockOutProvider'),
('provider.user.className', 'org.jivesoftware.openfire.user.DefaultUserProvider'),
('provider.auth.className', 'org.jivesoftware.openfire.auth.JDBCAuthProvider'),
('xmpp.auth.anonymous', 'true'),
('provider.group.className', 'org.jivesoftware.openfire.group.DefaultGroupProvider'),
('provider.vcard.className', 'org.jivesoftware.openfire.vcard.DefaultVCardProvider'),
('provider.securityAudit.className', 'org.jivesoftware.openfire.security.DefaultSecurityAuditProvider'),
('xmpp.session.conflict-limit', '0'),
('update.lastCheck', '1309985119064'),
('plugin.presence.public', 'true'),
('plugin.presence.unavailable.status', 'Unavailable'),
('register.inband', 'false'),
('register.password', 'false'),
('locale.timeZone', 'Europe/Berlin'),
('xmpp.offline.type', 'store_and_bounce'),
('xmpp.offline.quota', '1048576'),
('jdbcAuthProvider.passwordSQL', 'SELECT password FROM salons WHERE username=?'),
('jdbcProvider.connectionString', 'jdbc:mysql://localhost/frisr?username=USERNAME&password=PASSWORD'),
('jdbcProvider.driver', 'com.mysql.jdbc.Driver'),
('jdbcAuthProvider.passwordType', 'plain');

And this is how i edit my openfire.xml:

<port>9090</port>
    <securePort>9091</securePort>
  </adminConsole>
  <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>
    <!-- DefaultConnectionProvider</className> -->
  </connectionProvider>
  <database>
    <defaultProvider>
      <driver>com.mysql.jdbc.Driver</driver>
      <serverURL>jdbc:mysql://localhost:3306/jabber</serverURL>
      <username>jabber</username>
      <password>mangePenge</password>                                <testSQL>select 1</testSQL>
      <testBeforeUse>true</testBeforeUse>
      <testAfterUse>true</testAfterUse>
      <minConnections>5</minConnections>
      <maxConnections>25</maxConnections>
      <connectionTimeout>1.0</connectionTimeout>
    </defaultProvider>
  </database>
  <setup>true</setup> <jdbcProvider>
    <driver>com.mysql.jdbc.Driver</driver>
    <connectionString>jdbc:mysql://localhost/frisr?user=USERNAME&amp;password=PASSWORD</connectionString>
  </jdbcProvider>
  <provider>
    <auth>
      <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    </auth>
  </provider>
  <jdbcAuthProvider>
     <passwordSQL>SELECT password FROM salons WHERE username=?</passwordSQL>
     <passwordType>plain</passwordType>
   </jdbcAuthProvider>
</jive>

So right now, if i restart openfire server it will remove what i’ve put into the openfire.xml file and i get this error when trying to log in to adminpanel of openfire:

Login failed: make sure your username and password are correct and that you’re an admin or moderator

thx, Daniel

Actually i’ve just checked error.log and get this:

java.sql.SQLException: Access denied for user ‘’@‘localhost’ (using password: NO)

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)

at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)

at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)

at com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)

at com.mysql.jdbc.Connection.(Connection.java:1555)

at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)

at java.sql.DriverManager.getConnection(Unknown Source)

at java.sql.DriverManager.getConnection(Unknown Source)

at org.jivesoftware.openfire.auth.JDBCAuthProvider.getConnection(JDBCAuthProvider. java:264)

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

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

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

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

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

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

at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:530)

Why does it try to login without username and with no password?