# Configurable ODBC

The Configurable ODBC directory service provider can connect to ODBC databases. [](https://help.qlik.com/en-US/qlikview/Subsystems/QMC/Content/QV_QMC/QMC_System_Setup_DirectoryServiceProviders_ConfigurableODBC.htm)This folder includes only a General tab.

The ODBC database must have two tables (views); one for entities and one for groups. The entity table (the name of which is defined by the Entity Table Database Name setting below) must contain the following fields:

* entityid (unique identifier, suitable for primary key)
* name (string) (the name of this field is defined by the Entity Name setting below)
* descr (string)
* email (string)

The groups table (the name of which is defined by the Groups Table Database Name setting below) must contain the following fields, which together create a unique identifier:

* groupid
* memberid

Information noteWhen using configurable ODBC under Directory Service Connectors (DSC), it is recommended to use Header authentication. Authorization using Groups pulled from Configurable ODBC under DSC does not work when coupled with NTLM authentication for AccessPoint.

## Example

### Groups table

Groups properties

| Groupid | Memberid |
| --- | --- |
| 1001 | 101 |
| 1001 | 102 |
| 1001 | 103 |
| 1001 | 104 |
| 1002 | 104 |
| 1002 | 105 |

### Entity table

Entity properties

| Entityid | Name | Descr | Email |
| --- | --- | --- | --- |
| 101 | sandra.franklin | Sandra Franklin | sandra.franklin@example.com |
| 102 | michael.milliken | Michael Milliken | michael.milliken@example.com |
| 103 | lucille.pender | Lucille Pender | lucille.pender@example.com |
| 104 | dustin.plunkett | Dustin Plunkett | dustin.plunkett@example.com |
| 105 | benjamin.mitchell | Benjamin Mitchell | benjamin.mitchell@example.com |
| 1001 | users | Users | - |
| 1002 | admins | Administrators | - |

In this example, everyone but Benjamin Mitchell is member of the group named Users, and Dustin Plunkett and Benjamin are the only members of the group called Administrators.

## Directory Service

Information noteIn a server environment, the Microsoft Access Database driver has limitations. To avoid issues, use SQL Server Express Edition.  
[Microsoft Access Database Engine 2016 Redistributable](https://www.microsoft.com/en-us/download/details.aspx?id=54920).

### Add

To add a Configurable ODBC entry, click on the Add icon, the Add icon, to the right in the pane, and configure the following text boxes:

* Path

  To configure the path to the directory service, click on the Get Default icon, the Get Default icon, to use the default path, or enter a valid path in this text box.

  Default value: ODBC://localhost.
* User Name

  To configure the user name used for accessing the directory service, enter the desired credentials in this text box.
* Password

  To configure the password used for accessing the directory service, enter the desired credentials in this text box.
* Settings

  To configure the Directory Service Provider (DSP) Settings, open the dialog by clicking on the Edit icon, the Edit icon, and enter the desired values in the following text boxes:
  * Service Timeout in Seconds

    Set the timeout for the connection to the directory service.

    Default value: 30 (seconds).
  * Directory Label

    Set the label of the directory service to connect to.

    Default value: DB DSP.
  * Entity Name

    Set the name of the entity to connect to.

    Default value: entity\_name.
  * Entity Table Database Name

    Set the name of the entity table to connect to.

    Default value: entity.
  * Groups Table Database Name

    Set the name of the table named groups to connect to.

    Default value: groups.
  * Data Source Name

    Set the name of the ODBC driver.

    Default value: MySQL ODBC 5.1 Driver.
  * Connection Database Name

    Set the name of the ODBC database to connect to.

    Default value: dbname.
  * Database Backend

    Set the type of connection to the database:
    * ODBC: This connection type makes the system use the .NET OdbcConnection class.

      [ObdcConnection Class](http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.aspx)
    * SQL: This connection type makes the system use the .NET SqlConnection class. Enter the custom connection string in the Override Connection String field.

      [SqlConnection Class](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx)
    * Oracle: This connection type makes the system use the .NET OracleConnection class. Enter the custom connection string in the Override Connection String field.

      [OracleConnection Class](http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleconnection.aspx)

    Default value: ODBC.
  * Override Connection String

    If entering a string in this field, the entered value will be used, while the settings in Connection Database Name and Data Source Name will be ignored. The variables {user} and {pwd} can be used in the connection string. The variables are automatically replaced by the contents of the User Name and Password text boxes and can be used independently of each other.

    Example:

    Server=MyServer;Database=MyDB;User Id={user};Password={pwd};

    If not entering a string in this field, the Database Backend drop-down will be ignored and the connection to the database will be set to ODBC.

    Default value: Driver={Microsoft Access Driver (\*.mdb, \*.accdb)};Dbq=C:\AccessTest.accdb;.
  * Cache Expiry in Minutes

    Set the time for how long the queries to the directory service should be cached.

    Default value: 15 (minutes).

Information note[](https://help.qlik.com/en-US/qlikview/Subsystems/QMC/Content/QV_QMC/QMC_System_Setup_DirectoryServiceProviders_ConfigurableODBC.htm)Valid value(s) must be entered, which might be indicated with a red asterisk and a tool-tip error message.

### Delete

To entirely remove a configured entry from the list, click on the Delete icon,the Delete icon.