How to connect to SQL Server 2014 in SAP Data Services using JDBCAdapter Adapter type
SAP Data Services, as of 4.2 SP5, now supports the use of JDBC drivers as a data source. This capability has been implemented through a new adapter type, JDBC.
Environment: Data Services 4.2 SP5 Designer(Windows 7)
DS Job Server: Red Hat Linux
Database: SQL Server 2014
Database Authentication: Windows Authentication
If we want to use Windows Authentication in SQL Server 2014 Datastore then this is not supported for unix platform.
As per the Product Availability Matrix, we can install SQL Server Native Client 11 on DS Client machine and DataDirect ODBC Driver v7.2 on linux box so that BODS communicates with SQL Server 2014 properly. But due to security compliance in our project we need to use Windows Authentication as no exception can be raised.
As a workaround I tried to create an Adapter Datastore in Designer of JDBCAdapter type.
To add a JDBC adapter you use the Data Services Administration Console, browse to adapter instances, choose your job server and then select Adapter Configuration.
Select JDBCAdapter. As we are using SQL Server 2014 as a source so add the JTDS library files for SQL Server to the classpath /appl/sap/BODS4/dataservices/ext/lib/ jtds-1.2.7.jar. You can also add the jar file under the C:\Program Files (x86)\SAP BusinessObjects\Data Services\ext\lib directory of Windows 7 Client machine where DS Designer Client is installed.
SQL Server 2014 only supports JDBC connectivity using JTDS 1.7 jar file. You can download the JTDS JAR file from below URL
Then you add the class name, driver URL, username and password. Class name and URL should be contained in the documentation from the JDBC driver provider. The URL for SQL Server 2014 includes server name, integrated Security, authentication Scheme, domain and database name.
JDBC Driver Class Name: net.sourceforge.jtds.jdbc.Driver
JDBC Driver URL: jdbc:jtds:sqlserver://database_server_name;integratedSecurity=true;authenticationScheme=JavaKerberos;domain=domain_name;databaseName=database_name;
The next section is used to configure what Data Services pushes down to the database. This will vary by database type and JDBC driver provider.
Once you have applied the configuration settings you can start the adapter. This is now ready for use in Data Services Designer.
In Designer create a new datastore and change the type to Adapter. Select the adapter you have created in the management console and select OK.
You can now browse the metadata as you would in any other datastore type and import the tables you require.
There are a few limitations with the JDBC Adapter, which include no View Data, Stored Procedures, Lookup and Table Comparisons. You cannot use the tables in the adapter datastore as a target in your data flow.