2

I originally posted this on stackoverflow. Not sure if this belongs on Serverfault. I am reasonably familiar with the guidelines of serverfault vs stackoverflow however, while it is a server question the goal is to get my XMPP development environment configured. Any recommendations on stackoverflow vs serverfault will also be appreciated.

Anyway here's my question

I am trying to configure my Openfire Server with an instance of MS SQL Server 2008.

I must admit that while I have used Sql Server extensively I have been successfully in using it in spite of myself as I am certainly not an expert (bottom line, you should not assume that I already made any obvious configurations). I also apologize in advance if there is anything so obviously wrong that this is a waste of time question.

My Basic SQL Information/Configuration

  • Server name: PALBU\PALBUSQL
  • Authentication: Sql Server Authentication
  • Login: sa
  • Password: mypassword

Here is my Openfire Settings/Screen shots

Server Settings

  • Domain: palbu

  • Admin Console Port: 9090

  • Secure Admin Console Port: 9091

I selected 'Standard Database Connection' under 'Database Settings'

Database Settings - Standard Connection

  • Database Driver Presets: Microsoft SQLServer
  • JDBC Driver Class: net.sourceforge.jtds.jdbc.Driver
  • Database URL: jdbc:jtds:sqlserver://PALBU/PALBUSQL;appName=jive
  • Username: su
  • Password: mypassword
  • Minimum Connections: 5
  • Maximum Connections: 25
  • Connection Timeout: 1.0 Days

When I try to connect I get an error message 'A connection to the database could not be made You may view the error message error.log'

Here is the Output from the error.log file

2011.07.12 11:10:43 Prototype java.sql.SQLException: Network error IOException: Connection refused: connect at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:385) 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) at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConnectionBuilder.java:39) at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159) at org.logicalcobwebs.proxool.Prototyper.sweep(Prototyper.java:102) at org.logicalcobwebs.proxool.PrototyperThread.run(PrototyperThread.java:44) Caused by: java.net.ConnectException: Connection refused: connect at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.PlainSocketImpl.doConnect(Unknown Source) at java.net.PlainSocketImpl.connectToAddress(Unknown Source) at java.net.PlainSocketImpl.connect(Unknown Source) at java.net.SocksSocketImpl.connect(Unknown Source) at java.net.Socket.connect(Unknown Source) at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:304) at net.sourceforge.jtds.jdbc.SharedSocket.(SharedSocket.java:255) at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:310) ... 8 more

I tried changing my DatabaseURL to just show my server URL

  • Database URL: PALBU/PALBUSQL

I received the same error in the Database Settings but received a different output in my log file. 

  2011.07.12 11:16:12 Prototype
java.sql.SQLException: No suitable driver found for PALBURTUS/PALBURTUSSQL
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.logicalcobwebs.proxool.DefaultConnectionBuilder.buildConnection(DefaultConnectionBuilder.java:39)
    at org.logicalcobwebs.proxool.Prototyper.buildConnection(Prototyper.java:159)
    at org.logicalcobwebs.proxool.Prototyper.sweep(Prototyper.java:102)
    at org.logicalcobwebs.proxool.PrototyperThread.run(PrototyperThread.java:44)

It seems as though this driver is missing from the default Openfire installation, however I'm not very familiar with sql drivers so any help would be appreciated.

pat8719
  • 123
  • 1
  • 8

2 Answers2

3

It says Connection refused in the first log. Try telnetting from your Openfire machine to the SQL server port and see if you can open a connection. If not then you might want to check the firewall rules and allow incoming connections.

The second log is merely telling you that the connection string is invalid, and it doesn't know where or how to connect.

cvaldemar
  • 1,096
  • 1
  • 10
  • 12
  • The openfire server and the sql server are on the same machine so I don't believe the firewall could be an issue, could it? – pat8719 Jul 12 '11 at 16:00
  • Try 127.0.0.1 instead of the machine name. – cvaldemar Jul 12 '11 at 16:10
  • I tried both 127.0.0.1 with this connection string: jdbc:jtds:sqlserver://127.0.0.1/PALBURTUSSQL;appName=jive I tried using localhost as well but I keep getting the same error: java.sql.SQLException: Network error IOException: Connection refused: connect – pat8719 Jul 12 '11 at 16:26
2

Is your SQL server configured to accept network connections? Or, is it on a non-standard port (1433 is default)? Or, is there a host-based firewall enabled on the server?

From that error message, it appears that the OpenFire server could resolve the name, but the attempt at connection was actively refused.

OpenFire takes a jdbc connection string, so it's to be expected that your second attempt failed.

Shane Madden
  • 112,982
  • 12
  • 174
  • 248
  • SQL is configured to 'Accept remote connections to this server' and the port number in use is the default. There is a firewall in use but Openfire and SQL are on the same machine right now. Could the firewall still be a problem? – pat8719 Jul 12 '11 at 16:03
  • Use `localhost` as the server name if it's on the same system. – Shane Madden Jul 12 '11 at 16:07
  • changed it to local host and used the jdbc connection string. Still went back to the error: java.sql.SQLException: Network error IOException: Connection refused: connect – pat8719 Jul 12 '11 at 16:15
  • What's the full jdbc string, now? – Shane Madden Jul 12 '11 at 16:16
  • jdbc:jtds:sqlserver://localhost/PALBUSQL;appName=jive – pat8719 Jul 12 '11 at 16:18
  • Is `PALBUSQL` the name of the database within MSSQL, or the server? A database will need to exist with that name.. but it still doesn't make sense that connections would be refused. As @cvaldemar suggested, try telnetting to 1433. – Shane Madden Jul 12 '11 at 16:25
  • PALBUSQL is the name of the server. The name of the database I was going to use is 'openfire' should this be what my my connection string looks like?: jdbc:jtds:sqlserver://localhost/openfire;appName=jive – pat8719 Jul 12 '11 at 16:30
  • Correct. You should also have prepared the tables in that database; see the documentation [here](http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database.html#sqlserver). – Shane Madden Jul 12 '11 at 16:33
  • okay so had already done all of the steps listed in that document except 'Create a user to access the database if you do not already have one you'd like to use. Consider making the default database for the user be the one you created in step 1.' However even after creating this user and using that login credential instead in the openfire setup I still received the same error. – pat8719 Jul 12 '11 at 17:05
  • Again, what happens when you telnet to port 1443? – Shane Madden Jul 12 '11 at 18:00
  • I tried Telneting to localhost at port 1443 using PuTTy but no connection was able to be made. In fact PuTTy just closed. (Not sure if this was the right way to check using Telnet I just don't know any better not that experienced with server issues) – pat8719 Jul 12 '11 at 19:42
  • so it looks like it was a port issue after all but there were other configurations specific to my environment that were causing your answer not to show results. Thank you and @cvaldemar for you help, I'd vote you both up as well if I had the rep. – pat8719 Jul 14 '11 at 13:21