security with sql server

[#1]

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...

[pover]

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.

[#3]

Thank you for reply,

i tried your solution,

but the problem is not solved.

There is something else we should change in the application?

[pover]

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.

[#5]

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..

[Miguel_Angel_Baeyens · Accepted solution]

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.

[pover]

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

LOAD Region as REGION,

...

[#8]

Thank you for your reply..

it is solved..

[#9]

Thank you for your reply.. it is solved.