Goal

This document describes how to use Windows Authentication to access SQL Server using a JDBC data source.

These steps are only needed when we do not want to use a login and a password in our SQL Server JDBC data source and we want to obtain those credentials from the operating system. When using this approach, the credentials from the user that started the Denodo VDP service will be the ones used to access the SQL Server data source.

The configuration with the jTDS and the Microsoft drivers will be explained.

Content

Using the jTDS JDBC driver

Before creating a data source to a MS SQL Server database from Virtual DataPort using the jTDS driver and Windows Authentication follow these steps:

1. Download the latest jTDS driver version from here
2. Extract from the distribution jtds-xxx-dist.zip the file ntlmauth.dll. Extract the file that matches the target architecture. For instance, for a 64-bit operating system, extract the file under x64/SSO.
3. Copy the file under <DENODO_HOME>/extensions/thirdparty/dll.
4. Create a new JDBC data source in the Virtual DataPort Administration Tool or the Web Design Studio.
5. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012 (JTDS).
6. Database URI:

jdbc:jtds:sqlserver://host:port/database;useNTLMv2=true;domain=domain

7. No login or password is needed. Virtual DataPort will use the credentials of the user that started the VDP server.

Using the Microsoft JDBC driver

MS JDBC Drivers

There are a different methods to authenticate into SQLServer using Windows Credentials:

• Reading OS Credentials without the need of adding login/password in Denodo’s JDBC Data Source dialog. A dll is needed and the steps can be found below
• Using Windows credentials (writing the credentials in the data source dialog). This option has different behaviors depending on the driver version used:

• Drivers < 9.x Not supported
• Version 9.x.

• The url needs to include the domain and integratedSecurity parameters. E.g. :

• jdbc:sqlserver://localhost:1433;database=denodo_db;integratedSecurity=true;domain=TEST

• In the driver properties of the datasource use authenticationScheme=ntlm

• In the login password, use your Windows credentials without the domain

• Version 10.x.

• Steps are the same as in version 9.x although the connection seems to take much longer than with 9.x. Depending on your configuration the following error may appear (but it doesn’t appear in 9.x): “Unable to establish connection: Login failed. The login is from an untrusted domain and cannot be used with Integrated Authentication”

Steps using dll

To access MS SQL Server from VDP using the Microsoft driver and Windows Authentication:

1. Download the driver from Download Microsoft JDBC Driver for SQL Server

2. Unzip the distribution file and copy sqljdbc_xx./enu/auth/x86|x64/msssqljdbc_auth-xx.x.x.x64.dll under <DENODO_HOME>/extensions/thirdparty/dll.
3. For Denodo 7 and newer versions, skip steps 3 and 6. For Denodo 6 and older versions, copy the 'mssql-jdbc-x.x.jar’ file to DENODO_HOME/extensions/thirdparty/lib. If your Virtual DataPort server is running, restart the server so the new driver is added to the classpath. If you do not restart the server an additional configuration property will have to be set when creating the JDBC data source.

4. Create a new JDBC data source in the Virtual DataPort Administration Tool or the Web Design Studio.
5. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012/2014/2016/2017/2019 and higher(MS Driver)
6. For Denodo 7 and newer versions skip this step. If you did not restart the server, browse to the folder where you saved the jar file and select it as Driver class path (optional). If you did restart the server after adding the driver just leave this field blank.
7. Database URI:

jdbc:sqlserver://host;IntegratedSecurity=true;DatabaseName=database

8. No login or password is needed. Virtual DataPort will use credentials of the user that started the VDP server.
9. As the Microsoft driver is provided in Denodo 7 and newer versions, you could skip step 3 and 6 when connecting MS SQL Server in recent Denodo versions.

When using the Microsoft Diver, the URI parameter IntegratedSecurity=true indicates that Windows credentials will be used by SQL Server to authenticate the user of the application.

If "true" the JDBC driver searches the local computer credential cache for credentials that have already been provided at the computer or network logon. If "false" the username and password must be supplied.

This means that the credentials used to authenticate to SQL Server will be the credentials used to log in the machine where the VDP Server is installed or the credentials used to run the VDP Server as a Windows Service.