Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

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

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Andre_Sostizzo
Digital Support
Digital Support

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

Last Update:

Sep 2, 2020 7:46:00 AM

Updated By:

Sonja_Bauernfeind

Created date:

Nov 9, 2015 9:49:00 AM

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.
 

  1. Go into QMC > User Directory Connectors > Create New > SQL (via ODBC) and create a new connection
  2. Specify a name to the UDC; this is arbitrary
  3. 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
  4. Specify a User directory name; this is arbitrary but cannot contain spaces
  5. For the User table name, specify the table (or view)
  6. For the Attributes table name, specify the table (or view)
  7. 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:
 

 User-added image
 

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\.

Labels (1)
Comments
rajeshbheeman
Contributor
Contributor

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. UDC_Error.PNG

 

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

 

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @rajeshbheeman 

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

 

 

rajeshbheeman
Contributor
Contributor

@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. 

Xuser112
Contributor II
Contributor II

@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.

Senor_Dai
Partner - Creator II
Partner - Creator II

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

GianlucaFerri
Partner - Contributor II
Partner - Contributor II

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

Sonja_Bauernfeind
Digital Support
Digital Support

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 

 

 

Contributors
Version history
Last update:
‎2020-09-02 07:46 AM
Updated by: