Accessing MS SQL Server using Windows Authentication

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 11 May 2018
Tags: NTLM JDBC data sources SQL Server

Download document

Goal

This document describes how to use Windows Authentication to access SQL Server using a JDBC 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 %SYSTEMROOT%/system32.
  4. Create new JDBC data source.
  5. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012/2014/2016 (JTDS).
  6. Database URI:

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

  1. Login/Password (Windows login and associated pass)

 

The option Pass-through session credentials can also be selected.

Troubleshooting note: In some cases adding the ntlmauth.dll library to the %SYSTEMROOT%/system32 folder will raise the error “SSO Failed: Native SSPI library not loaded” when creating the data source. If this happens, instead of adding the ntlmauth.dll file to the %SYSTEMROOT%/system32 folder copy it to the bin folder of the JRE used by the Denodo installation (<DENODO_HOME>/jre/bin if you are using the default JVM) and restart the VDP server afterwards.

Using the Microsoft JDBC driver

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

  1. Download the driver from  

http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774

  1. Unzip the distribution file and copy sqljdbc_xx./enu/auth/x86|x64/sqljdbc_auth.dll under %SYSTEMROOT%/system32.
  2. For Denodo 7 skip steps 3 and 6. For Denodo 6 and older versions, copy the 'mssql-jdbc-6.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 and additional configuration property will have to be set when creating the JDBC data source.
  1. Create a new JDBC data source.
  2. Select DB Adapter: MS SQL Server 2000/2005/2008/2008R2/2012/2014/2016 (MS Driver)
  3. For Denodo 7 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.
  4. Database URI:

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

  1. No login or password is needed. Virtual DataPort will use credentials of the logged Windows user.
  2. As Microsoft driver is provided in Denodo 7 version, you could skip step 3 and 6 when connecting MS SQL Server in Denodo 7.

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.

Questions

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

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training