How to set up SSO with pass-through for ODBC connections to Denodo

Introduction

This document recreates step-by-step the process of configuring an ODBC northbound connection to Denodo with Kerberos with single-sign-on (SSO) and pass-through session credentials.  

The user authentication happens against the Microsoft Active Directory (AD) synchronized with the machine, as the user automatically forwards the session Kerberos ticket to it getting access to the authorized sources with SSO.

This is a pre requirement to configure SSO with pass-through from third-party applications that use the Denodo ODBC driver to connect to Denodo

Steps to configure the connection

1. Active Directory: In Denodo Virtual DataPort (VDP) we will need to configure an appropriate LDAP connection to AD, to return the user groups of any user logging in using SSO.

• We need details of the AD server holding users and their groups.

• LDAP/AD: host Uri e.g ldap://host:port
• Service User ID and Password for access to LDAP/AD server

• In VDP we will need to create an LDAP Data Source in Denodo pointing to the appropriate AD server. See section LDAP Sources.

2. We will need to configure the Kerberos server. See section Setting-up Kerberos Authentication.
3. Once the Kerberos server and the LDAP data source is ready we will need to configure the VDP Server to use Kerberos. See section Setting-Up the Kerberos Authentication in the Virtual DataPort Server.

• We need to import the roles of the users that we want to connect to the database configured.

Once we have them imported, you must grant at least connection privileges to them.

• Pay special attention to the Java Cryptography Extension installation just in case AES 256 bit encryption is activated in the Kerberos Server.
• The Virtual DataPort database that the DSN connects to needs to be configured with the option “ODBC/ADO.net authentication type” set to “Kerberos”. See section Configuring and Deleting Databases.

4. To perform this connection we will need to be logged in with an account belonging to the Active Directory as the current session credentials are going to be used for authentication.
5. Create a DSN using the Denodo ODBC Driver. See sections:

• For Windows:

• Install the ODBC Driver on Windows
• Set Up a DSN on Windows 

• For Linux:

• Install the ODBC Driver on Linux
• Set Up a DSN on Linux and Other UNIX

6. To use SSO and Pass-Through session credentials configure the DSN as follows:

• Server: Provide the Fully Qualified Domain Name of the machine that has the VDP Server on it. For example, if in the Kerberos configuration, the field Server principal is HTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM, enter denodo-prod.subnet1.contoso.com.
• Database: The name of the database that has configured the Kerberos authentication for ODBC/ADO.NET connections.
• User Name & Password: This must be set empty to force the Driver to use SSO + Pass-Through.

Troubleshooting

To obtain the log of any error that could occur in the VDP Server, start the server from a console using the command line scripts and errors will be shown in the standard output.

This Oracle troubleshooting page includes a good list of common problems when configuring SSO.

Problem:

No output in the cmd when using the command line startup script

Solution:

If the debug mode for Kerberos is enabled, use Powershell to start the server or the .sh script.

Problem:

Found unsupported keytype (18) for HTTP/test.domain.com@DOMAIN.COM

Solution:

Check if the AES-256 option is enabled/disabled for the user configuration associated with the SPN on the Denodo Server. If enabled, disable it. If this is not possible, install the JCE in the JRE used by the Denodo Server. When the encryption is changed it is recommended to regenerate the .keytab for the user that authenticates the VDP Server.

Problem:

Mechanism level: GSSHeader did not find the right tag

Solution:

When performing a connection to VDP through the ODBC Driver the FQDN of the machine that has the Denodo Server (or the load balancer) running has to be specified. The connection to the VDP server has to be done from a different machine from where the VDP Server is running.

Problem:

Key for the principal HTTP/test.domain.com@DOMAIN.COM not available in <<keytab directory>>

Password from shared state is null

                [Krb5LoginModule] authentication failed

Password can not be obtained from sharedstate

Solution:

The SPN has changed, the address in the VDP Server needs to be changed.