Rhyous

October 27, 2009

Adding a SQL 2008 Server as a DataSource to JasperServer

Filed under: JasperSoft,Software Applications — J. Abram barneck @ 10:07 pm

Ok, so at work I installed JasperServer and spent quite a lot of time in their documentation and could find the basic information of how to connect to a Microsoft SQL 2008 server.

JasperSoft has a nice installer that installs everything for you: JasperServer, Tomcat, Java, MySQL, etc…

The first problem was I had a 2008 R2 64 bit VM that I thought I would use that OS and their installer just crashed with MySQL errors. So I installed MySQL myself, and then reinstalled JasperServer and pointed to my MySQL install and it appeared to work better but there were still a lot of errors in normal use that I couldn’t find documentation on. So I am now using a 2003 R2 SP2 server and everything installed just fine, first time.

I thought Microsoft was really friendly about deploying their JDBC driver for SQL 2005/2008 and that anyone could distribute it as long as they just told Microsoft first, (maybe I am wrong and maybe MS charges to distribute it). Anyway, I expected JasperServer to install the JDBC driver for me (included in their all-encompassing package) but to my dismay, the driver wasn’t there. So that is fine, I was certain there would be a quick and easy document on how add SQL 2008 as a database. Yeah, three days later, still no documentation found….

Finally, with almost no help from the JasperServer documentation, I figured out that really it was Tomcat that needed the JDBC driver, not JasperServer. Well, it turns out that Tomcat no longer uses the CLASSPATH environment variable, so I just had to copy the SQL 2008 JDBC .jar file to a directory Tomcat did look at and I was done. It was simple.

  1. Download the Microsoft JDBC driver: Microsoft SQL Server JDBC Driver
  2. Extract to a good location. I chose c:\program files to extract to.
  3. Copy the sqljdbc4.jar to your installation directory which by default is:
    c:\program files\jasperserver-pro-3.5.1\apache-tomcat\webapps\jasperserver-pro\WEB-INF\lib

  4. Restart JasperServer (there is a shortcut in the start menu to restart it).
  5. Log into JasperServer’s web site. http://YourServer:8080/jasperserver-pro
  6. Go to View | Repository.
  7. Click the second icon called Add Resource and select Data Source.
  8. Choose JDBC Data Source and click next.
  9. Provide any Name, Label, and Description.
  10. For the driver put this:
    com.microsoft.sqlserver.jdbc.SQLServerDriver

  11. For the URL put this:
    jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;

  12. Enter the username and password.
  13. Select a time zone.
  14. Click Test and it should succeed.
  15. Troubleshooting: If it doesn’t succeed, save anyway and restart the JasperServer and watch for any exceptions.

Wow that was easy once I figured it out. Too bad that the lack of good documentation resulted in me spending three days to figure out that I just needed to spend three seconds to copy a file.

JasperServer score card

Install = 8 (Minus two points for not working Server 2008 R2 64 bit)
Documentation = 0 (no points as so far I have not found a document that has been helpful yet.

Also I note that the JasperForge (the opensource part of JasperSoft) has a wiki, but it is nothing but headers and the data is pretty much blank.

I am not trying to knock JasperSoft with this post, but I am just trying to make sure that someone who tries to do the same thing ends up finding my post and getting their SQL 2008 server added as a data source rather quickly, instead of wading through the lack of documentation for three days.

Even to show my good will towards open source projects, I documented my steps on their wiki for them.
http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/DataSources#Adding_a_Microsoft_SQL_Server_2008_Database_as_a_Data_Source

Advertisements

15 Comments »

  1. God Bless U. i was trying hard to do something similar. U have almost saved my job. I agree JASPER documentation sucks. they have only given examples of the samples provided by them. May u get the best appraisal in ur organization 🙂

    Comment by Kiran — December 2, 2009 @ 6:53 am | Reply

    • Thanks. My company, LANDesk, chose to use Jasper’s iReport in part of its upcoming product and it looks great. So we thought we would use the Jasper Server (a different product than iReport) for internal reporting but the Jasper Server was not as easy to work with as iReport. Hopefully it improves.

      Comment by rhyous — December 2, 2009 @ 7:11 am | Reply

      • I do agree. Jasper Server is going the Microsoft way with fancy look and feel but I found a few basic bugs in it in my first few 2 days of usage itself. The exception handling is also bad.
        Btw please do let me know if applications developed in Jasper Server Pro are compatible with IBM Websphere. Because the reporting module which I am designing will finally be integrated with an existing portal which runs on IBM WAS.

        Comment by Kiran — December 3, 2009 @ 4:49 am | Reply

  2. Thanks a lot buddy.. You saved my time… Great work.. Hats off

    Jayabal.

    Comment by Jayabal — December 7, 2009 @ 9:10 am | Reply

  3. Yes. Jaspersoft documentation is a joke. I know they make money off support, but how can I tell if I’d even want to put money and further resources into using these products if I can’t even get the very basics setup.

    Waste of time.

    Comment by kbkb — February 21, 2011 @ 8:34 am | Reply

    • That is why we didn’t choose them for our project. 😉
      And why I have never posted about them again.

      Comment by Rhyous — February 22, 2011 @ 7:11 am | Reply

      • Agree totally. But I had to use an open source solution. No budget to buy a commercial license for a better documented product. I agree that the lack of documentation for Jasperserver is a major reason why I would recommend against companies using it. It has been an extreme source of frusturation for me to get things done. Try an approach, abandon it and do it different. Repeat until you get it to work. Over and over and over. by the way, I don’t have thousands of dollars to spend on their training and neither does my company.

        Comment by Lynette — July 6, 2011 @ 5:23 pm | Reply

  4. Hi, I follow your steps but my JasperSoft 4.0.1 can’t connect to SQL Server, still with the error “Connection Fail” and I don’t understand why?!!!, can give me some advices to fix this?, and yes by the way Jaspersoft Documentation S*CKS!!!!!!

    Comment by Kamuy — April 22, 2011 @ 8:46 am | Reply

  5. Thanks, saved me time! Adding additional notes for benefit of others because I ran into different issues.
    There were a few things I did first to rule out Jasperserver bugs and jasperserver specific problems.

    I first tested connectivity between my laptop and a remote SQL Server 2005 database using SQL
    Server Management Server Express. By doing that, I discovered several problems: (1) port 1433
    TCP/IP was not opened on the remote SQL Server. (2) In my case it was necessary to specify a named instance as part of the connection string (3) I had set up login credentials incorrectly on the remote SQL Server database. I had it set up as using Windows Authentication instead of SQL Server authentication. (4) I had to make sure that the login credential had public and read access to tables
    (5) Microsoft wouldn’t accept my workgroup name so I just used the server’s IP address. If your server is in a domain and uses DNS, you probably won’t run into this issue. This server was not in a domain.
    (6) The database itself was only set up to use windows authentication. I had to change a property at the database level to enable access by both SQL Server authentication and Windows authentication. (7) And of course restart SQL Server services so that the new settings would take. After I finally got a sucessful connection using Microsoft to Microsoft products, the fun started with Jasperserver 🙂
    Thats where your post really saved me time. Here is my variation to show how to reference a named instance in jasperserver if you have one. It defaulted to port 1433.
    jdbc:sqlserver://myServerNameOrIpAddressmyNamedInstance;databaseName=myDatabaseName;

    Comment by Lynette — July 6, 2011 @ 5:15 pm | Reply

  6. Thank you for writing these steps up! I’ve been fighting with JasperServer for a few days now because it wasn’t connecting to SQL Server 2008.

    Comment by Zelria Kinder — December 9, 2011 @ 7:53 am | Reply

  7. Thankyou – very helpful.
    My connection string was jdbc:sqlserver://localhostSQLEXPRESS

    Comment by Roberta — January 15, 2012 @ 7:37 pm | Reply

  8. Thanks.
    JasperServer becomes more and more popular. And it’s not that bad as some of you complain (no, I’m not working for them).

    Comment by Andrew — January 18, 2012 @ 12:19 am | Reply

  9. I need help urgently, I try to connect my jasper 4.5 to ms. sql server 2008 r2 32 bit but failed. Please guide me.thanks

    Comment by Benedict — February 15, 2012 @ 7:23 pm | Reply

  10. I try to connect jasper 4.5 to sql server 2008 r2 but connection fail. can anyone guide me.thanks in advance. Need it urgently

    Comment by Benedict — February 15, 2012 @ 7:25 pm | Reply

  11. I think you made a small error in your URL. You used a : instead of a ; just after the port number. This fixed my problems!

    jdbc:sqlserver://ServerName:1433:databaseName=MyDatabase;

    must be

    jdbc:sqlserver://ServerName:1433;databaseName=MyDatabase;

    Comment by Wouter — March 26, 2012 @ 6:53 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: