Ignite Realtime

powered by Jive Software

Ignite Realtime Community Forums

MSSQL as Custom Database

Openfire Openfire Support

Paul_Sherwood December 4, 2006, 1:15pm 1

Hi I am having an issue configuring MS SQL as a custom database. I have MSSQL working well with the Wildfire created database, but as we already have our users in another database it makes sense to use what we already have.

Firstly I am finding the Custom Database Integration Guide as little confusing, so anyone that can help out there would be a great help; I have copied my wildifre.xml below, and I have copied the error from the error.log below that.

Generally running Wildfire from MSSQL is fine so I am sure that the correct drivers are where they should be, googling the errors hasnt yeilded much, as neither has much trial end error. When I first set up Wildfire, after creating the databases in MSSQL all worked fine and I could log in as admin, once changing the wildfire.xml I could no longer log in as admin, however if I remove the class references in the provider section I can log back in again.

For testing purposes I have created a simple (paulsdb) database with two columns username and password.

The environment is SQL 2000 on Windows 2003 server.

my wildfire.xml;

(ConnectionJDBC3.java:50)

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

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

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

at org.jivesoftware.wildfire.auth.JDBCAuthProvider.getPasswordValue(JDBCAuthProvid er.java:194)

at org.jivesoftware.wildfire.auth.JDBCAuthProvider.authenticate(JDBCAuthProvider.j ava:104)

at org.jivesoftware.wildfire.auth.AuthFactory.authenticate(AuthFactory.java:156)

at org.jivesoftware.wildfire.admin.login_jsp._jspService(login_jsp.java:137)

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

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

at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:428)

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:830)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

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

at org.mortbay.jetty.servlet.WebApplicationHandler$CachedChain.doFilter(WebApplica tionHandler.java:821)

at org.mortbay.jetty.servlet.WebApplicationHandler.dispatch(WebApplicationHandler. java:471)

at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:568)

at org.mortbay.http.HttpContext.handle(HttpContext.java:1530)

at org.mortbay.jetty.servlet.WebApplicationContext.handle(WebApplicationContext.ja va:633)

at org.mortbay.http.HttpContext.handle(HttpContext.java:1482)

at org.mortbay.http.HttpServer.service(HttpServer.java:909)

at org.mortbay.http.HttpConnection.service(HttpConnection.java:816)

at org.mortbay.http.HttpConnection.handleNext(HttpConnection.java:982)

at org.mortbay.http.HttpConnection.handle(HttpConnection.java:833)

at org.mortbay.http.SocketListener.handleConnection(SocketListener.java:244)

at org.mortbay.util.ThreadedServer.handle(ThreadedServer.java:357)

at org.mortbay.util.ThreadPool$PoolThread.run(ThreadPool.java:534)

Caused by: java.io.IOException: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

at net.sourceforge.jtds.jdbc.TdsCore.sendMSLoginPkt(TdsCore.java:1894)

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

… 35 more

Conor_Hayes December 4, 2006, 2:35pm 2

A quick search for your exception leads to lots of info. One link said

"According to the README.SSO file in the JDTD installation you must have

the ntlmauth.dll file in your PATH. Make sure that you do, or try to set

the java.library.path system property to the directory containing this

file, that is /SSO."

Does that make any sense to you?

In any case it’‘s almost certainly a problem with the your JDBC driver set-up. Sorry I can’'t be more helpful but unfortunately I have never used that driver.

Cheers,

Conor.

Paul_Sherwood December 4, 2006, 2:57pm 3

HI Conor

The priniciple makes sense, so I will research it. The documentation concerning custom DB leaves a bit to be desired, something I may undertake should I resolve this.

The configuration works fine from the same SQL server using the standard database, would I not expect the ‘‘missing’’ .dll to create a similar issue using that too?

Paul

Conor_Hayes December 4, 2006, 5:39pm 4

Paul,

I re-read your post, and am convinced its the driver and/or configuration. The problem occurs in trying to establish the connection to your separate database, using the SSO user supplied credentials. These are the credentials you specifed in the xml file.

Have you verified the connection string is correct, is it the same SQL server, do you have the same type of permission the the default configuration has. SQL connection strings can be different on different versions of databases.

Just another area to check.

Conor.

Paul_Sherwood December 4, 2006, 6:59pm 5

Right, since last posting I have been around the proverbial houses!!!

I am slowly getting more uses to the XML syntax, however I dont find it very inspiring…

Yes the same server, yes the same authentication, the same username and password(we are in the fortunate position to have access to quite a few servers!) . While I have been trying this I have made a couple of new observations. I am now not getting any error messages in the error log, but I cannot log in… When I put in the valid username/password the page just refreshes but no message on the login page, if I use a not valid username/password then I get the approptiate message telling me username or password incorrect!

If I remove the following lines from the wildfire.xml;

I can once again log in, without any errors, I cannot of course access the users in my MSSQL tables without the above, so I am in a bit of a dilema. Either way when I tried the above I profiled the MSSQL server and couldnt see any reference to my custom tables. I am now toying with trying this using MySQL but this whatever the results of that test the data I need is in MSSQL.

Anyone else with some more detailed info on the wildfire.xml I would appreciate it, I am making presumptions based on the info at http://www.jivesoftware.org/builds/wildfire/docs/latest/documentation/db-integra tion-guide.html but some verification or even a copy of a working custom wildfire.xml would be invaluble.

Unfortunately I have spent too much time on this already and as much as I like Wildfire, I am under pressure to find a resolution that includes Wildfire or not. Peronsally I would love to have Wildfire based solution but if basic connectivity is this much of an issue I will have a tough time convincing the powers that be!