3 Replies Latest reply: Sep 17, 2012 6:04 AM by Thomas Recke RSS

    Configurable ODBC Directory Service Connector issues

      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