MS SQL 2000 connection problem

Openfire Openfire Support
1

Hi all-

I’‘ve searched through and tried some of the other solutions in the forums, but I’'m not having any luck. Messenger and SQL server are on the same machine (im.domain.com)

I’'m re-installing messenger with the SQL DB support instead of the embedded DB.

I’'ve done the following so far:

1 - Created a DB called jive.

2 - Installed the MS JDBC driver and copied the required files to the Messgenger/lib

directory

3 - Run the .sql script to create the tables.

4 - Selected MSSQL as the DB type in the setup.

Setup info is:

JDBC Drive Class: com.microsoft.jdbc.sqlserver.SQLServerDriver

DB URL: jdbc:microsoft:sqlserver://im.domain.com:1433;databasename=jive

User: Domain\adminUser (i’'ve tried it without the ‘‘domain’’ as well.) User is the dbo

for the jive DB

Connections: 5/50

Timeout: 1.0

Thanks for the help!!

Joe

Here’'s my error log:

2005.12.07 11:12:16 [org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 0 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Error establishing socket.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 1 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Error establishing socket.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 2 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Error establishing socket.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSConnection.(ConnectionPool.java:120)

at org.jivesoftware.database.DefaultConnectionProvider.start(DefaultConnectionProv ider.java:83)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:330)

at org.jivesoftware.messenger.admin.setup.setup_002ddatasource_002dstandard_jsp._j spService(setup_002ddatasource_002dstandard_jsp.java:194)

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

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

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

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

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2005.12.07 11:12:48 [org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConne ctionProvider.java:68)

] Warning: DbConnectionDefaultPool.getConnection() was called before the internal pool has been initialized.

2005.12.07 11:12:48 [org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnecti onManager.java:348)

]

java.lang.NullPointerException

at org.jivesoftware.database.DbConnectionManager.setMetaData(DbConnectionManager.j ava:507)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:335)

at org.jivesoftware.messenger.admin.setup.setup_002ddatasource_002dstandard_jsp._j spService(setup_002ddatasource_002dstandard_jsp.java:194)

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

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

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

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

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2005.12.07 11:12:48 [org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConne ctionProvider.java:68)

] Warning: DbConnectionDefaultPool.getConnection() was called before the internal pool has been initialized.

2005.12.07 11:12:48 [org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManage r.java:107)

] WARNING: ConnectionManager.getConnection() failed to obtain a connection.

2

Joe, have you rebooted since you setup the dbase info in the admin console?

That was an issue I ran across.

Hope that helps…

PM.

3

PM-

I haven’‘t rebooted, but I did restart my SQL services. I’'ll reboot and see if it helps.

Thanks!

joe

4

So much for that… No change after a reboot

5

Joe,

I was having the exact same errors but once i rebooted the box the DB was working just fine afterwards. I thought that would help. sorry.

I’‘m sure you’‘ve tried this but I’‘m just throwing things out there to see what sticks… Have you tried putting in the IP of the DB server instead of the FQDN? It doesn’‘t sound like a rights issue but have you tried logging in with the sa account to see if that ID works? Hope I’'ve helped.

PM.

6

PM-

No need to appologize, it was a good thought

I did try the IP instead of the FQDN, and I just tried the SA account with no joy.

7

Joe,

Maybe you could post your config file so we can take a look to make sure the syntax is all correct?

PM.

8

Joe,

instead of using a domain account to connect to sql, why don’'t you create a SQL account and give it admin access to the Jive database and use it the sql connect string in jive config file to see if it connects.

9

PM-

here’‘s my current config file using SQL’'s ‘‘sa’’ account for DB access.

10

SupaXStarz-

I tried the SA user that PM suggested, and it didn’‘t work. It’‘s like the JDBC connector’'s not able to get to the DB in order to make the connection.

11

Joe,

in your config file make sure you close the org.jivesoftware.database.DefaultConnectionProvider</className &g t;

also is the sql server on the same machine as jive? can you ping im.domain.com address as specified in your config file from the Jive Server?

Can you connect to the Jive database using the same logon credentials and server name im.domain.com from SQL Query Analyzer?

Are all three files: (mssqlserver.jar, msutil.jar, msbase.jar) in the lib directory of Jive Messenger?

12

Joe,

in your config file make sure you close the

tag properly here:

org.jivesoftware.database.DefaultConnectio

Provider</className &g t;

It must have copied oddly… In the XML file it is closed correctly.

also is the sql server on the same machine as jive?

Yes, both are on the same machine (windows XP Pro sp2; this is my machine)

can you ping im.domain.com address as specified in

your config file from the Jive Server?

Yes

Can you connect to the Jive database using the same

logon credentials and server name im.domain.com from

SQL Query Analyzer?

No[/b]. I tried various combinations (im.domain.com, im, localhost, 192.168.1.59,127.0.0.1) with no luck. I also tried putting the DB on another server and couldn’'t connect to it.

I could connect to with my domain credentials but no the SQL login ‘‘jive’’ that I created.

I discovered that I had not turned on TCP/IP and NamedPipes in the network config of the server. I enabled both protocols, and in Query Analyzer I can connect using 127.0.0.1 and 192.168.1.59 but not the FQDN. I tried the IP and localhost IP in the setup, but neither worked.

Are all three files: (mssqlserver.jar, msutil.jar,

msbase.jar) in the lib directory of Jive Messenger?

Yes

Here’'s my current config[/u]:

Here are the contents of the error log[/u]:

2005.12.09 08:49:21 [org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 0 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)

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

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

at org.jivesoftware.database.ConnectionPool.createCon(ConnectionPool.java:437)

at org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 1 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)

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

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

at org.jivesoftware.database.ConnectionPool.createCon(ConnectionPool.java:437)

at org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:108)

] Failed to create new connections on startup. Attempt 2 of 3

java.sql.SQLException: [SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL.

at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)

at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)

at com.microsoft.jdbc.base.BaseDriver.connect(Unknown Source)

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

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

at org.jivesoftware.database.ConnectionPool.createCon(ConnectionPool.java:437)

at org.jivesoftware.database.ConnectionPool.(ConnectionPool.java:120)

at org.jivesoftware.database.DefaultConnectionProvider.start(DefaultConnectionProv ider.java:83)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:330)

at org.jivesoftware.messenger.admin.setup.setup_002ddatasource_002dstandard_jsp._j spService(setup_002ddatasource_002dstandard_jsp.java:194)

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

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

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

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

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2005.12.09 08:49:51 [org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConne ctionProvider.java:68)

] Warning: DbConnectionDefaultPool.getConnection() was called before the internal pool has been initialized.

2005.12.09 08:49:51 [org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnecti onManager.java:348)

]

java.lang.NullPointerException

at org.jivesoftware.database.DbConnectionManager.setMetaData(DbConnectionManager.j ava:507)

at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:335)

at org.jivesoftware.messenger.admin.setup.setup_002ddatasource_002dstandard_jsp._j spService(setup_002ddatasource_002dstandard_jsp.java:194)

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

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

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

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

at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:11 8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2005.12.09 08:49:51 [org.jivesoftware.database.DefaultConnectionProvider.getConnection(DefaultConne ctionProvider.java:68)

] Warning: DbConnectionDefaultPool.getConnection() was called before the internal pool has been initialized.

2005.12.09 08:49:51 [org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManage r.java:107)

] WARNING: ConnectionManager.getConnection() failed to obtain a connection.

Thanks!

Joe

13

Found the problem–

I did a search on ‘‘jdbc connection string + sql’’ and saw in the first hit that the URL needs to be like this:

jdbc:microsoft:sqlserver://192.168.1.59:1433;databasename=jive

not like this:

jdbc:microsoft:sqlserver://192.168.1.59:1433/databasename=jive

Note the semicolon instead of a slash after the port

The only thing I’‘m having issues with now is the admin email, but I’'ll save that for another show.

Thanks for your help gang!!!

14

hello sir…

iam having the same problem can u help me

iam online in vathanasegaran@gmail.com

regards

Vathan