JasperServer Repository MS SQL Server 2008

Could You please help me with some problems connected with connection between my MS SQL Server 2008 database and Jaspersoft ? There is a problem with adding a SQL 2008 Server as a DataSource to Jaspersoft.

I have been trying to make a connection between these two softwares fo whole day, but unsuccessfully I couldn't manage to solve the problem. I have done almost everything according to the article:

http://jasperforge.org/plugins/mwiki/index.php/Jasperserver/DataSources#Adding_a_Microsoft_SQL_Server_2008_Database_as_a_Data_Source

My server is named: MORSZYNSKA
DB: Morszynska_dane
User is: Administrator
no password

I'm not exactly sure if I put correct URL:

Is phrase: "jdbc:sqlserver://host:MORSZYNSKA;databaseName=Morszynska_dane" correct ?

What is more, should driver be:

com.microsoft.sqlserver.jdbc.SQLServerDriver

or

com.microsoft.sqlserver.jdbc4.SQLServerDriver ?

And the last question:

Shoul I install WAR File Distribution ? Is it necessary ?

Thank You for Your help in advance.


Kind regards,
Bartłomiej Siawrys

Tel.: +48 513 959 069
Mail: siawrys@synergy.pl

Synergy Polska Sp. z o.o.
Plac Rembowskiego 9A lokal 3
02-915 Warszawa
www.synergy.pl

Thx for Ur help.

Exactly, there was a problem with no password for the user who wanted to connect to DB via Jaspersoft.

Bartłomiej Siawrys

 Have you restarted the JasperServer Service? (That is necissary in order for Jasper Server to see the .jar files that you copied over).

I'm also connecting to a remote SQL Server (2005)  datasource for the first time using Jasperserver.   I finally got the connection to work but it was lots of trouble.   Is Ireport installed on a Windows machine?  Is the Jasperserver also installed on a Windows machine?  This may make a difference.  I had all kinds of trouble with SQL Server authentication versus Windows Authentication.

I can finally connect to SQL Server 2005 via a datasource from both Windows laptop and Linux Fedora machine.  However, I am now trying to figure out the cause of error when it executes a query.  I'm getting "com.microsoft.sqlserver.jdbc.SQLServerException: SQL Server supports holdability at the connection level only. ".    This error did not occur when connecting from Jasperserver 4.01 on my Windows Laptop.   The error happens in Linux Jasperserver 4.01.  Thats why I ask about which environment IReport is installed in and which environment Jasperserver is installed in.

I will open a different thread on my problem if I can't figure it out within a few days.

*** 07/11/11 ***
My issue above was caused by a setting In jasperreports.properties:   net.sf.jasperreports.jdbc.holdability=close
I had configured my laptop Jasperserver slightly differently than the production Linux one.  Thats why it worked in
one environment but not another.

MS SQL Server won't allow a query to execute with the holdability setting set to close.
It throws an exception with message "SQL Server supports holdability at the connection level
only, use the connection.setHoldability() method".

Once I commented out the holdability and let it default, everything worked fine.

Yes, same scenario only I'm using 2005 versus 2008. But that should not matter. With a local connection your connection string should probably just have localhost in it. Here is everything I ended up having to do to connect to SQL Server. Surely one or more of these must be causing your connection failure.

(1) port 1433 TCP/IP was not opened on the remote SQL Server. Adjusted firewall to allow it.

(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.  Add a new user account to your SQL server database.  Don't use administrator account.  Just a simple user (myuser) and then make sure that user has SQL Server authentication - not windows 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 in the connection string. 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.

(8) I had sqljdbc4.jar in the wrong place. It had to be under the lib directory of Apache Tomcat. NOT under the lib under webapps. Put it under the very first lib you see as you expand tomcat root.  I'm using sqljdbc4.jar for the JDBC driver.  This is the driver to use if you have jdk 1.6 installed on the server.  You have to use a different JDBC driver if you have an earlier version of jdk.  Make sure there are not 2 conflicting jdks on your server.

After I finally got a sucessful connection using Microsoft to Microsoft products, I created a SQL Server connection string like the one below that connected. It defaulted to port 1433 and it worked.

Named instance: jdbc:sqlserver://myServerNameOrIpAddress\\myNamedInstance;databaseName=myDatabaseName;

No Named instance: jdbc:sqlserver://myServerNameOrIpAddress;databaseName=myDatabaseName;