Skip to main content Skip to complementary content

Configurable ODBC

The Configurable ODBC directory service provider can connect to ODBC databases. 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.

Add

To add a Configurable ODBC entry, click on the Add icon, Add, 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, Get Default, 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, Edit, 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.
      • SQL: This connection type makes the system use the .NET SqlConnection class. Enter the custom connection string in the Override Connection String field.
      • Oracle: This connection type makes the system use the .NET OracleConnection class. Enter the custom connection string in the Override Connection String field.

      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 noteValid 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,Delete.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com