Active Directory Connection Details

Introduction

Connector Version

This documentation is based on version 21.0.8657 of the connector.

Get Started

Active Directory Version Support

The connector provides SQL-based access to Active Directory through the LDAP protocol. The connector is a standard LDAP client that supports LDAP v2 and v3.

Establish a Connection

Authenticate to Active Directory

To authenticate requests, set the User and Password properties to valid Active Directory credentials (e.g., set User to "Domain\BobF" or "cn=Bob F,ou=Employees,dc=Domain").

The connector uses plaintext authentication by default, since the connector attempts to negotiate TLS/SSL with the server. You can specify another authentication method with AuthMechanism.

See SSL Configuration for more information on TLS/SSL configuration.

Connect to Active Directory

Set Server and Port for basic connectivity. Additionally, you can fine-tune the connection with the following:

• FollowReferrals: When set, the connector surfaces data as views from only referral servers. To modify data on a referral server, you must specify this server with Server and Port.
• LDAPVersion: Set this to the version of the protocol your server implements; by default, the connector uses version 2.

Fine Tuning Data Access

The following properties control the scope of data returned:

• BaseDN will limit the scope of LDAP searches to the height of the distinguished name provided. Note: Specifying a narrow BaseDN may greatly increase performance; for example, a value of "cn=users,dc=domain" will only return results contained within "cn=users" and its children.
• Scope: This property enables more granular control over the data to return from a subtree.

Customize Tables

The connector surfaces the columns most often needed from Active Directory entities. However, if you need to work with other data, the tables are easy to modify. Tables are defined in schema files, which have a simple format.

See Working with Active Directory Tables for a guide to extending the default schemas or writing your own. To use custom schemas, set the Location property to the folder containing the schema files.

Work with Active Directory Tables

The connector includes table schemas for many standard Active Directory objects. You can easily extend the included table schemas to edit column behavior or you can write your own from scratch.

Table schemas are defined in .rsd files, which are simple configuration files. This section will walk through different parts of the schema, adding several columns to the Person table as an example.

You can find the Person.rsd file in the db subfolder in the installation folder of the Active Directory connector.

Connect to Custom Tables

To use custom schemas, set the Location property to the folder containing the schema files.

Define a New Table

It is important to define a new table with the same name as the object class that the table will represent. This will allow the connector to search for only the desired object class when querying the Active Directory server. The file name defines the table name.

Define Table Columns and Inputs

Columns are defined in the rsb:info block, a shown below. The attr tags in the schema represent the columns of the table. These should match the attributes that make up the desired object class.

There are a few columns that every table should include, regardless of the object class:

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">
<rsb:info title="Person" description="Create, update, delete, and query person entries in Active Directory.">
<!-- Required Columns -->
<attr name="Id"          xs:type="string" readonly="true"  key="true"        />
<attr name="DN"          xs:type="string" readonly="true"  required="false" other:ldaptype="OID" />
<attr name="RDN"         xs:type="string" readonly="true"  required="false" other:ldaptype="Directory String" />
<attr name="BaseDN"      xs:type="string" readonly="true"  required="false" other:ldaptype="OID" />

Note: The title attribute of the rsb:info block must match the name of the .rsd file.

Customize Column Behavior

Each column requires at least name and xs:type attributes. Additionally, you will need to specify dataFormat to decide how data is returned from the table. For example:

<!-- Person Required Attributes -->
<attr name="ObjectClass"                 other:dataFormat="splitDataByRow" xs:type="string" readonly="false" required="false" other:ldaptype="OID" />
<attr name="SN"                          other:dataFormat="delimitedData"  xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="CN"                          other:dataFormat="delimitedData"  xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />

<!-- Person Optional Attributes -->
<attr name="UserPassword"                other:dataFormat="delimitedData"  xs:type="string" readonly="false" required="false" other:ldaptype="Binary" />
<attr name="TelephoneNumber"             other:dataFormat="delimitedData"  xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="SeeAlso"                     other:dataFormat="delimitedData"  xs:type="string" readonly="false" required="false" other:ldaptype="DN" />
<attr name="Description_1"               other:dataFormat="splitDataByCol" xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="Description_2"               other:dataFormat="splitDataByCol" xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="Description_3"               other:dataFormat="splitDataByCol" xs:type="string" readonly="false" required="false" other:ldaptype="Directory String" />

The other:dataFormat attribute has three options:

• delimitedData: Return multiple Active Directory attribute values as delimited strings, separated by the delimiter character defined in the Table Settings section of the .rsd file, detailed later.

  This is the default format in which to retrieve data and the delimiter defaults to a semicolon.

• splitDataByRow: Push multiple Active Directory attribute values for the same DN as separate rows. All other columns will be pushed consistently, and the index in ID will be incremented. Note: Pushing multiple columns like this will exponentially grow the result set, potentially causing performance issues.

• splitDataByCol: Push multiple Active Directory attribute values for the same DN with an appended index on the column name. You need to define multiple columns and append an "_n" to the end; for example, ObjectClass_1, ObjectClass_2, and ObjectClass_3. In this example, if there are more than 3 values, the remaining values will not be visible in the table, unless more columns are added.

Example: Splitting the ObjectClass Attribute

The code below can be used to split the different values of the ObjectClass attributes into their own rows and Description attributes into their own columns. Notice the column definition now includes multiple columns for the Description attribute. Also note the other:dataFormat attribute for the attr.

...
<attr name="ObjectClass"     other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="OID" />
<attr name="SN"              other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="CN"              other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="UserPassword"    other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Binary" />
<attr name="TelephoneNumber" other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="SeeAlso"         other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="DN" />
<attr name="Description_1"   other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="Description_2"   other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />
<attr name="Description_3"   other:dataFormat="delimitedData" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" />

</rsb:info>

<!-- Table Settings -->
<rsb:set attr="delimiter" value=";"/>
...

An example result will look like:

Specify Column Encoding

In addition to data format on inputs, encoding can also be specified. Currently, returning data with UTF8 encoding or BASE64 encoding is supported. In order to retrieve data with a specified encoding, the other:encoding field must be specified for the desired attribute to be encoded. If no encoding is specified, UTF8 is the default.

An example of specifying encoding for an attribute:

...
<attr name="ObjectClass"     other:dataFormat="delimitedData" other:encoding="UTF8"   xs:type="string"  readonly="false" required="false" other:ldaptype="OID" desc="The object class of the entry."/>
<attr name="SN"              other:dataFormat="delimitedData" other:encoding="BASE64" xs:type="string"  readonly="false" required="false" other:ldaptype="Directory String" desc="The surname of the person."/>
...

Modify Filter Behavior

Optionally, there are two attributes that can be used to control how filtering is handled when using the driver with SupportEnhancedSQL. The other:ldaptype attribute can be used to set the LDAP syntax of a field. This is used to determine the comparison operators that are supported server-side on a per-field basis. For example, if a field is marked as the type 'DN' and a query filtering for a substring (i.e., CONTAINS), which is not supported server-side, the driver will instead process this part of the filter entirely client-side. The supported type names are found in section 4.3.2 of RFC 2252. If you are unsure of the type or just want to disable server-side filtering for a given column entirely, the other:filterable attribute is also available. Setting this to false for the field will prevent this from ever being sent to the server in a filter, overriding the other:ldaptype attribute entirely.

Configure Table Settings

In addition to the attributes and inputs, you will need to specify the delimiter.

The delimiter specifies the character that will be used for delimited data. Delimited data will be returned for any attribute that appears multiple times for a single object (unless otherwise specified in other:dataFormat).

For example, the code below will concatenate multiple values of an attribute using the ';' character.

...
</rsb:info>

<!-- Table Settings -->
<rsb:set attr="delimiter" value=";"/>
...

Define Supported Operations

Operation definitions will remain exactly the same for all newly created tables: Simply copy and paste these from an existing table, as needed.

<!-- Operation definitions -->
<rsb:script method="GET">
<rsb:set attr="action" value="Get" />
<rsb:call op="ldapadoLDAP" >
<rsb:push />
</rsb:call>
</rsb:script>

<rsb:script method="POST">
<rsb:set attr="action" value="Post" />
<rsb:call op="ldapadoLDAP" >
<rsb:push item="toout"/>
</rsb:call>
</rsb:script>

<rsb:script method="MERGE">
<rsb:set attr="action" value="Merge" />
<rsb:call op="ldapadoLDAP" >
<rsb:push />
</rsb:call>
</rsb:script>

<rsb:script method="DELETE">
<rsb:set attr="action" value="Delete" />
<rsb:call op="ldapadoLDAP" >
<rsb:push />
</rsb:call>
</rsb:script>

Important Notes

Stored Procedures

• The stored procedures features described in this documentation are not currently supported.
• Because stored procedures are not currently supported, any feature dependent on stored procedures is also currently not supported.

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Active Directory connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

User Defined Views

The Active Directory connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM User WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model

The Active Directory connector models Active Directory entities in relational tables and stored procedures.

Tables

The included Tables cover many standard Active Directory object classes. You can easily extend the schemas to map more closely to your Active Directory classes. The schemas are defined in simple configuration files.

To use custom tables and schemas, set the Location property to the folder containing the schema files. The schemas shipped with the connector are located in the db subfolder of the installation directory.

See Working with Active Directory Tables for a guide to customizing table schemas.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to access Active Directory functionality not represented as SELECT, INSERT, UPDATE, or DELETE.

Collaborative Query Processing

API limitations and requirements are documented in this section. The connector offloads as much of the SELECT statement processing as possible to Active Directory and then processes the rest of the query in memory.

See SupportEnhancedSQL for more information on how the connector circumvents API limitations with in-memory client-side processing.

Tables

The connector models the data in Active Directory into a list of tables that can be queried using standard SQL statements.

Generally, querying Active Directory tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Active Directory Connector Tables

Account

The account object class is used to define entries that represent computer accounts.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ApplicationEntity

X.500 base class for applications: Directory Service only uses subclass MSFT-DSA.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ApplicationProcess

X.500 base class for applications: Exchange only uses subclass DSA-Application.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ApplicationSettings

Base class for server-specific application settings.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ApplicationSiteSettings

Contains all site-specific settings.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ApplicationVersion

Can be used by application developers to store version information about their application or its schema.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BuiltinDomain

The container that holds the default groups for a domain.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CertificationAuthority

Represents a process that issues public key certificates, for example, a Certificate Server.

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Computer

This class represents a computer account in the domain.

Columns