Login Problems with Custom Database Integration Guide in OpenFire

Openfire Openfire Dev
1

I followed the solution below to try to integrate my custom Oracle database.

Now I am able to login to Admin Console using my custom admin account & password from by my custom table, and those custom users from my custom table can be shown on the User Summary page.

However, when I try to login the Client (e.g. Spark), login is failed.

In the Debug log, there is an exception which said “No stored key for user”:

2016.09.26 09:32:03 org.jivesoftware.openfire.net.SASLAuthentication - SASLAuthentication: SaslException

javax.security.sasl.SaslException: No stored key for user ‘imuser1’

at org.jivesoftware.openfire.sasl.ScramSha1SaslServer.generateServerFinalMessage(S cramSha1SaslServer.java:194)

at org.jivesoftware.openfire.sasl.ScramSha1SaslServer.evaluateResponse(ScramSha1Sa slServer.java:117)

at org.jivesoftware.openfire.net.SASLAuthentication.handle(SASLAuthentication.java :357)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:186)

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:181)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$TailFilter.messageReceive d(DefaultIoFilterChain.java:690)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFilterAdapte r.java:109)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.filter.codec.ProtocolCodecFilter$ProtocolDecoderOutputImpl.flus h(ProtocolCodecFilter.java:407)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:236)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.core.filterchain.IoFilterEvent.fire(IoFilterEvent.java:74)

at org.apache.mina.core.session.IoEvent.run(IoEvent.java:63)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTask(Ordere dThreadPoolExecutor.java:769)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTasks(Order edThreadPoolExecutor.java:761)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.run(OrderedThr eadPoolExecutor.java:703)

at java.lang.Thread.run(Thread.java:745)

There is also an exception shown in Warn log:

2016.09.26 09:32:03 org.jivesoftware.openfire.sasl.ScramSha1SaslServer - Exception in SCRAM.getSalt():

java.lang.UnsupportedOperationException

at org.jivesoftware.openfire.auth.JDBCAuthProvider.setPassword(JDBCAuthProvider.ja va:341)

at org.jivesoftware.openfire.auth.AuthFactory.setPassword(AuthFactory.java:197)

at org.jivesoftware.openfire.sasl.ScramSha1SaslServer.getSalt(ScramSha1SaslServer. java:327)

at org.jivesoftware.openfire.sasl.ScramSha1SaslServer.generateServerFirstMessage(S cramSha1SaslServer.java:161)

at org.jivesoftware.openfire.sasl.ScramSha1SaslServer.evaluateResponse(ScramSha1Sa slServer.java:113)

at org.jivesoftware.openfire.net.SASLAuthentication.handle(SASLAuthentication.java :300)

at org.jivesoftware.openfire.net.StanzaHandler.process(StanzaHandler.java:183)

at org.jivesoftware.openfire.nio.ConnectionHandler.messageReceived(ConnectionHandl er.java:181)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$TailFilter.messageReceive d(DefaultIoFilterChain.java:690)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.core.filterchain.IoFilterAdapter.messageReceived(IoFilterAdapte r.java:109)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.filter.codec.ProtocolCodecFilter$ProtocolDecoderOutputImpl.flus h(ProtocolCodecFilter.java:407)

at org.apache.mina.filter.codec.ProtocolCodecFilter.messageReceived(ProtocolCodecF ilter.java:236)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.callNextMessageReceived(D efaultIoFilterChain.java:417)

at org.apache.mina.core.filterchain.DefaultIoFilterChain.access$1200(DefaultIoFilt erChain.java:47)

at org.apache.mina.core.filterchain.DefaultIoFilterChain$EntryImpl$1.messageReceiv ed(DefaultIoFilterChain.java:765)

at org.apache.mina.core.filterchain.IoFilterEvent.fire(IoFilterEvent.java:74)

at org.apache.mina.core.session.IoEvent.run(IoEvent.java:63)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTask(Ordere dThreadPoolExecutor.java:769)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.runTasks(Order edThreadPoolExecutor.java:761)

at org.apache.mina.filter.executor.OrderedThreadPoolExecutor$Worker.run(OrderedThr eadPoolExecutor.java:703)

at java.lang.Thread.run(Thread.java:745)

My custom user table is called PS_FT_IM_ACCOUNT which only contains 4 fields: IM_USERNAME, PASSWORD, IM_NAME, EMAIL

I just used the the plain as the password type in the jdbcAuthProvider properties.

jdbcAuthProvider.passwordSQL
SELECT PASSWORD FROM PS_FT_IM_ACCOUNT WHERE IM_USERNAME=?
jdbcAuthProvider.passwordType
plain

Does anyone know what the problem is ?

Please help, Thanks!!!

2

Please help.

Does anyone know ?

There is also a special message in Debug log

“2016.09.27 16:10:53 org.jivesoftware.openfire.sasl.ScramSha1SaslServer - No salt found, so resetting password.”

Actually there are some fields (STOREDKEY, SERVERKEY, SALT etc.) in my OFUSER table. I am using the openfire version 4.0.3.

Those fields are even not specified on the page Openfire: Database Schema Guide .

Those fields are new in my version??

Do I have to add any properties to handle it for Custom Database Integration?

Here is my OFPROPERTY table:

admin.authorizedJIDs
admin@XXXXXXXX
admin.authorizedUsernames
admin
adminConsole.port
9090
adminConsole.securePort
9091
connectionProvider.className
org.jivesoftware.database.DefaultConnectionProvider
conversation.idleTime
10
conversation.maxAge
60
conversation.maxRetrievable
60
conversation.maxTime
60
conversation.messageArchiving
true
conversation.metadataArchiving
true
conversation.roomArchiving
true
database.defaultProvider.connectionTimeout
1.0
database.defaultProvider.driver
oracle.jdbc.driver.OracleDriver
database.defaultProvider.maxConnections
25
database.defaultProvider.minConnections
5
database.defaultProvider.password
XXXXXXXXXXXXXXXXXXXXXXXXXXX
database.defaultProvider.serverURL
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXXXXXXX)(PORT=1521))(LOAD_BALANCE=ON)(FAILOVER=ON)(CONNECT_DATA=(SERVICE _NAME=XXXXXXX.world)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY =1))))
database.defaultProvider.testAfterUse
false
database.defaultProvider.testBeforeUse
false
database.defaultProvider.testSQL
select 1 from dual
database.defaultProvider.username
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
httpbind.CORS.domains
*
httpbind.CORS.enabled
true
httpbind.enabled
true
httpbind.forwarded.enabled
false
jdbcAuthProvider.passwordSQL
SELECT PASSWORD FROM PS_FT_IM_ACCOUNT WHERE IM_USERNAME=?
jdbcAuthProvider.passwordType
plain
jdbcAuthProvider.useConnectionProvider
true
jdbcProvider.connectionString
jdbc:oracle:thin:XXXX/XXXX@(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXXXXX)(PORT=1521))(LOAD_BALANCE=ON)(FAILOVER=ON)(CONNECT_DATA=(SERVICE_N AME=XXXXXXXX.world)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY= 1))))
jdbcProvider.driver
oracle.jdbc.driver.OracleDriver
jdbcUserProvider.allUsersSQL
SELECT IM_USERNAME FROM PS_FT_IM_ACCOUNT
jdbcUserProvider.emailField
EMAIL
jdbcUserProvider.loadUserSQL
SELECT IM_NAME,EMAIL FROM PS_FT_IM_ACCOUNT WHERE IM_USERNAME=?
jdbcUserProvider.nameField
IM_NAME
jdbcUserProvider.searchSQL
SELECT IM_USERNAME FROM PS_FT_IM_ACCOUNT WHERE
jdbcUserProvider.useConnectionProvider
true
jdbcUserProvider.userCountSQL
SELECT COUNT(*) FROM PS_FT_IM_ACCOUNT
jdbcUserProvider.usernameField
IM_USERNAME
locale
en
log.debug.enabled
true
passwordKey
1PA1h8LplO671rP
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
register.inband
false
register.password
false
sasl.mechs
PLAIN
sasl.scram-sha-1.iteration-count
4096
setup
true
stream.management.active
true
stream.management.requestFrequency
5
update.lastCheck
1474796121189
user.usePlainPassword
true
xmpp.auth.anonymous
false
xmpp.client.idle
360000
xmpp.client.idle.ping
true
xmpp.domain
XXXXXXXXXXXXXX
xmpp.httpbind.scriptSyntax.enabled
true
xmpp.offline.quota
102400
xmpp.offline.type
store
xmpp.session.conflict-limit
0
xmpp.socket.ssl.active
true

3

SCRAM is a relatively new addition to Openfire. Although an effort was made to make this backwards compatible, something went wrong. I’ve logged this as https://issues.igniterealtime.org/browse/OF-1195

You might work around this by allowing your auth provider to update passwords, but I am worried that this will generate passwords that are not usable by other systems than Openfire, when such systems exist and make use of the same database. Be careful!

4

Thanks for your reply @Guus der Kinderen,

I added the following two properties:

jdbcAuthProvider.passwordSQL
SELECT PASSWORD FROM PS_FT_IM_ACCOUNT WHERE IM_USERNAME=?
jdbcAuthProvider.setPasswordSQL
UPDATE PS_FT_IM_ACCOUNT SET password=? WHERE IM_USERNAME=?

When I try to login Spark, no “UnsupportedOperationException” in Warn log and no “SaslException: No stored key for user” in Debug log now.

But I still cannot login Spark, and in the Info log, there is an another unexpected exception related to SASL.

org.jivesoftware.openfire.net.SASLAuthentication - User Login Failed. Unexpected exception while evaluating SASL response.

Do you have any idea??

5

On the other hand, do you have any ideas which version of openfire has no problem for Custom Database Integration?

I would like to try older version to work around this.

Thanks a lot.