Skip to main content Skip to complementary content

ODBC example

Each data source has a different configuration and the following are two examples (csv and SQL) of adding an ODBC user directory connector.

ODBC example (csv)

  1. Verify that the Microsoft Access Text Driver is installed.

  2. Set up an ODBC source on the server. You need to store the data in two separate csv files, for example, in this location: %ProgramData%\Qlik\Sense\temp.

    Information noteThe temp folder is not included in the default installation. You need to create the temp folder, if not already done by another QMC administrator.

    Table1.csv contains the users and Table2.csv the user attributes. The values in the csv files are comma separated.

    Example:  

    Table1.csv contents:

    userid,name
    JoD,John Doe

     

    Table2.csv contents:

    userid,type,value
    JoD,email,jod@gmail.com
  3. Open the QMC: https://<QPS server name>/qmc

  4. Select User directory connectors on the QMC start page or from the StartArrow down drop-down menu to display the overview. Create a new user directory connector (ODBC) and edit the properties.

     

    Identification

    All fields are mandatory and must not be empty.

    Identification properties
    Property Description
    Name

    The name of the UDC configuration, defined from the QMC.

    Type

    The UDC type.

    User sync settings

    User sync property descriptions and values
    Property Description Default value
    Sync user data for existing users
    • When selected, only the existing users are synchronized. An existing user is a user who has logged in to Qlik Sense and/or been previously synchronized from the configured directory service.
    • When not selected, all the users, defined by the properties for the UDC, are synchronized from the configured directory service.
    Information noteThe user attributes are only synced when a user logs in to the hub. Even if you delete the user in the QMC, the active session is still valid for the user that has been deleted. If the hub is only refreshed, the user is added to the database, but without any attributes.

    Selected

    Connection

    Property Description Default value
    User directory name

    The name of the user directory. Must be unique, otherwise the connector will not be configured. The name must not contain spaces.

    -
    Users table name The name of the table containing the users. Include the file extension in the table name, for example: Table.csv. -
    Attributes table name The name of the table containing the user attributes. Include the file extension in the table name, for example: Table.csv. -
    Visible connection string

    The visible part of the connection string that is used to connect to the data source. Specify one of the following:

    • A full connection string, for example: Driver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Dbq=%ProgramData%\Qlik\Sense\temp
      • Driver must point to a driver currently on the machine. In the ODBC Data Source Administrator, check which driver to specify. Search for "data source" to find the application.
      • Dbq: Path to the folder where the csv files are stored.
    • A pointer to an established System DSN, for example, dsn=MyDSN;
    Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.
    -
    Encrypted connection string

    The encrypted part of the connection string that is used to connect to the data source. Typically, this string contains user name and password.

    Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.
    -
    Synchronization timeout (seconds) The timeout for reading data from the data source. 240

    Example:  

    User table name: Table1.csv

    Attributes table name: Table2.csv

    Visible connections stringDriver={Microsoft Access Text Driver (*.txt, *.csv)};Extensions=asc,csv,tab,txt;Dbq=%ProgramData%\Qlik\Sense\temp

  5. Click Apply to apply your changes.

  6. Go to the User directory connectors overview and check if the user directory is displayed as Configured and Operational.

    Information noteIf the User directory name is not unique the connector will not be configured. If not operational, check the repository system log in: %ProgramData%\Qlik\Sense\Log\Repository\Trace.

You have added an ODBC data source and initial synchronization will be performed within five minutes (by default).

ODBC example (SQL)

  1. Create an SQL database with users. The database must consist of two tables, one with the users and one with the attributes of the users.

    Example:  

    Table1: SQL users

    SQL users
    Index Value
    1 ID,userid,name
    2

    1,JoD,John Doe

    Table2: SQL attributes

    SQL attributes
    Index Value
    1 userid,type,value
    2 JoD,email,jod@gmail.com
    Information noteIf the user IDs are unique, the ID column is redundant.
  2. Install an SQL driver on the server, for example, SQL Server Native Client 11.0.

  3. Open the QMC: https://<QPS server name>/qmc

  4. Select User directory connectors on the QMC start page or from the StartArrow down drop-down menu to display the overview. Create a new user directory connector (ODBC) and edit the properties.

     

  5. Identification

    All fields are mandatory and must not be empty.

    Identification properties
    Property Description
    Name

    The name of the UDC configuration, defined from the QMC.

    Type

    The UDC type.

    User sync settings

    User sync properties, descriptions, and default values
    Property Description Default value
    Sync user data for existing users
    • When selected, only the existing users are synchronized. An existing user is a user who has logged in to Qlik Sense and/or been previously synchronized from the configured directory service.
    • When not selected, all the users, defined by the properties for the UDC, are synchronized from the configured directory service.
    Information noteThe user attributes are only synced when a user logs in to the hub. Even if you delete the user in the QMC, the active session is still valid for the user that has been deleted. If the hub is only refreshed, the user is added to the database, but without any attributes.

    Selected

    Connection

    Connection properties, descriptions and default values
    Property Description Default value
    User directory name

    The name of the user directory. Must be unique, otherwise the connector will not be configured. The name must not contain spaces.

    -
    Users table name The name of the table containing the users, for example, UsersTable.
    Information noteWhen setting up an Oracle ODBC user directory connector, the Users table name and Attributes table name must be prefaced by the owner of those tables. For example: OWNER.USERS instead of only USERS.
    -
    Attributes table name The name of the table containing the user attributes, for example, AttributesTable.
    Information noteWhen setting up an Oracle ODBC user directory connector, the Users table name and Attributes table name must be prefaced by the owner of those tables. For example: OWNER.USERS instead of only USERS.
    -
    Visible connection string

    The visible part of the connection string that is used to connect to the data source. Specify one of the following:

    • A full connection string, for example: Driver={SQL Server Native Client 11.0};Server=localhost;Database=Users;Trusted_Connection=yes;
      1. Driver must point to a driver currently on the machine. In the ODBC Data Source Administrator, check which driver to specify. Search for "data source" to find the application.
      2. Server must point to the server that you want to connect to.
      3. Database must point to the database where the tables are.
      4. Trusted_Connection=yes may be required, depending on the setup. In this example it is required.
    • A pointer to an established System DSN, for example, dsn=MyDSN;
    Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.
    -
    Encrypted connection string

    The encrypted part of the connection string that is used to connect to the data source. Typically, this string contains user name and password.

    Information noteThe two connection strings are concatenated into a single connection string when making the connection to the database.
    -
    Synchronization timeout (seconds) The timeout for reading data from the data source. 240

    Example:  

    User table name: UsersTable

    Attributes table name: AttributesTable

    Visible connections stringDriver={SQL Server Native Client 11.0};Server=localhost;Database=Users;Trusted_Connection=yes;

  6. Click Apply to apply your changes.

  7. Go to the User directory connectors overview and check if the user directory is displayed as Configured and Operational.

    Information noteIf the User directory name is not unique the connector will not be configured. If not operational, check the repository system log in: %ProgramData%\Qlik\Sense\Log\Repository\Trace.

You have added an ODBC data source and initial synchronization will be performed within five minutes (by default).

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!