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

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