How can you use Sql Server as an Auth provider only?

Openfire Openfire Support
1

I’ve recently setup a fresh install of Openfire 3.7.1 on fresh install of CentOS. It’s all working correctly and as epected “out of the box”. What I have been trying to accomplish is using an existing MS Sql Server database for authentication but not for user profiles or groups. I followed the guide found here http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/db-integ ration-guide.html but after restarting the openfire service it seemed to ignore the changes I made in openfire.xml regarding the custom auth provider. So, using the web admin, I changed the system property “provider.auth.className” to the correct value and then all of the sudden in the web admin I could see the other settings that I had entered in openfire.xml. I thought “that was strange” but moved on because it seemed to have the settings I wanted. I restarted the openfire service and after doing so I could no longer login to the web admin console using the “admin” user, at first I thought that was a good thing because that user wouldn’t exist in the MS Sql Server DB. However, I couldn’t login as any user, even ones that I had specified in the system property “admin.authorizedJIDs”. Sorry for the verbose “back history” but here is where I found the problem I am stuck on. In the error.log I found that it was throwing the following exception every time the openfire service started.

2012.03.21 18:26:36 org.jivesoftware.openfire.auth.JDBCAuthProvider - Exception in JDBCAuthProvider

java.sql.SQLException: The url cannot be null

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.net.XMPPCallbackHandler.handle(XMPPCallbackHandler.ja va:102)

at org.jivesoftware.openfire.sasl.SaslServerPlainImpl.evaluateResponse(SaslServerP lainImpl.java:120)

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

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

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

at org.apache.mina.common.support.AbstractIoFilterChain$TailFilter.messageReceived (AbstractIoFilterChain.java:570)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.common.IoFilterAdapter.messageReceived(IoFilterAdapter.java:80)

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.codec.support.SimpleProtocolDecoderOutput.flush(SimplePr otocolDecoderOutput.java:58)

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

at org.apache.mina.common.support.AbstractIoFilterChain.callNextMessageReceived(Ab stractIoFilterChain.java:299)

at org.apache.mina.common.support.AbstractIoFilterChain.access$1100(AbstractIoFilt erChain.java:53)

at org.apache.mina.common.support.AbstractIoFilterChain$EntryImpl$1.messageReceive d(AbstractIoFilterChain.java:648)

at org.apache.mina.filter.executor.ExecutorFilter.processEvent(ExecutorFilter.java :239)

at org.apache.mina.filter.executor.ExecutorFilter$ProcessEventsRunnable.run(Execut orFilter.java:283)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at org.apache.mina.util.NamePreservingRunnable.run(NamePreservingRunnable.java:51)

at java.lang.Thread.run(Unknown Source)

Here are the settings that I have in the OFPROPERTY table (sensative info removed)…

jdbcAuthProvider.passwordSQL = SELECT Password FROM Users WHERE Username = ?

jdbcAuthProvider.passwordType = plain

jdbcProvider.connectionString = jdbc:jtds:sqlserver://[DBSERVER]:1433/[DBNAME];user=[DBUSER];password=[DBPASS]; appName=jive

jdbcProvider.driver = net.sourceforge.jtds.jdbc.Driver

Can anyone tell me what this exception means? Or what “url” it is referring to? I have searched a decent amount on these forums and I can find people with similar problems but they are not doing exactly what i’m doing. Thanks again to anyone who can/will help me figure this out!

Darren

2

“Does anyone have any idea about this because we’re having the exact same issue?”

3

Bump. Is there really no one on the forums who has done this successfully who can help me or even point me to a more updated resource about doing this?

4

The expection means that jdbcProvider.connectionString is null. Try to add it again to openfire.xml.

Actually Openfire should be able to get this value from the database.

5

Thanks LG for that comment. I had originally put the information in openfire.xml so I assumed it would still be there. However, when I opened the file I noticed that something had changed it and the root level nodes for “jdbcProvider” and “jdbcAuthProvider” were both there but they had no child nodes. I stopped the openfire service and (don’t hate me for this) took a look at the embedded-db/openfire.script file to see if the settings were in there. They actually were in the script file correctly but I decided to try what you suggested anyway. Putting them back in the openfire.xml file got me “closer” to “working” than I’ve been before so that is good. After restarting the openfire service it did remove the settings from openfire.xml again but this time it was at least not throwing that error in the error.log. I still couldn’t login to the admin area so I double checked a few other things just to make sure. I checked the authorizedJIDs to see that I was logging in as someone who should have rights to, and I was, so everything seemed fine there. Next, I took at look at the SQL profiler to see if I could observe openfire actually making a connection to the database server at all. What I found was that if you were trying to login with a jID that wasn’t authorized it didn’t even try to query SQL to check the password (which makes perfect sense) but if you tried to login with an authorized jID the SQL server would receive a connected and a single query would be run… a seemingly useless query:

SELECT @@MAX_PRECISION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET IMPLICIT_TRANSACTIONS OFF

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 2147483647

That’s it it never ran the SQL script I had in the openfire.xml file (which is also stored in the database and visible from the “System Properties” screen). That was confusing so I thought I would check the logs again. The error.log file was clean so I enabled the debugging log and tried again. There is an exception showing up in the debug.log file:

org.jivesoftware.openfire.auth.UnauthorizedException

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

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)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1216)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:50)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:425)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:494)

at org.eclipse.jetty.server.session.SessionHandler.handle(SessionHandler.java:182)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:93 3)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:362)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:867 )

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)

at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:245)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:126)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113)

at org.eclipse.jetty.server.Server.handle(Server.java:334)

at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559)

at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.j ava:1007)

at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747)

at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209)

at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406)

at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:4 62)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436)

at java.lang.Thread.run(Unknown Source)

I’ve seen SIMILAR exceptions on these forums but the stack trace has key differences in it. For example, in my debug.log if you try to login as a user that should not have admin rights the same exception shows up but the first line is different and the stack trace is too, it reads like this:

org.jivesoftware.openfire.auth.UnauthorizedException: User ‘mystandarduser’ not allowed to login.

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

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)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1216)

at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:39)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:74)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:50)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:78)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:164)

at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1187)

at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:425)

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:119)

at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:494)

at org.eclipse.jetty.server.session.SessionHandler.handle(SessionHandler.java:182)

at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:93 3)

at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:362)

at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:867 )

at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:117)

at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandler Collection.java:245)

at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.jav a:126)

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:113)

at org.eclipse.jetty.server.Server.handle(Server.java:334)

at org.eclipse.jetty.server.HttpConnection.handleRequest(HttpConnection.java:559)

at org.eclipse.jetty.server.HttpConnection$RequestHandler.content(HttpConnection.j ava:1007)

at org.eclipse.jetty.http.HttpParser.parseNext(HttpParser.java:747)

at org.eclipse.jetty.http.HttpParser.parseAvailable(HttpParser.java:209)

at org.eclipse.jetty.server.HttpConnection.handle(HttpConnection.java:406)

at org.eclipse.jetty.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:4 62)

at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:436)

at java.lang.Thread.run(Unknown Source)

Note that the exception I’m reporting doesn’t have the same ending, it simply lists the same type of exception but no details.

Could this be a bug? Can you (or anyone) tell me what that means or what to do about it?

Message was edited by: Darren Hollick

6

I actually had a quick peek at the source code for org/jivesoftware/openfire/auth/JDBCAuthProvider.java and I can see that the error is because somewhere in the function getPasswordValue() an exception is being raised. I can’t see exactly where because the trace obscures that because that exception is caught and another is thrown outside of it. There seems to be three places in that function that can throw an exception.

The first is on line 288, this shouldn’t be it since it shouldn’t even get into that code block since I’m not trying to login with a full email address (e.g. I’m not using something like "user@dmpp-domain.org" but instead I’m using something like “user”).

The second is on line 301, this could be what is causing the problem. However, if it were I would expect to see the query hit the database when I’m watching the SQL Profiler and I’m not.

The third is on line 307, I don’t think this is it because if the excution gets into this code block then it should also be logging an error and there is nothing showing in the error log.

So, back the second possible place that an exception should be able to thrown… I’m pretty sure that my passwordSQL is never being executed against the SQL server. Do you have any idea why that would be? I have re-added that statement to openfire.xml several times and every time I start the openfire service it removes it from the xml. I can see in the embedded-db/openfire.script file that it’s being added to the database:

INSERT INTO OFPROPERTY VALUES(‘jdbcAuthProvider.passwordSQL’,‘SELECT Password FROM Users INNER JOIN Usernames ON Users.ID = Usernames.UserID WHERE Usernames.Username = ?’)

Does that provide you with any more helpful info?

7

Any update on this issue? Would love to know the answer to this.

8

Again, is there anyone else (or LG again) on here who can help with this?

9

Bump. :slight_smile:

10

Can you attach openfire.xml?

11

I’ve attached the file… I did hide the sensative information…

However, I should point out that I don’t fully get the openfire.xml functionality. I assumed that it was an easy way to get information into the openfire database but maybe I’m wrong. If I change the file and then stop and restart the openfire service it will often change openfire.xml and remove some of the settings I had added. But even in the case of the file I just posted after stop and restarting the service I can tell that it definitely doesn’t read all of those values in to the database because if you look at the raw openfire.script file after that process it still shows my old values.

Most of the time that I’ve been testing this I’ve found that changing the values in the web admin is best way to make sure the values you want are ending up in the database. That is useful right up until you change the AuthProvider and then logout because (since it isn’t working) you’re locked out… :slight_smile:

In any case, here’s my openfire.xml
openfire.xml (1072 Bytes)

12

You DO get openfire.xml functionality fully! If you look at sources of Openfire you’ll see that they migrate all your configs to database from openfire.xml each time when you start your server.

So this is not problem, you can always look at your openfire’s database global configs at “ofproperty” table.

Back to problem. I’ve implemented similar functionality(using postgres) can you try to change your connectionString to

jdbc:jtds:sqlserver://***.***.***.***:1433/MyDatabase?user=MyUser&password=M yPassword

**
**

13

I too have seen the connection string formatted like that. However I’ve also noticed that the format seems to vary slightly based on which jdbc driver you are using (and there are several drivers made for MS SQL)… I was using a format that was in the documentation for the exact driver I’m using. Nevertheless, I tried changing it as you suggested and that produces an error in the error.log file as follows:

2012.04.09 16:38:43 org.jivesoftware.openfire.auth.JDBCAuthProvider - Exception in JDBCAuthProvider

java.sql.SQLException: Login failed for user ‘MyUser&amp’.

at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)

at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)

at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)

at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:602)

at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:344)

at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50)

at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182)

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

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

…

That makes me assume that it was correct before. In case you didn’t catch this from my earlier post, when I have the connection string the way I had it I can see the openfire server making a connection using the username and password I specified to my SQL server but instead of running any kind of useful query it simply runs…

SELECT @@MAX_PRECISION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SET IMPLICIT_TRANSACTIONS OFF

SET QUOTED_IDENTIFIER ON

SET TEXTSIZE 2147483647

That’s it! It never tries to run the tsql statement I have setup in openfire. It doesn’t produce any errors in the error log either. Which is where I get lost. I’ve tried looking at the source code as you can see in my earlier post but it still doesn’t make sense to me.

Thanks again for your time, do you have any other thoughts of things I might try?