#1 2015-10-27 15:22:55

thr27
Member
Registered: 2015-10-27

SQL Server jTDS Support

In case anyone needs support for sourceforge jTDS Jdbc Access to SQLServer.

Pls. find attached the groovy script to install.

How to install new jdbc Support for any database is explained here .

jTDS supports access to SQL Server with Domain NTLM User/Password. This does not work with the Microsoft SQL Server JDBC Driver from Linux.

package databasehelper;

import net.datenwerke.rs.scripting.service.scripting.scriptservices.GlobalsWrapper;
import net.datenwerke.rs.base.service.dbhelper.DatabaseHelper
import net.datenwerke.rs.base.service.dbhelper.hooks.DatabaseHelperProviderHook
import net.datenwerke.rs.base.service.dbhelper.queries.LimitQuery
import net.datenwerke.rs.base.service.dbhelper.queries.OffsetQuery;
import net.datenwerke.rs.base.service.dbhelper.queries.OrderQuery;
import net.datenwerke.rs.base.service.dbhelper.queries.Query
import net.datenwerke.rs.base.service.dbhelper.querybuilder.ColumnNamingService
import net.datenwerke.rs.base.service.dbhelper.querybuilder.QueryBuilder
import net.datenwerke.rs.base.service.dbhelper.db.mssql.MsSQLLimitQuery;
import net.datenwerke.rs.base.service.dbhelper.db.mssql.MsSQLOffsetQuery;
import net.datenwerke.rs.base.service.dbhelper.db.mssql.MsSQLOrderQuery;

class JTDSSqlServer extends DatabaseHelper {

	public static final String DB_NAME = "JTDS SqlServer";
	public static final String DB_DRIVER = "net.sourceforge.jtds.jdbc.Driver";
	public static final String DB_DESCRIPTOR = "DBHelper_JTDSSqlServer";

	@Override
	public String getDescriptor() {
		return DB_DESCRIPTOR;
	}

	@Override
	public String getDriver() {
		return DB_DRIVER;
	}

	@Override
	public String getName() {
		return DB_NAME;
	}
	public String createDummyQuery()
  	{
    	return "SELECT 1 as A";
  	}

  	public LimitQuery getNewLimitQuery(Query nestedQuery, QueryBuilder queryBuilder)
  	{
    	return new MsSQLLimitQuery(nestedQuery, queryBuilder);
  	}

  	public OffsetQuery getNewOffsetQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService)
  	{
    	return new MsSQLOffsetQuery(nestedQuery, queryBuilder, columnNamingService);
  	}

  	public OrderQuery getNewOrderQuery(Query nestedQuery, QueryBuilder queryBuilder, ColumnNamingService columnNamingService)
  	{	
    	return new MsSQLOrderQuery(nestedQuery, queryBuilder, columnNamingService);
  	}
	
}


def HOOK_NAME = "DATASOURCE_HELPER_JTDSSQLSERVER"

def callback =  [
			provideDatabaseHelpers : {
				return Collections.singletonList(new JTDSSqlServer());
			}
		] as DatabaseHelperProviderHook;

GLOBALS.services.callbackRegistry.attachHook(HOOK_NAME, DatabaseHelperProviderHook.class, callback)

Offline

#2 2015-10-31 13:07:33

Arno Mittelbach
datenwerke
Registered: 2012-02-14

Re: SQL Server jTDS Support

Hey,

thanks for this. I've transformed this tip into a small blog post.

Cheers
-Arno

Offline

#3 2021-09-20 11:42:45

zaink89
Member
Registered: 2021-09-20

Re: SQL Server jTDS Support

Hello Thr27,

I am not sure how to go about adding jTDS support to my reportserver, the link you provided is not working.
Please assist with another link. I have already downloaded the driver "jtds-1.3.1.jar" and copied it to folder "C:\Program Files\Apache Software Foundation\Tomcat 8.5_Tomcat80\webapps\ROOT\WEB-INF\lib".

however the server still reports driver missing.

Offline

#4 2021-09-22 08:05:51

WendyWright
Member
Registered: 2021-09-22

Re: SQL Server jTDS Support

This is very helpful and much needed, thank you so much!!!!

Last edited by WendyWright (2021-09-22 08:06:17)

Offline

#5 2023-04-18 12:54:41

stefano.orlandi
Member
From: Italy
Registered: 2023-03-22
Website

Re: SQL Server jTDS Support

Hi everybody,
is it still possibile to use this script in ReportServer v4.5?
I need to connect to a very old MSSQL database instance and this driver is required instead of the official microsoft drivers.

I've tried the groovy script but something goes wrong...

net.datenwerke.rs.adminutils.client.datasourcetester.ConnectionTestFailedException: AbstractMethodError:
net.datenwerke.rs.adminutils.service.datasourcetester.DatasourceTesterServiceImpl.testConnection(DatasourceTesterServiceImpl.java:59) at net.datenwerke.rs.adminutils.server.datasourcetester.DatasourceTesterRPCServiceImpl.testConnection(DatasourceTesterRPCServiceImpl.java:52) ...

As I can see the latest version of jtds driver is 1.3.1 which is quite old (2013). According to documentation "jTDS is 100% JDBC 3.0 compatible" but ReportServer v4.5 use jdbc 4.0. Maybe they are no longer compatible.

Thank you in advance

Regards,
Stefano

Offline

#6 2023-04-18 13:00:21

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

the "test" is in fact different in JDBC3 and JDBC4, as you can see here:
https://reportserver.net/releasenotes/R … ectiontest

But maybe your datasource works without testing it, so without using the "test" button to test it, can you pls check this ?
Just open the dynamic list and suppose it works :-) Of course you have to make sure your jdbc URL/user etc are correct, as the "test" button doesn't work.

Regards,
Eduardo

Offline

#7 2023-04-18 13:05:20

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

.. also, remove this from the script:

public String createDummyQuery()
  	{
    	return "SELECT 1 as A";
  	}

Again, the "test" button will not work, but everything else should work.

Regards,
Eduardo

Offline

#8 2023-04-18 14:05:20

stefano.orlandi
Member
From: Italy
Registered: 2023-03-22
Website

Re: SQL Server jTDS Support

Hi Eduardo,
first of all, thanks again.

I removed method "createDummyQuery()" from the script and I confirm that "test" button still doesn't work, but a simple dynamic list report (with a simple query inside) works nicely.

So, it's just a matter of correcting the testing function.

Regards,
Stefano

Offline

#9 2023-05-23 14:35:25

stefano.orlandi
Member
From: Italy
Registered: 2023-03-22
Website

Re: SQL Server jTDS Support

Hi Eduardo,
is there any plan to add support for jtds-1.3.1.jar driver (Legacy SQL Server / Sybase databases) natively in ReportServer?
I'm using it in my ReportServer v4.5 installation and it works nicely.

https://jtds.sourceforge.net/index.html

Regards,
Stefano

Offline

#10 2023-05-24 07:37:39

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

I don't see any reason why this should not be supported, so yes, I raised ticket RS-7438.

You are using the script above, correct? and it works fine (apart from the "test datasource") for exactly which products ? What MSSQL and what Sybase versions exactly are you using with the script above?

Regards,
Eduardo

Offline

#11 2023-05-24 07:50:18

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

also, what JDBC URLs are you using exactly? Of course you can change your ip/hostname, but pls post all other arguments necessary.

Regards,
Eduardo

Offline

#12 2023-05-24 08:01:45

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

pls also note that the JDBC 3 test also works in RS 4.6.1: https://reportserver.net/releasenotes/RS4.6.1.html

RS-7431    New Feature    Support JDBC 3 datasource test

Regards,
Eduardo

Offline

#13 2023-05-25 14:02:48

stefano.orlandi
Member
From: Italy
Registered: 2023-03-22
Website

Re: SQL Server jTDS Support

Hi Eduardo,
here is my url connection string:

jdbc:jtds:sqlserver://10.11.14.17:49189/DbName

I'm successfully using the groovy script above on Microsoft SQL Server 10.50.2500.
I actually don't have any Sybase database configured.

Regards,
Stefano

Offline

#14 2023-08-18 19:34:11

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

pls note that native support for old MSSQL installations (with the jTDS driver) will be added in RS 4.7.0. You will still have to download the driver, but you don't need a script for this.

Regards,
Eduardo

Offline

#15 2024-09-17 07:23:22

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL Server jTDS Support

Hi Stefano,

pls note that 4.7 is released where this functionality is supported out-of-the-box:

https://reportserver.net/releasenotes/RS4.7.0.html

RS-7438    New Feature    Support for old MSSQL installations with jTDS driver

Regards,
Eduardo

Offline

Board footer

Powered by FluxBB