Ignite Realtime: Trouble with using SQL Express 2005 ...

Up to Discussions in Openfire Support

This Question is Answered

12 Replies Last post: Jul 29, 2008 11:06 AM by Ryan Hawley

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 23, 2008 3:37 PM

Trouble with using SQL Express 2005

If someone can point me to what i am doing wrong, id appreciate it.

First databases and such are not my fortay, frankly i have little idea what i am doing, but i am a quick learner, and the documentation is just not helping me.

I have created a database, i have run the script to drop the tables and set up. I am running the set up and it never connects.

Here is what i am putting in, i just do not know if its right, i have tried a few different ways.

jdbc:jtds:sqlserver://[server]/[SQLEXPRESS]/[Databases]/[Openfire];appName=jive

i have changed nothing else other then to put in the user and its password, i am just using the SA account and have modified its password, and made Openfire its default database.

What am i doing wrong?

Tags: sql_database, instal, setup_wizard, configuration

Bara Dude

11 posts since
Jul 23, 2008

Currently Being Moderated

Jul 24, 2008 1:28 AM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

Hi,

I am new to Openfire myself but have managed to get it running with Microsoft SQL Server 2005 Express Edition SP2. First, please check whether you have MSSQL TCP/IP up and running (it's off by default). Try at Command Prompt on the server running Openfire this command "telnet hostname_or_ip_addy_of_the_sql_server 1433" and see if the connection is refused or established. If the connection is refused that means you haven't configured MSSQL TCP/IP yet. Please do so using SQL Server Configuraiton Manager. If the connection is established (you see a blank screen and not being returned to the prompt) then the problem is with your DB Connection URL. The one you are using seems to be incorrect. First, you don't need those square brackets ( [ ] ) in the URL. Then it's the server name and the instance name. I had this problem myself and it took a while to figure out. In my case I did not create a new DB under the instance SQLExpress but instead used the default one. The default database for user "sa" is the database called master and that's where the script "C:\Program Files\Openfire\resources\database\openfire_sqlserver.sql" was executed to create the tables. With the help of the error.log file in "C:\Program Files\Openfire\logs" I finally found that the URL in my case would have to be simply jdbc:jtds:sqlserver://localhost/;appName=jive (as Openfire is on the same server as MSSQL Express).

Hope this helps. In any case please let us know.

Cheers!

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 24, 2008 10:25 AM

in response to: Bara Dude

Re: Trouble with using SQL Express 2005

Saddly no dice, yet anyway. I have enabled TCP but nothing, i went in and played with the properties as there was no port listed in there, so i put in 1433. Still nothing, i reset to defaults. Nothing. I have yet to move on to the next step, as i assume it wouldnt work till i resolved this. looks like i was right in regards to that as well. once i can connect to it, i am going to drop the database, and just recreate the tables under the default.

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 24, 2008 10:37 AM

in response to: Bara Dude

Re: Trouble with using SQL Express 2005

hmmm after looking around, it ways its a different port. sure enough i can telnet to that port 7378 in this case. I did drop the database and recreate, but i still can not connect. I still get connection refused in the error log.

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 24, 2008 10:55 AM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

Well seems i am making head way, i changed the URL to

jdbc:jtds:sqlserver://[ Server ]:7378/SQLEXPRESS/;appName=jive

and now get a HTTP 500 error... so i would have to believe i am connecting but running in to something else.

Here is what i get

HTTP ERROR: 500
INTERNAL_SERVER_ERROR
RequestURI=/setup/setup-datasource-standard.jsp

Caused by:
java.lang.NullPointerException
    at org.jivesoftware.database.EmbeddedConnectionProvider.destroy(EmbeddedConnection Provider.java:100)
    at org.jivesoftware.database.DbConnectionManager.setConnectionProvider(DbConnectio nManager.java:476)
    at org.jivesoftware.openfire.admin.setup.setup_002ddatasource_002dstandard_jsp._js pService(setup_002ddatasource_002dstandard_jsp.java:204)
    at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
    at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1093)
    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.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
    at org.jivesoftware.util.LocaleFilter.doFilter(LocaleFilter.java:66)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
    at org.jivesoftware.util.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingF ilter.java:42)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
    at org.jivesoftware.admin.PluginFilter.doFilter(PluginFilter.java:70)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
    at org.jivesoftware.admin.AuthCheckFilter.doFilter(AuthCheckFilter.java:99)
    at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.ja va:1084)
    at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
    at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
    at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
    at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
    at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
    at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollect ion.java:206)
    at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
    at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
    at org.mortbay.jetty.Server.handle(Server.java:324)
    at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
    at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843 )
    at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)
    at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
    at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
    at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
    at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)

Report Abuse

Bara Dude

11 posts since
Jul 23, 2008

Currently Being Moderated

Jul 24, 2008 9:49 PM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

I had similar error but proably on a different step and it was due to a session timeout. Did you try starting it over?

I still don't think you need SQLEXPRESS in the URL. My understanding is one instance can only have one TCP port thus you specify only the port number (correct me if I am wrong as MSSQL isn't my forte either). Anyways, do let us know if it works.

Also I am not sure how you came up with the port number 7378. In my case I put the default port number 1433 as TCP Port under IPAll section of IP Addresses tab of TCP/IP Properties dialog. I also changed Enabled field from No to Yes for both IP1 and IP2 and changed TCP Dynamic Ports to 0. Is 7378 the TCP Dynamic Ports in your case?

I would recommend using SQL Server Management Studio Express to connect to the database via TCP/IP and logon as "sa" (as opposed to using DOMAIN\Administrator). That will confirm whether the database is accessible via TCP/IP. You could try it on the same server where the database resides or even better try it from a different computer. Good luck.

Cheers!

Report Abuse

seth

7 posts since
Feb 29, 2008

Currently Being Moderated

Jul 24, 2008 9:59 PM

in response to: Bara Dude

Re: Trouble with using SQL Express 2005

Here's a couple of other places to check:

Open up your SQL Server Configuration Manager and make sure the SQL Server Browser is turned on. Also make sure that TCP/IP is enabled -- if you're not using any other apps with SQL Server, you may as well turn off Shared Memory, Named Pipes & VIA too. Then take a look under SQL Native Client Configuration -> TCP/IP, right click it and look at Properties. The "Default Port" will tell you what port SQL Server is listening to.

Also, you'll need to make sure that your server is configured for mixed mode authentication (Windows and Sql Server auth). You can do this in SQL Server Management Studio Express. Connect the object explorer to your server (with Windows authentication, if necessary), right click on the database, choose properties, then look at Security, and click on "SQL Server and Windows Authentication mode".

You can also use SQL Server Management Studio Express to verify that your OpenFire user is able to login and can see the database.

Cheers,

- Seth -

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 28, 2008 9:50 PM

in response to: seth

Re: Trouble with using SQL Express 2005

Well i still can not get open fire to connect... via the SQL Manager.. the login i created works via TCP/IP. I can see the tables, after some playing around with but i still get an error msg. I have attached the error log.. still not sure what is going on.

thanks for all your help so far

Attachments:

error.txt (149.7 K)

Report Abuse

Bara Dude

11 posts since
Jul 23, 2008

Currently Being Moderated

Jul 29, 2008 12:13 AM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

It says "Connection refused" meaning the port isn't open on the MS SQL server side or an incorrect port is specified on the Openfire side.

First, try "netstat -an | findstr LISTENING" and see if you have your port open. Second, what's the DB URL you are using?

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 29, 2008 9:32 AM

in response to: Bara Dude

Re: Trouble with using SQL Express 2005

Here are the ports I get back... 1433 is not on there. I found the 7378 port in the configuration manager, properties of TCP/IP and the IP address tab. At the bottom it says IP All... Dynamic Ports.. 7378.

21 ,25 ,53 ,80 ,88 ,135 ,389 ,443 ,445 ,464 ,548 ,593 ,636 ,1026 ,1027 ,1043 ,1067 ,1072 ,1073 ,1077 ,1100 ,1157 ,1232 ,2002 ,2967 ,3131 ,3268 ,3269 ,3389 ,5800 ,5900 ,7378 ,9090 ,10000 ,41380 ,139

This is my URL

jdbc:jtds:sqlserver://[FS0]/;appName=jive

Report Abuse

Bara Dude

11 posts since
Jul 23, 2008

Currently Being Moderated

Jul 29, 2008 10:55 AM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

Try the following URL and if it still doesn't work let's see the error log.

jdbc:jtds:sqlserver://SERVERNAME:7378/;appName=jive

Report Abuse

Ryan Hawley

13 posts since
Jul 18, 2008

Currently Being Moderated

Jul 29, 2008 11:06 AM

in response to: Bara Dude

Re: Trouble with using SQL Express 2005

I had tried that port the first time you had suggested using the port in the URL, but it wasnt working due to the TCP/IP settings i changed for seth. It worked now... and completely installed.

Thank you very much

Report Abuse

Mozzy Thor

7 posts since
Jul 28, 2008

Currently Being Moderated

Jul 29, 2008 11:00 AM

in response to: Ryan Hawley

Re: Trouble with using SQL Express 2005

The problem is that you need to open your "SQL Server Configuration Manager" tool and make sure you have enabled port 1433 to listen on the correct network device. Once you get it enabled then it will show up in your netstat and it will allow remote connections...

SQL 2005, by default, doesnt have that port service enabled. Also, when you reference your SQL instance you might need to refer to it as "MYMACHINE\SQLEXPRESS" .

You'll probably need something like this:

jdbc:jtds:sqlserver://SERVERNAME:1433;instance=SQLEXPRESS;appName=jive

Report Abuse

Go to original post

This Question is Answered

Jul 23, 2008 3:37 PM

Trouble with using SQL Express 2005

Actions

More Like This