Issue with ODBC user directory connector

[Anonymous]

Hi all,

I have set up an ODBC UDC. It used to work fine, then suddenly it stopped working. checked the support page https://help.qlik.com/en-US/sense/June2017/Subsystems/ManagementConsole/Content/ODBC-example.htm

I verified that the ODBC Access Text Driver is installed. When I refer to the logs, I see a whole bunch of errors...

Any idea what could be causing this issue?

My guess is the server got hardened and something stopped working?

Sample logs as shown below.

211 20180925T102548.430+0800 ERROR HOSTNAME UserManagement.Repository.Repository.Users.Factories.UserDirectoryFactory ... DO\acct ERROR [42000] [Microsoft][ODBC Text Driver] Undefined function 'LOWER' in expression.    ...
212 20180925T102548.430+0800 WARN HOSTNAME UserManagement.Repository.Repository.Users.SyncUsersWorker ... DO\acct Couldn't retrieve users from directory: 'DO' of type Repository.UserDirectoryConnectors.ODBC.ODBC ERROR [42000] [Microsoft][ODBC Text Driver] Undefined function 'LOWER' in expression. ↵↓Server stack trace: ↵↓   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)↵↓   at Repository.UserDirectoryConnectors.ODBC.DataBaseConnector.SyncUsers(String[] usersToFilterOn)↵↓   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)↵↓   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)↵↓↵↓Exception rethrown at [0]: ↵↓   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)↵↓   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)↵↓   at System.Func`1.EndInvoke(IAsyncResult result)↵↓   at Repository.Users.SafeUserDirectoryConnector.CallWithTimeout(Func`1 func, TimeSpan timeout)↵↓   at Repository.Users.SafeUserDirectoryConnector.SyncUsers(String[] usersToFilterOn)↵↓   at Repository.Users.SyncUsersWorker.FetchUdcEntities(IUserDirectoryConnector udc, String ud, String[] usersToFilterOn)↵↓   at Repository.Users.SyncUsersWorker.RetrieveUdcEntities(IUserDirectoryConnector udc, String ud, String[] onlyOldUsers, String udcFullName)  ...
213 20180925T102548.462+0800 ERROR HOSTNAME UserManagement.Repository.Repository.Users.SyncUsersWorker ... DO\acct Exception when fetching data from 'DO' of type Repository.UserDirectoryConnectors.ODBC.ODBC ERROR [42000] [Microsoft][ODBC Text Driver] Undefined function 'LOWER' in expression.↵↓Couldn't retrieve users from directory: 'DO' of type Repository.UserDirectoryConnectors.ODBC.ODBC ↵↓Server stack trace: ↵↓   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)↵↓   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)↵↓   at Repository.UserDirectoryConnectors.ODBC.DataBaseConnector.SyncUsers(String[] usersToFilterOn)↵↓   at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)↵↓   at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)↵↓↵↓Exception rethrown at [0]: ↵↓   at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)↵↓   at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)↵↓   at System.Func`1.EndInvoke(IAsyncResult result)↵↓   at Repository.Users.SafeUserDirectoryConnector.CallWithTimeout(Func`1 func, TimeSpan timeout)↵↓   at Repository.Users.SafeUserDirectoryConnector.SyncUsers(String[] usersToFilterOn)↵↓   at Repository.Users.SyncUsersWorker.FetchUdcEntities(IUserDirectoryConnector udc, String ud, String[] usersToFilterOn)↵↓   at Repository.Users.SyncUsersWorker.RetrieveUdcEntities(IUserDirectoryConnector udc, String ud, String[] onlyOldUsers, String udcFullName)↵↓   at Repository.Users.SyncUsersWorker.RetrieveUdcEntities(IUserDirectoryConnector udc, String ud, String[] onlyOldUsers, String udcFullName)↵↓   at Repository.Users.SyncUsersWorker.<>c__DisplayClass11_1.<SaveUsers>b__0(KeyValuePair`2 pair)  ...

[Levi_Turner]

This is an old thread, but too often things are resolved by people and there isn't follow up to close out things.

At least for me:

• Driver: 64 version here (https://www.microsoft.com/en-us/download/details.aspx?id=54920)
• Fix: Switch from Excel (via ODBC) to Access (via ODBC)