MS SQL database connection issue

Openfire Openfire Support
1

Hello all,

I’'m have troubles in connecting the Jive Messenger to the database. The Jive Messenger is installed in first server and the MS SQL 2000 database in second server. I created a new database for the Jive in the second and user login account, password. When I tried to set up the Jive database following the instructions, I got the following error messages,

"The Jive Messenger database schema does not appear to be installed. Follow the installation guide to fix this error. "

Then I followed the Installation Guide

"Open the Query Analyser and connect to the server.

Select the database you want to use for Jive Messenger from the DB drop down (the one you created in step 1 if you’'re using a new database).

Open the messenger_sqlserver.sql file. "

But could not figure out how to run the messenger_sqlserver.sql , I mean, from the first server, or the second one. The first server doesn’‘t have the SQL tools and the second server doesn’'t have the Jive Messager installed.

Can anyone please give me a hand with this issue?

Thanks,

Frank

2

Frank,

Basically, you need to create a database for Jive Messenger to connect to and install all the correct tables. The SQL script is what you need to use to create the tables. So, grab it from the resources/database directory and move it to your database server to run.

Hope that helps!

-Matt

3

Hi Frank,

Just to expand slightly on what Matt said, when you’‘re logged into the Jive database from Query Analyzer you can load the messenger_sqlserver.sql script by going to the File menu, selecting the Open option, point to the script file and clicking on the Ok button (at least I think it says Ok, I don’'t have Query Analyzer in front of me right now).

-Ryan

4

Thanks Matt and Ryan for your helps. I did what you told and copied the file from the first server where the Jive M locates to the second one where the SQL database locates, opened and run it by F5. Then I went back the first server and tried to connect to the second one. Unfortunately, I still got the error message as below.

"The Jive Messenger database schema does not appear to be installed. Follow the installation guide to fix this error. "

BTW, does it matter that where I placed the file to run it?

Any clues?

Frank

5

Hi Frank,

If the sql creation script ran fine it could be that you aren’‘t supplying Messenger with the correct database info during the setup process. Be sure you’'ve selected the proper driver and entered the server address as specified in the url=http://www.jivesoftware.org/builds/messenger/docs/latest/documentation/datab ase.html#sqlserver2000Database Installation Guide[/url].

BTW, does it matter that where I placed the file to run it?

No, once the script file has been imported you no longer need it.

Thanks,

Ryan

6

Hi Ryan,

Here are my Jive database setings.

Database Driver Preset: .MSSQL

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

Database URL: jdbc:microsoft:sqlserver://192.175.0.61:1433;databasename=messenger

Also I input the correct username and password but couldn’‘t figure out what’'s wrong.

Frank

7

Hi Frank,

Strange… Could you look to see if you have an error.log file in your log directory? If you do, could you post the contents?

Thanks,

Ryan

8

Here it is.

===============================================================

2005.08.17 11:54:00 [org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnecti onManager.java:275)

] Database upgrade failed. Please manually upgrade your database.

java.sql.SQLException: [SQLServer 2000 Driver for JDBC][SQLServer]User does not have permission to perform this operation on table ‘‘jiveGroup’’.

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

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

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSExecuteRequest.processReplyToken(Unknown Source)

at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)

at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.postImplExecute(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)

at com.microsoft.jdbc.base.BaseStatement.execute(Unknown Source)

at org.jivesoftware.database.DbConnectionManager.upgradeDatabase(DbConnectionManag er.java:670)

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

at org.jivesoftware.messenger.admin.setup_002ddatasource_002dstandard_jsp._jspServ ice(setup_002ddatasource_002dstandard_jsp.java:269)

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 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:78)

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)

9

Ah, there’‘s a (the?) problem. Does the user that you’'re trying to use to connect Messenger to SQLServer have full permissions (read/write/update) on all the tables in the jive database?

Thanks,

Ryan

10

Thanks Ryan. It seems that I finally got connected with the database hosted in another server. Whne I logged in, The Jive Admin Console asks my username and password. What should they be?

Frank

11

Hi Frank,

Good to hear you finally got things connected. The username is going to be admin and the password is whatever you told it to be during the setup process.

Hope that helps,

Ryan

12

Hi Ryan,

Now I can create user (JID) for testing. Can you tell me what are the full JID if I create the user name, say “device”? Or where to find out the info regarding my server DNS? (device@[server name].com)

13

Hi Frank,

Your server/Messenger name is whatever you specified it to be during the setup process. You can view and edit the name via the Admin Console in Server -> Server Manager -> Server Settings.

Hope that helps,

Ryan

14

Hi Ryan,

Thanks for your info. However, my server name is "127.0.0.1 ". How can I change it into a domain name which can be used for ourside of our firewall?

Regards,

Frank

15

Hi Frank,

How can I change it into a domain name which can be used for ourside of our firewall?

Changing the name would be just as I described above, but to configure your network and firewall (and to register a domain or sub domain if need be) so that Messenger is accessible from the outside world is something you’'ll have to coordinate with whomever administers your network and firewall. Just tell the administrator(s) that you have an application/server you need to be accessible via the Internet and you can go from there.

Hope that helps,

Ryan

16

Thanks Ryan for your great helps. I solved the problem.

Best regards,

Frank