Configurable ODBC Directory Service Connector issues

Mon, 26 Jan 2026 18:19:17 GMT

Hi, I've been struggling hard with trying to get the Configurable ODBC directory service authentication to fully work. Currently i can pull in users but I am not able to see groups.

We are using version 11 and we have created a view on our source system to pull the appropriate fields in the appropriate manner (from what i can tell).

When i look at the log I see this:

7/25/2012 10:33:19.4190937 Information Start web service call ResolveGroups for user QV\george clooney

7/25/2012 10:33:19.4347187 Information (Database.DatabaseProvider) rd have been successfully created

7/25/2012 10:33:19.4347187 Error (Database.DatabaseProvider) DB-search wasn't successful:

System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.

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 Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)

7/25/2012 10:33:19.4347187 Error (Database.DatabaseProvider) Exception 'System.Data.Odbc.OdbcException (0x80131937): ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.

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 Database.DatabaseProvider.DoSearch(String[] pattern, eSearchType[] searchTypes, String otherAttribute)

at Database.AbstractDSP.Search(String[] pattern, eSearchType type, String otherattribute)' when searching for 'Database.AbstractDSP+QueryBase'

7/25/2012 10:33:19.4347187 Warning (Database.DatabaseProvider) SearchCache lock inconsistency found (reader: False writer: True)

7/25/2012 10:33:19.4347187 Warning Resolving groups caused an exception ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'GROUP'.

Here is the SQL used to generate the 2 views:

User/Group view:

create view [sysdba].[QVENTITY] as (

SELECT distinct

cast(lower(u.userid) as nvarchar(30)) as entityid,

cast(lower(u.username) as nvarchar(30)) as entity_name,

cast(lower(u.username) as nvarchar(30)) as descr,

cast(lower(isnull(U.EMAIL,' ')) as nvarchar(30)) as email

from [A_DEV_75P3HF4].[sysdba].[USERINFO] u

union

SELECT distinct

cast(lower(roleid) as nvarchar(30)) as entityid,

cast(lower(rolename) as nvarchar(30)) as entity_name,

cast(lower(rolename) as nvarchar(30)) as descr,

cast(' ' as nvarchar(30)) as email

from [A_DEV_75P3HF4].[sysdba].ROLE

)

Membership view:

create view [sysdba].[QVUSERROLE] as (

SELECT distinct

cast(lower(u.userid) as nvarchar(30)) as memberid,

cast(lower(r.rolename) as nvarchar(30)) as groupid
FROM [A_DEV_75P3HF4].[sysdba].[ROLE] r
join [A_DEV_75P3HF4].[sysdba].[USERROLE] ur on ur.roleid=r.roleid

join [A_DEV_75P3HF4].[sysdba].[USERINFO] u on u.userid=ur.userid

)

This works fine.. by the way i grabbed this off someone elses post.. how did one peek at the query being used by Qlikview???

SELECT

e.entityid,

e.NAME,

e.descr,

e.email,

COUNT(g.memberid) AS noGroupMembers

FROM QVENTITY e
LEFT JOIN QVuserrole g ON e.entityid=g.groupid

WHERE

e.NAME = 'george clooney'

e.descr = 'george clooney'
OR

e.NAME = 'george clooney'
GROUP BY

e.entityid,e.NAME,e.descr, e.email

When I look at the data types in the view they are all nvarchar.

SELECT distinct

sql_variant_property(groupid,'BaseType'),

sql_variant_property(memberid,'BaseType')

FROM [A_DEV_75P3HF4].[sysdba].[QVUSERROLE]

SELECT distinct

sql_variant_property(entityid,'BaseType'),

sql_variant_property(entity_name,'BaseType'),

sql_variant_property(descr,'BaseType'),

sql_variant_property(email,'BaseType')

FROM [A_DEV_75P3HF4].[sysdba].[QVENTITY]

Here is the configuration (which seems to work fine, again, i can pull up a list of users without a problem):

<?xml version="1.0" encoding="utf-8"?><dscresource><id>c8c41604-6b55-463f-b7bf-a16b9bebd978</id><name>QV (ODBC://sql2k5-x64-01)</name><type>odbc</type><path>ODBC://sql2k5-x64-01</path><uid>fd4NDOdMx+E=</uid><pwd>eWOTXmlgo5XUOpy+s9TLDg==</pwd><StringDictionarySerializer><Entry Key="ServiceTimeoutInSeconds" Value="30" /><Entry Key="DirectoryLabel" Value="QV" /><Entry Key="EntityName" Value="entity_name" /><Entry Key="EntityTableDbName" Value="sysdba.QVENTITY" /><Entry Key="GroupsTableDbName" Value="sysdba.QVUSERROLE" /><Entry Key="DataSourceName" Value="SQL Server" /><Entry Key="ConnDbName" Value="A_DEV_75P3HF4" /><Entry Key="OverrideConnectionString" Value="" /><Entry Key="CacheExpiryInMinutes" Value="15" /></StringDictionarySerializer></dscresource>

Is there a bug in qv 11 with configurable ODBC? I wouldn't think it should be this difficult.

Thanks in advance,

Chris

Re: Configurable ODBC Directory Service Connector issues

Thu, 26 Jul 2012 19:12:44 GMT

Chris, when you say you are not able to see groups, do you mean in QEMC Users tab?

Re: Configurable ODBC Directory Service Connector issues

Mon, 30 Jul 2012 17:46:48 GMT

Yes exactly, thanks Ralph, I can see the users I have brought in using the configurable ODBC but when i try to view a groups listing I don't see the groups I am trying to utilize per the query and it gives the log entry I pasted. It looks like there is a problem with a string not getting single quotes when the query is constructed on the qv server.

Thanks,

Chris

Re: Configurable ODBC Directory Service Connector issues

Mon, 17 Sep 2012 10:04:40 GMT

Hi Chris,

I got the same error using Configurable ODBC DSC connecting to MySQL.

I changed the type of the keys entityid, groupid and memberid from string to int - and everything works fine!

Got that hint from http://community.qlik.com/message/162432:

"Keep in mind, for group resolution to work at this time, the entityid field of your entity table, and the memberid and groupid fields in the group table cannot contain values greater than max(int), or 2.1 billion, because there is a bug in the group resolution code that doesn't allow numbers larger than that size to be resolved."

Thomas.

topic Re: Configurable ODBC Directory Service Connector issues in QlikView

Configurable ODBC Directory Service Connector issues

Re: Configurable ODBC Directory Service Connector issues

Re: Configurable ODBC Directory Service Connector issues

Re: Configurable ODBC Directory Service Connector issues