topic security with sql server in QlikView

security with sql server

Thu, 23 Sep 2010 06:18:00 GMT

Hello,

When i want to achieve the security with excel file it is possible with personal edition,

but when i make the same structure of table with the sql server then it cannot accept the user name which i already given in the sql server data table.

===================================

Section Access; // Start of section access part

Directory;

LOAD [USERID],

[PASSWORD],

[ACCESS],

[GROUP]

FROM

SECURE.xls

(biff, embedded labels, table is Sheet1$);

Section Application; // End of section access part

Directory;

LOAD [GROUP],

SHEET1,

SHEET2,

SHEET3

FROM

GROUP.xls

(biff, embedded labels, table is Matrix$);

=======================================

this code for the excel file...

and the below code when i use the sql server data table to achieve the same.

===========

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=BISRVR;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISRVR;Use Encryption for Data=False;Tag with column collation when possible=False];

Section Access;

Directory;

LOAD [USERID], [PASSWORD], [ACCESS], [Region];

SQL SELECT * FROM Test.dbo.SECURE;

Section Application;

Directory;

LOAD [Region], "Sales Data 1", "Sales Data 2", "Sales Data 3";

SQL SELECT * FROM Test.dbo.Regions;

===========

Prob Description: when i use excel as a table it accept the username and password, but when i use sql server tables, then it cannot accepts the user name and it says that access denied, it will ask three time to enter perfect user name and then show

pls help...

security with sql server

pover — Thu, 23 Sep 2010 06:55:41 GMT

All column names in Section Access should be in capital letters, so Region should be REGION.

Also, I've sometimes had problems with the value in the ACCESS column. Try fixing it like this 'ADMIN' as ACCESS to see if that fixes the problem and if it does we'll have to investigate it way it might cause problems.

Regards.

security with sql server

Thu, 23 Sep 2010 10:31:41 GMT

Thank you for reply,

i tried your solution,

but the problem is not solved.

There is something else we should change in the application?

security with sql server

pover — Thu, 23 Sep 2010 16:05:46 GMT

You might also want to verify that there is no null or empty value in any of the columns.

Password is optional so try your security with USERID and ACCESS first to see if you still have a problem and limit your search for the problem.

Regards.

security with sql server

Sat, 25 Sep 2010 07:10:14 GMT

Dear,

thanks for reply..

i check with table.. it does not have any empty cell..

i m attaching the whole steps with images.. so u can easily understand..

=====================================

CONNECT TO [Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=BISERVER;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BISERVER;Use Encryption for Data=False;Tag with column collation when possible=False];

Section Access;

LOAD USERID,

PASSWORD,

ACCESS,

REGION;

SQL SELECT *

FROM Test.dbo.SECURE;

Section Application;

LOAD Region,

"Sales Data 1",

"Sales Data 2",

"Sales Data 3";

SQL SELECT *

FROM Test.dbo.Regions;

==============================

pls help..

security with sql server

Miguel_Angel_Baeyens — Sat, 25 Sep 2010 08:48:02 GMT

Hello,

Capitalize your fields when you load them:

LOAD UPPER(USERID) AS USERID, UPPER(PASSWORD) AS PASSWORD, UPPER(ACCESS) AS ACCESS, UPPER(REGION) AS REGION;SQL SELECT *FROM Test.dbo.SECURE;

Uncheck "Reduce Data based on section access" in your settings menu, Document Properties, On Open tab.

Regards.

security with sql server

pover — Mon, 27 Sep 2010 06:41:45 GMT

Also, the column Region in the "Section Application" should be REGION. So create a new column like this:

LOAD Region as REGION,

...

security with sql server

Mon, 27 Sep 2010 08:29:19 GMT

Thank you for your reply..

it is solved..

security with sql server

Mon, 27 Sep 2010 08:29:30 GMT

Thank you for your reply.. it is solved.