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'
OR
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
