Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Aug 24, 2020 7:22:07 AM
May 5, 2017 10:40:33 AM
Trying to synchronize users by creating a new User Directory Connector of type Excel (via ODBC) fails. The user after creating the directory connector sees message in yellow in the Edit section of the User Directory connector.
The connection is created but it does not work.
Error printed on screen is:
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.
The User Direcotyr Connector (UDC) is not operational.
The Qlik Sense User Management Repository logs read:
Repository.UserDirectoryConnectors.ODBC.OdbcExcel ERROR [42000] [Microsoft][ODBC Excel Driver] Undefined function 'LOWER' in expression
The error may be triggered after an upgrade.
Recreating the User Directory connection causes the same error.
Environment:
Cause:
The (Excel via ODBC) connector is deprecated and there are plans to remove it. However we do not know when that will be removed from the available User Directory Connectors. The other issue is that the Microsoft Excel Drivers are not updated too often so some incompatibilities might occur. Please always advise to use the ODBC User Directory Connector when loading users and attributes from Excel.
Resolution:
In order to solve this issue please check the following:
- Check if the connection is correctly set up:
- check if the names of the User Directory are correct
- check if the name of user table and attributes table are correct. See the Qlik Sense online help for details.
***Make sure that the check box of "Sync user data for existing users" is NOT checked***
If this box is checked you will get the ERROR [42000] [Microsoft][ODBC Excel Driver] Undefined function 'LOWER' in expression.
- Is the Visible connection string is correct
- Go to server and check the ODBC Data Source Administrator
- check if the Microsoft Access Driver is of version: 14.00.7010.1000 from 2013.02.14
- If not, please upgrade the driver from by downloading the latest version from the following link:
https://www.microsoft.com/en-us/download/details.aspx?id=54920 ( for 2016 )
- Upgrade the driver
- Restart Sense Services
- Remove the previously created connection
- Create new connection
