SQL Server jTDS Support

ReportServer (EN)
1

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)
2

Hey,

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

Cheers
-Arno

3

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.

4

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

5

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

6

Hi Stefano,

the “test” is in fact different in JDBC3 and JDBC4, as you can see here:
https://reportserver.net/releasenotes/RS4.2.0.html#connectiontest

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 :slight_smile: Of course you have to make sure your jdbc URL/user etc are correct, as the “test” button doesn’t work.

Regards,
Eduardo

7

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

8

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

9

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

10

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

11

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

12

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

13

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

14

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

15

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