Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rob_insley
Partner - Contributor III
Partner - Contributor III

Distributed document fails section access

We have set up a 2 server Publisher / QVS Architecture, and have migrated this from a simple working one server architecture.

Source Documents Folder REsides on Publisher, User Documetns  Folder resides on QVS

If I copy and place the reloaded source document directly in the USer Documents Folder, the document can be opened in Access Point.  However when I add a distribute task to distribute the document to the USer Documents Folder,  when trying to access this document via Access Point, section access appears to fail as I am greeted with the USer Id Password Prompt.

I cannot seem to get this to work, which is baffling me.   Expected NTName and USER_NAME exist in the document  and this is verified by being able to open the Source Document. 

Any suggestions to what could be going wrong.  I have set this up at several other clients without issue, not sure what could be incorrect.

9 Replies
pradosh_thakur
Master II
Master II

Hi Robert

can you post your section acess script?

some thing if you have to keep in mind

1: Everything n section access  should be capital for not getting crazy results.

2: Have a back up.

3: Add your used as admin and user perfetly

last but most important

4: Have you added the"SERVICE ACCOUNT" user as your ADMIN beacause it is under this user app gets reloaded in QMC .

Most of the time when we don't add service account in our section access this kind of problem arises.

regards

Pradosh

Learning never stops.
Peter_Cammaert
Partner - Champion III
Partner - Champion III

Did you let the Publusher just distribute the document while it was reloaded on another platform, or did you do a reload and distribute on the server after which it fails to open in the AP?

Opening a document with section access in QV Desktop is always possible if your current account is listed with ACCESS=ADMIN in Section Access. Whether you enabled Data Reduction or not.

rob_insley
Partner - Contributor III
Partner - Contributor III
Author

Thanks for your response, Pradosh

The section access considers everyone of your points.

UPPERCASE, ADMIN and User.

SERVICE Account added into Section Access loaded as admin.  (If you don't do this then you cannot reload, unless you add SECTION ACCESS entry into the QMC Dcoument REload Tab.  I have tried this with and without and doesn;t make a difference as we have <<PUB_SERVER>>\QV_ADMIN and <QVS_SERVER>>\QV_ADMIN  loaded into the Section Access as ADMIN

So what appears to be happening is this.

The Source Document is reloaded using local account <<PUB_SERVER>>\QV_ADMIN and resides on the Publisher Server.  If I copy this document  to the Access Point Folders on the QVS Server (just doing a file copy, it can be opened no problem and as expected using the local Account <QVS_SERVER>>\QV_ADMIN    This issue lies when I distribute it using Publisher Distribute to Server or Distribute to Folder.  In this instance the document cannot be opened in the Access Point (using <QVS_SERVER>>\QV_ADMIN  )  or using QV Desktop from <QVS_SERVER>>\QV_ADMIN.

If I then move this distributed document back to the Publisher Server I can open it in Desktop using the <<PUB_SERVER>>\QV_ADMIN account.

Therefore I am concluding that somehow the document is beign reduced during distribute so that the only entry in Section Access is <<PUB_SERVER>>\QV_ADMIN, preventing any other access.

I have never noticed this behaviour on  other clients so am baffled by it.

pradosh_thakur
Master II
Master II

Can you post your section access code here.

We can have a look and try to find out what could be the casue.

regards

Pradosh

Learning never stops.
rob_insley
Partner - Contributor III
Partner - Contributor III
Author

TempSectionAccess:

LOAD

    UPPER(ACCESS1) AS ACCESS,

    UPPER(USERID) AS USERID,

    UPPER(OS_USER) AS OS_USER,

    UPPER(USER_NAME) AS USER_NAME,

    UPPER(USER_NAME) AS USER_NAME_PAY,

    UPPER(PASSWORD) AS PASSWORD

;

SELECT

       decode(usertable.user_name,'SYSADMIN','ADMIN'

                       ,'USER') access1

      ,usertable.user_name

      ,upper(fv.flex_value) AS OS_USER

      ,'*' AS USERID

      ,'*' AS PASSWORD

FROM fnd_user usertable

,    fnd_flex_values fv

,    fnd_flex_values_tl fvt

WHERE user_id not in (-1,2,3,4,5,6,7)

AND usertable.user_name = fvt.description

AND fv.flex_value_id = fvt.flex_value_id

AND fv.enabled_flag = 'Y'

AND sysdate between nvl(fv.start_date_active, sysdate-1)

                and nvl(fv.end_date_active, sysdate+1)

AND fv.flex_value_set_id in (

   select flex_value_set_id

   from fnd_flex_value_sets

   where flex_value_set_name = 'XXCUST_QV_USER_MAP_FA')     

UNION

SELECT 'ADMIN', 'SYSADMIN', 'DCAL-QKVPB-01\QV_ADMIN', '*', '*'

FROM DUAL

UNION

SELECT 'ADMIN', 'SYSADMIN', 'DCAL-QKVAP-01\QV_ADMIN', '*', '*'

FROM DUAL;

SECTION ACCESS;

//Note in section access usernames will be uppercase

//////////////

LOAD

    ACCESS,

    USERID,

    PASSWORD,

    UPPER(OS_USER) AS NTNAME,

    USER_NAME,

    USER_NAME_PAY

RESIDENT TempSectionAccess;

pradosh_thakur
Master II
Master II

Hi Robert

Please have a back up and try this code out

star is *;

TempSectionAccess:

LOAD

    UPPER(ACCESS1) AS ACCESS,

    UPPER(USERID) AS USERID,

    UPPER(OS_USER) AS OS_USER,

    UPPER(USER_NAME) AS USER_NAME,

    UPPER(USER_NAME) AS USER_NAME_PAY,

    UPPER(PASSWORD) AS PASSWORD

;

SELECT

       decode(usertable.user_name,'SYSADMIN','ADMIN'

                       ,'USER') access1

      ,usertable.user_name

      ,upper(fv.flex_value) AS OS_USER

      ,'*' AS USERID

      ,'*' AS PASSWORD

FROM fnd_user usertable

,    fnd_flex_values fv

,    fnd_flex_values_tl fvt

WHERE user_id not in (-1,2,3,4,5,6,7)

AND usertable.user_name = fvt.description

AND fv.flex_value_id = fvt.flex_value_id

AND fv.enabled_flag = 'Y'

AND sysdate between nvl(fv.start_date_active, sysdate-1)

                and nvl(fv.end_date_active, sysdate+1)

AND fv.flex_value_set_id in (

   select flex_value_set_id

   from fnd_flex_value_sets

   where flex_value_set_name = 'XXCUST_QV_USER_MAP_FA') ;

   

load * inline[

ACCESS,

    USERID,OS_USER,USER_NAME,USER_NAME_PAY,PASSWORD

    ADMIN, SYSADMIN,DCAL-QKVPB-01\QV_ADMIN,*, *

    ADMIN, SYSADMIN,DCAL-QKVAP-01\QV_ADMIN,*, *

];

SECTION ACCESS;

//Note in section access usernames will be uppercase

//////////////

LOAD

    ACCESS,

    USERID,

    PASSWORD,

    UPPER(OS_USER) AS NTNAME,

    USER_NAME,

    USER_NAME_PAY

RESIDENT TempSectionAccess;

regards

Pradosh

Learning never stops.
rob_insley
Partner - Contributor III
Partner - Contributor III
Author

Hi Pradosh,

I don;t believe this will work. In your inline load, you load SYSADMIN Into OS_USER.    THE OS USer is DCAL-QKVPB-01\QV_ADMIN.

PradoshI apprecite your help on this but  if the Section Access was the problem, then I am sure the scenario where I load the Source Document to the Uer Folder (By CMD Line Copy rather than distribute) wouldn;t work either  but it works perfectly as I would expect.

Currently as a workaround, nce I have reloaded the document I have created a BAT file and call this from QMC. This BAT File copies the Reloaded Source Document to the User Folder.  In this way users can access the document.

pradosh_thakur
Master II
Master II

Hi Robert

sorry i just noticed that
can you try this . I believe it is not recognizing the service account user at all. Please adjust the inline load to suit your requirement.

star is *;

TempSectionAccess:

LOAD

    UPPER(ACCESS1) AS ACCESS,

    UPPER(USERID) AS USERID,

    UPPER(OS_USER) AS OS_USER,

    UPPER(USER_NAME) AS USER_NAME,

    UPPER(USER_NAME) AS USER_NAME_PAY,

    UPPER(PASSWORD) AS PASSWORD

;

SELECT

       decode(usertable.user_name,'SYSADMIN','ADMIN'

                       ,'USER') access1

      ,usertable.user_name as USER_NAME

      ,upper(fv.flex_value) AS OS_USER

      ,'*' AS USERID

      ,'*' AS PASSWORD

FROM fnd_user usertable

,    fnd_flex_values fv

,    fnd_flex_values_tl fvt

WHERE user_id not in (-1,2,3,4,5,6,7)

AND usertable.user_name = fvt.description

AND fv.flex_value_id = fvt.flex_value_id

AND fv.enabled_flag = 'Y'

AND sysdate between nvl(fv.start_date_active, sysdate-1)

                and nvl(fv.end_date_active, sysdate+1)

AND fv.flex_value_set_id in (

   select flex_value_set_id

   from fnd_flex_value_sets

   where flex_value_set_name = 'XXCUST_QV_USER_MAP_FA') ;

  

load * inline[

    ACCESS,USERID,OS_USER,USER_NAME,USER_NAME_PAY,PASSWORD

    ADMIN,*,DCAL-QKVPB-01\QV_ADMIN,*, *,*

    ADMIN,*,DCAL-QKVAP-01\QV_ADMIN,*, *,*

];

SECTION ACCESS;

//Note in section access usernames will be uppercase

//////////////

LOAD

    ACCESS,

    USERID,

    PASSWORD,

    UPPER(OS_USER) AS NTNAME,

    USER_NAME,

    USER_NAME_PAY

RESIDENT TempSectionAccess;

regards

Pradosh

Learning never stops.
rob_insley
Partner - Contributor III
Partner - Contributor III
Author

Hi Peter,

The tasks were :

1) Reload document on the QV Publisher Server

2) Distribute to Mounted Folder in QVS.

I have tried this in one task ,and also two task, (Spilt reload and Distribute).

I undestand about your point "Opening a document with section access in QV Desktop is always possible if your current account is listed with ACCESS=ADMIN in Section Access. Whether you enabled Data Reduction or not."

And this is consistent with my thoughts that somewhere in the distribute, data reduction is happening., because

a) I cannot access this User  document using QV Desktop with USer QVS_SERVER\QV_ADMIN

b) I can access this User document if I copy to Pulbishher Server and open it using <<PUB_SERVER>>\admin which is the service account, then it opens.

The other thing to add that ofcorse may affect are the Doument Settings

Initial Data REduction Based On Section Access is checked

Strict exclusion  is checked.