4 Replies Latest reply: Sep 23, 2010 3:02 PM by Diego Adum RSS

    QlikView App login using Windows credential

      Hi Folks,

      I was playing around for a while with this subject without success. The idea is to avoid my users uses another user ID and Password since they are authenticated to my network agains a global domain using Windows Active Directory services.

      Beside to use SID on my Section Access, I don't know if we need to change something on our IIS.

      Below you will see the code that we are using on our test app, but when we open it from a user computer, there is no link between the security and the data. I mean, our goal is to show only the data belong to the logged user.

       

       

      star is *;

      section access;

      SET DBHOST="MXCUAUTI602P";

      SET DBNAME="PerfTrk";

      SET DBUSERID="sa";

      SET DBUSERPWD="ecolab2008";

      SET DBOWNER="$(DBNAME).dbo";

      CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;uid=$(DBUSERID);pwd=$(DBUSERPWD);Initial Catalog=$(DBNAME);Data Source=$(DBHOST);Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False];

      secValidLogonID:

      load distinct UPPER(@1) as @UID

      FROM C:\Apps\Qlik\Security\secSKPI.txt (ansi, txt, delimiter is ',', explicit labels)

      //FROM secSKPI.txt (ansi, txt, delimiter is ',', explicit labels)

      where UPPER(@1) <> 'ADMIN';

      // ********************************************************

      // *** Load User Logon Credentials (LogonID and Password)

      // ********************************************************

      secLogonInfo:

      load SERIAL,ACCESS,@UID,NTNAME,NTDOMAINSID where exists( @UID );

      SQL SELECT UPPER(SS.LogonID) as [NTNAME],

      UPPER(SS.Password) As [PASSWORD],

      '*' AS [SERIAL],

      'USER' as [ACCESS],

      UPPER(SS.LogonID) as [@UID],

      'S-1-5-21-3343834222-2031793820-3172701118' as NTDOMAINSID

      FROM $(DBOWNER).sysSecurity as SS

      LEFT JOIN $(DBOWNER).sysSecurityView as SSV

      ON (SS.LogonID = SSV.LogonID)

      WHERE SS.AcctActiveInd = 'Y' AND

      UPPER(SS.LogonID) <> 'ADMIN'

      GROUP BY SS.LogonID, SS.Password

      HAVING COUNT(SSV.LogonID) > 0;

      concatenate( secLogonInfo )

      load * inline [SERIAL,ACCESS,@UID,NTNAME,NTDOMAINSID

      Serial Number Info!!!

      ;

      drop table secValidLogonID;

      // ********************************************************

      section application;

      // ********************************************************

       

      x:

      load distinct @UID resident secLogonInfo;

      SecurityView:

      load distinct @UID, @SECKEY where @UID <> 'ADMIN' and exists( @UID );

      load UPPER(@1) as @UID, UPPER(@2&'_'&@3&'_'&@4&'_'&@5) as @SECKEY

      FROM C:\Apps\Qlik\Security\secSKPI.txt (ansi, txt, delimiter is ',', explicit labels);

      //FROM secSKPI.txt (ansi, txt, delimiter is ',', explicit labels);

       

      drop table x;

      // ********************************************************

      // *** Load User Information

      // ********************************************************

      secUsers:

      load @UID,NTNAME,[User Last Name],[User First Name],[User Name],[User Country],[User Role],SEC_VIEW,@SEC_TOKEN

      where exists ( @UID );

      SQL SELECT UPPER(SS.LogonID) as [@UID],

      SS.LogonID as [NTNAME],

      SS.LastName as [User Last Name],

      SS.FirstName as [User First Name],

      CASE LEN(ISNULL(LTRIM(RTRIM(SS.[FirstName])), '')) WHEN 0 THEN '' ELSE LTRIM(RTRIM(SS.[FirstName])) + ' ' END + LTRIM(RTRIM(SS.LastName)) as [User Name],

      SG.CountryName as [User Country],

      SSR.RoleName as [User Role],

      CASE UPPER(SS.ViewAllDataInd) WHEN 'Y' THEN 1 ELSE 0 END as [SEC_VIEW],

      SS.SecurityToken as [@SEC_TOKEN]

      FROM (($(DBOWNER).sysSecurity as SS

      LEFT JOIN $(DBOWNER).sysGlobal as SG

      ON SS.CountryCode = SG.CountryCode)

      LEFT JOIN $(DBOWNER).sysSecurityRole as SSR

      ON SS.RoleID = SSR.RoleID)

      LEFT JOIN $(DBOWNER).sysSecurityView as SSV

      ON (SS.LogonID = SSV.LogonID)

      WHERE SS.AcctActiveInd = 'Y' AND

      UPPER(SS.LogonID) <> 'ADMIN'

      GROUP BY SS.LogonID,SS.LastName,SS.FirstName,SG.CountryName,SSR.RoleName,SS.ViewAllDataInd,SS.SecurityToken

      HAVING COUNT(SSV.LogonID) > 0;

      concatenate( secUsers )

      LOAD * Inline [@UID,NTNAME,User Last Name,User First Name,User Name,User Country,User Role,SEC_VIEW,@SEC_TOKEN

      ;

       

        • QlikView App login using Windows credential
          Miguel Angel Baeyens de Arce

          Hello Diego,

          You may simplify your script using NTNAME as USERID in section access, and a reduction field that exists in your data, it may be rep name, some other key. In this case, your user will be logged into the document and the data will be reduced based on that reduction field, with no need to type user and password again.

          I'd suggest you to check this post. Instead of using USERID use NTNAME, since all your users are already members of the domain.

          Hope that helps.

            • QlikView App login using Windows credential

              Miguel, thanks for your reply.

               

              After made the changes that you mention, now the app is still asking my UserName and Password for open.

              Also, I've checked the post you mention but none of the examples shows how to work with NT domain authentication.

              In order to review the data used by the app on the NTNAME field, can I cath the data on the Section Access using a QVD file? I'm having the feeling that the NTNAME on my files (used to reduce data) are not the same as the NTNAME pass for the Domain system.

                • QlikView App login using Windows credential
                  Miguel Angel Baeyens de Arce

                  Hi Diego,

                  Usually, NTNAME is the name you use to log in your domain. So you need to change USERID field for NTNAME, and you don't have to load PASSWORD field (QlikView will look into the directory). Your NTNAME field will look like DOMAIN\USERNAME as used when logging into your computer.

                  Anyway, backup your files when using section access, because you can be locked out, and there's no way to get access again, if there's an error loading or whatever.

                  By the way, you actually can load section access from a QVD file, but make sure all fields are uppercase and load is done unoptimized, meaning

                   

                  LOAD ACCESS, NTNAME, REDUCTIONFIELD FROM SA.QVD WHERE 1=1;


                  Hope that helps.