You can translate the document:

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 Microsoft  and jTDS drivers will be explained.

NOTE: The jTDS driver is no longer maintained and its use should be avoided.

Content

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):
  • Drivers < 10.x Not supported
  • Version 10.x.

 

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

    jdbc:sqlserver://localhost:1433;database=denodo_db;

integratedSecurity=true;domain=TEST

b. In the Design Studio navigate to the Advanced Tab > Driver properties of the data source use authenticationScheme=ntlm like below

c.In the login password, use your Windows credentials without the domain

Steps using dll

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

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

  1. Make sure that you are downloading the driver that matches the driver used by Denodo in the data source, this depends on the database adapter and classpath selected.

For instance, if the classpath in the data source is mssql-jdbc-10.x go to <DENODO_HOME>/lib/extensions/jdbc-drivers/mssql-jdbc-10.x, check the driver version (i.e. mssql-jdbc-10.2.0.jre8.jar) and make sure you download the same version from the Microsoft download site.

  1. 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.

  1. Database URI:

jdbc:sqlserver://<host_name>:<port>;IntegratedSecurity=true;DatabaseName=database

    Note: If the SQL server is not SSL enabled,then select the latest driver version for            the SQL Server datasource and add encrypt=false in the Database URI if you are using MS Driver for the Test connection.

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

  1. As the Microsoft driver is provided in Denodo 7 and newer versions, you could skip step 2 while 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 to the machine where the VDP Server is installed or the credentials used to run the VDP Server as a Windows Service.

Using the jTDS JDBC driver

NOTE: The jTDS driver is no longer maintained and its use should be avoided.

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 JTDS Document.
  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.

Note: Please restart the Virtual DataPort server for the changes to take effect.

  1. Create a new JDBC data source in the Virtual DataPort Administration Tool or the Web Design Studio.
  2. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012 (JTDS).
  3. Database URI:

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

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

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here