Goal

This document describes how to configure an LDAP database in Virtual DataPort and how to debug and troubleshoot this configuration.

It is strongly recommended to read the sections User and access rights in Virtual DataPort and Administration of databases, users, roles and their access rights of the Virtual DataPort Administration Guide before reading this document.

Introduction

Virtual DataPort can connect to LDAP directories (such as Microsoft Windows Server Active Directory) to extract data stored in the server or to delegate the authentication to the LDAP.

In addition to the technical considerations about LDAP and role authentication, there are some aspects to take into account regarding the use of this authentication type. When using LDAP authentication to connect to a database, a potential latency during the authentication process is introduced which might impact overall system performance

To avoid a big overload due to LDAP authentication, the recommendation is to configure the clients to use Connection Pools against the Denodo VDP server. This approach eliminates the need for every query to establish a new connection and authenticate against the LDAP server. For instance, JDBC access allows for the implementation of a connection pool to minimize overhead.

LDAP Protocol

The Lightweight Directory Access Protocol(LDAP) is an open, industry standard application protocol for accessing and maintaining distributed directory information services over an IP network.

Common URLs for connecting to LDAP servers are:

• ldap://acme.server 
• ldap://acme.server/dc=example,dc=com (pertains to all entries within the example.com directory)
• ldaps://secured.acme.server (LDAP over SSL)

Directories have the following model:

• A directory is composed of entries.
• An entry consists of a set of attributes (defined in a schema).
• An attribute has a name and one or more values.
• Each entry has a unique identifier known as its Distinguished Name (DN).

For instance, consider the entry for the user “John Smith”:

Distinguished Name (DN): cn=John Smith,dc=example,dc=com

Attributes:

givenName: John
sn: Smith
mail: john.smith@example.com
memberOF: cn=Users,dc=example,dc=com
objectClass: person

Active Directory

Microsoft Active Directory(AD) is based on standard directory access protocols, such as the Lightweight Directory Access Protocol (LDAP). Therefore, the Virtual DataPort server is capable of communicating with such kinds of  directories using the LDAP protocol.

Most typical Active Directory configurations are domains and forests. For more detailed information about the architecture of an Active Directory installation, please read https://technet.microsoft.com/en-us/library/cc961765.aspx.

Active Directory Domain

Domains are container objects. Domains are a collection of administratively defined objects that share a common directory database, security policies, and trust relationships with other domains. In this way, each domain is an administrative boundary for objects.

A single domain can span multiple physical locations or sites and can contain millions of objects.

To connect to a single domain, usually you can use the LDAPprotocol connecting to the default port 389:

• ldap://acme.server:389

Active Directory Forest

A forest is a complete instance of Active Directory. Each forest acts as a top-level container in that it houses all domain containers for that particular Active Directory instance.

So, a forest can contain one or more domain container objects, all of which share a common logical structure, global catalog (GC), directory schema, and directory configuration, as well as automatic two-way transitive trust relationships.

By default, information in Active Directory is shared only within the forest. In this way, the forest is a security boundary for the information that is contained in that instance of Active Directory.

To connect to a forest, in order to delegate the authentication to the final domain,  you have to connect to the GC. In most configurations, the GC can be accessed using the port 3268.

• ldap://acme.server:3268

Denodo Virtual DataPort configuration

The recommended steps to connect to a Virtual Data Port(VDP) database using LDAP authentication, are as follows:

1. Verify the LDAP or Active Directory configuration.
2. Define an LDAP data source in VDP.
3. Enable and Configure LDAP Authentication for the server.
4. Import roles from the LDAP server.
5. Assign privileges to the LDAP roles to connect to the database.

Verify LDAP or Active Directory Configuration

The most common scenario is to have an Active Directory with the information about users and groups (for example, departments). Before activating the LDAP configuration in VDP, it is essential to take a look at the LDAP server itself to understand how it is structured.

For this purpose, we recommend using a client application like Microsoft AD Explorer (https://docs.microsoft.com/en-us/sysinternals/downloads/adexplorer).

The application will prompt the user for a valid username to connect to the AD (this user needs enough privileges to search into the active directory):

When connected, the application displays in the left panel, all available nodes (grouped in folders or containers):

The image above shows a typical configuration: all users and groups are stored inside CN=Users container;however,  more complex configurations can be found, as illustrated below:

In this case, users are inside containers, and the containers (which are not groups) are the departments.

In summary, with now a working user to connect to the LDAP server, the next step is to go to the Virtual DataPort server to configure the LDAP data source.

Define an LDAP Data Source in VDP

Connect to VDP using Design Studio, for example to the admin database (typically used to hold common data sources) and create a new LDAP data source by navigating to: File > New >  Data source > LDAP.

The Server URI and the credentials (Login and Password) to access the LDAP data source have to be provided (they are the same as the credentials used in AD Explorer in the previous step).

Note: The only difference resides in the Server URI. In VDP, it  must start with “ldap://” or “ldaps://“, whereas in AD Explorer you do not need to include the protocol.

It’s recommended to  enable the “Use paging” option, especially if your organization has numerous nodes,to retrieve them in blocks efficiently.

Enable and Configure LDAP Authentication for the Server

To enable Global LDAP configuration for the Virtual DataPort server, connect to VDP using the Virtual DataPort Administration Tool and navigate to:

Administration > Server Configuration > Server Authentication > LDAP

                        

Enable the check box “Enable LDAP Authentication for the server”  to allow all virtual databases to be authenticated based on the Global LDAP configuration. Leaving this checkbox unchecked will allow administrators to configure and enable LDAP authentication at Database level.

Note: For older versions prior to Denodo 8.0, LDAP Authentication can be only configured at Database level.

In order to find the user and role search patterns, we recommend using  any third- party LDAP client such as AD Explorer,JXplorer or Apache Directory studio to execute and test queries to the LDAP server if needed.

The following information  is required from the LDAP administrator:

• User base: the node of the LDAP server  used as the scope to search for nodes representing users. It is possible to specify more than one user base.
• Attribute with user name: name of the attribute that will be used as login identifier.
• User search pattern: the pattern used to retrieve users from LDAP.
• Role base: the node of the LDAP server used as the scope to search for nodes representing roles. It is possible to specify more than one role base. In some basic configurations, it is the same as the User base.
• Attribute with role name: name of the attribute that contains the name of the role.
• Role search pattern: the pattern used to generate  LDAP queries that will be executed to obtain the nodes representing the roles of a user. This pattern has to include the token @{USERDN}, which will be replaced with the user’s Distinguished Name attempting to connect to the database.

Let’s see an example: we are going to use the first AD configuration mentioned earlier in this document. The configuration was:

CN=Users Container

User base

In the image above, you can see the users stored inside CN=Users:

• CN=Administrator
• CN=Brandon Stevenson
• CN=Cindy Cullen
• CN=Guest
• CN=John Smith
• …

 

As all our users are in the same container, our User base parameter will be only one node. However, in the configuration we must  provide the complete distinguished name.You can find this complete value by selecting the element in AD Explorer, which will display it in full (for example,CN=Users,DC=denodo,DC=loc), as shown below:

Attribute with user name

The next parameter is the Attribute with user name. Now, we have to select the attribute used for the VDP user name at login time. To illustrate, let’s select a user and  examine all his attributes:

In Active Directory configurations, the recommended attribute is always sAMAccountName, because it is unique in the Domain (and it is commonly the same identifier used for  authentication in Windows). However,other attributes can be chosen if necessary.

Selecting sAMAccountName, users will have to utilize  it when using the VDP Administration Tool. For instance, in this case , the user “Brandon Stevenson”must use “bstevenson” as his login identifier.

User search pattern

The last parameter to configure LDAP users is the “User search pattern”. This parameter must be a valid search query  executed in the Domain Controller to retrieve the full list of users. Why is it necessary? At login time, when a user tries to connect to Virtual DataPort, the server will perform the following:

• It connects to the LDAP server using the values (URL path, …) of the LDAP data source.
• When connected, it searches for  users aiming to find the one whose  sAMAccountName value (or any other attribute configured in the previous section) matches the value the user entered in the Login field when connecting to Virtual DataPort.
• If the user is found, the server retrieves its DistinguishedName (DN).
• Finally, it delegates the authentication process to the LDAP server using the DN and the password provided by the user. if LDAP authentication is successful, the user is granted authorization to connect to VDP).

We are going to use AD Explorer to help us  in creating the LDAP query (click on Search > Search Container). To search for users, we have to find an attribute with that information. Referring back   to the user’s attributes screenshot, you can see an attribute with this value:

objectClass = top;person;organizationalPerson;user

This attribute is commonly used for searching users. (or computers, or any other entities). For instance, let’s try with objectClass = user in AD Explorer:

Clicking on the “Add” button, we can see a query (the circled text) which is the LDAP query expression to use in the “User search pattern” parameter in Virtual DataPort.

Clicking on the “Search” button we can test this query:

In the current search results, Computers are returned too (as they have the same objectClass=user value). In this case we can modify our query to filter the computers out by adding the following condition : objectClass is not computer.

Our final LDAP search query is:

(&(objectClass=user)(!(objectClass=computer)))

Role base

Earlier in this document, we saw in the Users container t that inside CN=Users, the users were listed among other elements:

• CN=Allowed RODC Password Replication Group
• CN=Cert Publishers
• CN=director
• CN=employee
• CN=Enterprise Admins
• …

 

These elements represent  groups within Active Directory. In Active Directory, users are members of one or several groups so typically they are used as roles (because people belonging to the same group should have the same privileges over the applications, in our case Virtual DataPort).

As all our groups/roles are in the same container, our Role base parameter will be only one node, as follows:CN=Users,DC=denodo,DC=loc

Attribute with role name

You have to do the same exercise we did with the ‘attribute with user name’ parameter, but in this case with the role name.

The most commonly used attribute can be the name of the group (usually the attributes cn or name or sAMAccountName). Any of them will work in our case.

Note: the value of this attribute has to be the same as the name of a role created in Virtual DataPort.

Role search pattern

First, we have to remember that we have available the variable @{USERDN} (the complete user Distinguished Name) in role searches. As we are using the groups as roles, it is as easy as a search for groups to have the user as member:

(&(member=@{USERDN})(objectClass=group))

For example if the user jsmith tries to connect, the Distinguished Name for this user will be:

CN=jsmith,CN=Users,DC=denodo,DC=loc 

and the final query to execute will be:

(&(member=CN=jsmith,CN=Users,DC=denodo,DC=loc)(objectClass=group)) 

In short, our parameters are:

• User base: CN=Users,DC=denodo,DC=loc
• Attribute with user name: sAMAccountName
• User search pattern: (&(objectClass=user)(!(objectClass=computer)))
• Role base: CN=Users,DC=denodo,DC=loc
• Attribute with role name: sAMAccountName
• Role search pattern:  (&(member=@{USERDN})(objectClass=group))

Import Roles from the LDAP Server

When a user is authenticated successfully in the LDAP server, Virtual DataPort gets the groups of that user. These groups will be compared with the roles created in VDP. In our example, the user “John Smith (jsmith)” is a member of the “director” group, so we need to create the role “director” in VDP.

This can be done manually;however, to import a large number of roles from the LDAP server, it is recommended to use the Import Roles tool. (see section Creating Roles of the Virtual DataPort Administration guide). Note that the ‘Role Search Pattern’ used to search the roles should be indicative of the distinguishing factor in terms of objectClass. For example, using (objectClass=group) allows you to search for the roles in all the groups.  You could also choose to exclude a group from the search for roles.

Note: if the role serveradmin is imported from the LDAP server it will be ignored for security reasons as this role is the predefined role in VDP for global administrators. To assign global administrator privileges to a user, it will be necessary to assign the serveradmin predefined role to one of the other roles retrieved by the LDAP server.

To import just a subset of all the existing roles in an LDAP server it is recommended to manually create the roles instead of iterating on the list of roles. To do that you can use the VDP Admin tool to add the roles one by one or a VQL script to create the roles using the VQL Shell. To create a new role using VQL the following statement can be executed:

CREATE ROLE <role_name> '<role_description>';

Assign Privileges to the LDAP Roles to Connect to the Database

The last step to finish the LDAP configuration is to assign privileges to the created roles. It is recommended to use several VDP admin tool sessions simultaneously to simplify the process:

1. VDP Admin tool connected with an admin user that configures the privileges for the role.
2. VDP Admin tool connected with a normal user that belongs to the role whose privileges are being assigned to check if it is working as expected. Any time the privileges of this normal user or its roles are changed the user will have to reconnect to the VDP admin tool so the changes in the configuration take effect.

Debugging the LDAP Configuration

If you find errors when testing the LDAP configuration, the recommendation is to check the vdp.log file under the <DENODO_HOME>/logs/vdp folder. If there is not enough information about the error it is possible to set up the following log categories to TRACE using the logcontroller stored procedure. For example, the following commands:

call logcontroller(
     'com.denodo.vdb.security.LDAPDatabaseAuthenticator',
     'trace');

call logcontroller(
     'com.denodo.vdb.catalog.user.User',

      'trace');

This will enable trace information in the logs for the categories relevant to the LDAP authentication, and:

call logcontroller(

      'com.denodo.vdb.security.DefaultLDAPUserGroupRetriever',

      'trace');

call logcontroller(
     'com.denodo.vdb.engine.wrapper.raw.ldap.executor.LDAPExecutor',

      'trace');

call logcontroller(

      'com.denodo.vdb.catalog.role.Role',

      'trace');

This will enable trace information in the logs for the categories relevant to the roles.

It is important to remember to change these categories back to ERRORonce the issue has been diagnosed and resolved.

For example, let’s see the log written when the user “John Smith (jsmith)” tries to login:

52270 [RMI TCP Connection(8585)-10.0.20.154] TRACE 20141021125041055 com.denodo.vdb.engine.wrapper.raw.ldap.executor.LDAPExecutor  - Executing LDAPrequest. [base search = CN=Users,DC=denodo,DC=loc, filter = (&(&(objectClass=user)(!(objectClass=computer)))(sAMAccountName=jsmith)), paging = false, page size = 1000, cookie = null]  

• The server makes a query to the LDAPdata source searching for objects having user but not computer as objectClass and having jsmith as sAMAccountName.

353952426 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041211 com.denodo.vdb.engine.wrapper.raw.ldap.executor.LDAPExecutor  - Pages readed: 0, with pagesize: 1000 and retrieved elements with last page: 1
 

• Number of retrieved elements of the LDAP server (in this case only one)

353952426 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041211 com.denodo.vdb.security.LDAPDatabaseAuthenticator  - User found: CN=John Smith,CN=Users,DC=denodo,DC=loc    

• Distinguished Name (DN) of the retrieved element (CN=John Smith,CN=Users,DC=denodo,DC=loc)

353952703 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041488 com.denodo.vdb.security.LDAPDatabaseAuthenticator  - Authentication successfully  

• Password is checked against LDAP server using the user DN (in this example, success)

353952703 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041488 com.denodo.vdb.security.DefaultLDAPUserGroupRetriever  - Searching LDAP groups with User: CN=John Smith,CN=Users,DC=denodo,DC=loc Base: CN=Users,DC=denodo,DC=loc Filter: (distinguishedname=CN=John Smith,CN=Users,DC=denodo,DC=loc)  

353952703 [RMI TCP Connection(8585)-10.0.20.154] TRACE 20141021125041488 com.denodo.vdb.engine.wrapper.raw.ldap.executor.LDAPExecutor  - Executing LDAPrequest. [base search = CN=Users,DC=denodo,DC=loc, filter = (distinguishedname=CN=John Smith,CN=Users,DC=denodo,DC=loc), paging = false, page size = 1000, cookie = null]  

• Role search using the DN. 

353952835 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041620 com.denodo.vdb.security.DefaultLDAPUserGroupRetriever  - Group attribute name: sAMAccountName: director

353952850 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041635 com.denodo.vdb.security.DefaultLDAPUserGroupRetriever  - Processing group director  

353952850 [RMI TCP Connection(8585)-10.0.20.154] DEBUG 20141021125041635 com.denodo.vdb.engine.wrapper.raw.ldap.executor.LDAPExecutor  - Pages readed: 0, with pagesize: 1000 and retrieved elements with last page: 1

 

• Processed groups/roles (in this example, one group ‘director’ was found)

Following the log you can pinpoint  the source of the error:

• User not found in the LDAP server
• Incorrect password
• The groups are not retrieved correctly.
• Groups are retrieved but the role is not created in Virtual DataPort.
• ...

Advanced Features

Working with hierarchical elements

In some environments, you may need to define some LDAP groups as roles in VDP even if users are not direct members of these groups . That configuration is hierarchical: users belong to some groups and those groups are members of other groups.

In this specific scenario, a special syntax is needed in the search queries in order to retrieve all user groups recursively. For example:

Role search pattern: member:1.2.840.113556.1.4.1941:=@USERDN

LDAP elements with unicode characters

If your LDAP elements (user / roles) have unicode characters, you will need to activate in Virtual DataPort “Unicode” as identifiers charset at global server level (Administration > Server configuration > Identifiers charset).

Note: if your preference is to use the Restricted charset in the Administration Tool when creating views, you always have the option to restore the Identifiers charset of an individual database to “Restricted” (Administration > Database management > <database> > Edit)

Assign privileges to single users

If you are in a situation where you need to apply permissions on a user level, then you can adjust the LDAP query to search for roles.

In our previous example, the user “jsmith” has the role “director” in VDP. By editing the role search pattern, you can use something similar to:

(|(distinguishedName=@{USERDN})(member=@{USERDN}))

The search query will return nodes containing "CN=John Smith, CN=Users,DC=denodo,DC=loc" as member OR nodes having as distinguishedName "CN=John Smith, CN=Users,DC=denodo,DC=loc" (the “John Smith” user itself). So in this case, he will have "director" and "jsmith" as roles in VDP (you will have to create this new specific role for this specific user).

References

LDAP Authentication at server level

Importing LDAP roles in Virtual DataPort