article How to setup a Sense User Directory Connector to a SQL Database in Official Support Articles

How to setup a Sense User Directory Connector to a SQL Database

Andre_Sostizzo — Wed, 02 Sep 2020 11:46:00 GMT

For data examples and more information, see the Qlik Sense online help section for the applicable Sense version.

The key elements that you will need are going to be:

A user table
An attributes table
Access to the Database

Keep in mind:

The user and/or attributes tables can be views rather than new tables so you do not need to alter your current database to work with Qlik Sense
Qlik Sense is CaSe SeNsiTiVe so Users does not equal users. This goes for the column names required below. You can also create aliases to conform to these requirements.

The user table requires the following fields and only those fields: userid and name.
The attributes table requires the following fields and only those fields: userid, type, value.

Once those elements are in place, you are ready to setup the UDC.

Go into QMC > User Directory Connectors > Create New > SQL (via ODBC) and create a new connection
Specify a name to the UDC; this is arbitrary
Uncheck Fetch user data on first access, then keep in sync if you want to pull in new users. If this is checked then you will only sync in users who currently already exist in the Users section of the QMC
Specify a User directory name; this is arbitrary but cannot contain spaces
For the User table name, specify the table (or view)
For the Attributes table name, specify the table (or view)
For the Visible connection string, specify either one of the following:
1. A full connection string, e.g. 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. You can check which driver to specify by loading up the Data Source (ODBC) applet from Windows and going to the Drivers tab
  2. Server must point to the server that wish to connect to
  3. Database must point to the database where the tables are
  4. Trusted_Connection=yes is required as well
2. A pointer to an established System DSN, e.g. dsn=MyDSN;

If you encounter an error message that "The User Directory Connector(UDC) is not configured, because the following error occurred: Exception when trying to connect to the data source. Edit the connection string in the user directory connector configuration" like so:

Then it is possible that some text was saved in the Encrypted connection string area. Try entering a semi-colon (;) and saving the UDC.

If you encounter further problems then the best log for troubleshooting will be the UserManagement Repository log found in C:\ProgramData\Qlik\Sense\Log\Repository\Trace\.

Re: How to setup a Sense User Directory Connector to a SQL Database

rajeshbheeman — Thu, 15 Oct 2020 02:24:11 GMT

Hi Andre,

I tried following the your steps above and couldn't get UDC created with SQL Server. Please find the details below.

Tables:

create table qlikuser(userid varchar(50), name varchar(300))

create table qlikuserattribute(userid varchar(50), type varchar(100), value varchar(300))

Inserts:

insert into qlikuser values('test.user', 'Test User')

insert into qlikuserattribute values('test.user', 'email', 'test.user@gmail.com')

Visible connection string:

Driver={SQL Server Native Client 11.0};Server="10.01.01.01";Database=TestDB;uid=testuser;pwd=testpw!2#;Trusted_Connection=yes;

testuser has db_admin privilege on TestDB

Attaching screenshot showing the same.

Could you please take a look and let me know what I am missing here? Appreciate your response/assistance on this.

Re: How to setup a Sense User Directory Connector to a SQL Database

Sonja_Bauernfeind — Wed, 21 Oct 2020 07:30:12 GMT

Hello @rajeshbheeman

I would recommend reviewing the log file as suggested in the article: C:\ProgramData\Qlik\Sense\Log\Repository\Trace\

Re: How to setup a Sense User Directory Connector to a SQL Database

rajeshbheeman — Wed, 21 Oct 2020 13:31:21 GMT

@Sonja_Bauernfeind Appreciate the response. I did look into the log but it was not much useful as it was generic error. However I was able to fix the issue. It turned out to be connectivity problem between the qlik instance with the DB server. I was able to create UDC successfully.

Re: How to setup a Sense User Directory Connector to a SQL Database

Xuser112 — Tue, 23 Nov 2021 14:39:30 GMT

@Andre_Sostizzo thanks for the information, I would like to know what are all the types in "type" column in user attributes table we can use?

There is no documentation provided.

Re: How to setup a Sense User Directory Connector to a SQL Database

Senor_Dai — Fri, 11 Mar 2022 16:53:15 GMT

Hi, has anyone done anything similar using AWS RDS Postresql DB?

Re: How to setup a Sense User Directory Connector to a SQL Database

GianlucaFerri — Fri, 11 Nov 2022 16:52:28 GMT

Hi, I managed to create an ODBC directory, but I don't get where/how to store the user passwords.

I tried creating a local user with the same name, but when the user attempts to login to qlik it then duplicates the user entries, and it doesn't get the previously assigned license.

The reason why I need an ODBC directory is that the customer cannot use LDAP and cannot require its users to try a login before adding licences.

What are the best practices in this case?

Thank you very much,

Gianluca

Re: How to setup a Sense User Directory Connector to a SQL Database

Sonja_Bauernfeind — Wed, 23 Nov 2022 08:06:32 GMT

Hello @GianlucaFerri

I would recommend posting your requirement in our Deployment and Management forums.

>>> I tried creating a local user with the same name, but when the user attempts to login to qlik it then duplicates the user entries, and it doesn't get the previously assigned license.

This can be explained as your server likely already has a connection to its local users set up and identified the user as such.

>>> The reason why I need an ODBC directory is that the customer cannot use LDAP and cannot require its users to try a login before adding licences.

Outline this requirement in our forums to make use of our userbase's experience and the knowledge of our engineers.

For more guided assistance, you can also contact our Professional Services: How and When to contact Consulting Services.

Some material I gathered for you that may help though is:

Authentication Methods (this lists avaiable methods with examples)
Available Authentication Solutions (this gives a list of the solutions available)

All the best,
Sonja

Re: How to setup a Sense User Directory Connector to a SQL Database

fmarvnnt — Tue, 29 Jul 2025 12:58:33 GMT

Qlik Sense Nov 2024 P14: I just remove the semi-colon at the ent of the "visible connection string" and the UDC re-started .

Quite strange.

FMs