QlikView App login using Windows credential

[#1]

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

;

[Miguel_Angel_Baeyens]

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.

[#3]

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.

[Miguel_Angel_Baeyens]

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.

[#5 · Accepted solution]

Miguel,

thanks a lot for your help. In fact the only missing thing into our script was to activate the check box "Initial Data Reduction on Section Access"

Now our app is showing only the data belong to the logged user.

Again, thanks for your support!!!