17 Replies Latest reply: Aug 16, 2017 7:25 PM by Arnaldo Sandoval RSS

    Fetch users from Active Directory in Sense script.

    Anders Florentin Hansen

      Hi there,

       

      I have not been able to fetch users from Active Directory in a Qlik Sense script via the OLE DB provider for Microsoft Directory Services. I am able to do it in Qlikview with the following script:

       

      OLEDB CONNECT32 TO [Provider=ADsDSOObject;Encrypt Password=False;Integrated Security=SSPI;Mode=Read;Bind Flags=0;ADSI Flag=-2147483648];

      LET vLDAP_UNC = 'LDAP://*****.COM';
      LET vUsername=chr(01); // Set search start name to a very low value

      DO

      ADUsers:
      FIRST 901  // Workaround for undefined SQL error. Uncomment this line if you are getting the error
      LOAD
        name     as userName,
        distinguishedName  as userDN,
        sAMAccountName  as userID,
        company,
        department,
        OU     as orgUnit,
        c     as country,
        '$(vUsername)'   as vUsername
      ;
      SQL SELECT
        name,
        distinguishedName,
        sAMAccountName,
        company,
        department,
        OU,
        c
      FROM
        '$(vLDAP_UNC)'
      WHERE
        SAMAccountName >'$(vUsername)'
      ;

      EXIT DO WHEN ScriptError > 1; // Stop loop if SELECT has error
      EXIT DO WHEN NoOfRows('ADUsers') = 0;  // Stop loop if SELECT returns nothing
      EXIT DO WHEN peek('userID') = '$(vUsername)';  // If the last "name" read is EQ to arg -- no more entries

      LET vUsername = peek('userID'); // Set the arg to the last "name" read
      LOOP

       

      In Sense I have to create a connection... All I type as source is LDAP://<domain>.com ...

       

      How do I go on from here? Does anyone know?

       

      Please notice that I cannot switch to legacy mode, because this will affect all apps on the server.