Connecting Analysis Server with ASP

Question

Post reply

Connecting Analysis Server with ASP

bambang

SSChasing Mays

Points: 642
More actions
September 5, 2006 at 9:22 am

#173805

SQL Server 2000, ASP and OWC

Hi,

I have a problem in connecting Analysis Server: cube role with ASP. In our system, there is an OLAP report using cube, and user may access that cube through intranet. We are using ASP to build intranet, and for the cube interface we use Office Web Component (OWC). So, user can access from anywhere.

The problem is, I want to limit user access, so they can not see the whole report. For example, user from New York branch may not see other branch data. That's mean I have to disable the branch dimension other than New York dimension right? In analysis server we can do this easily by using Cube Role Manager. But how to use the setting in Cube Role Manager with ASP and OWC?

For Illustration:

User A from NY branch connect to intranet. From intranet, user A klik the link to OLAP report. When his/her already linked, he/she should enter the login id and password. After that, user can explore the whole OLAP report.

any suggestion? thx

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bnordberg SSCertifiable Points: 5379 More actions · Answer 1

How are you doing your connection? There is a a Roles variable you should be able to add to your string to enable Role security. For example, below is my connection string:

con.ConnectionString = "Location=" + server + ";Initial Catalog=" + database + ";Provider=MSOLAP.2;Roles=XXX";

At the end I add the Roles = and specify which cube roles to include.

Hope this helps

bambang SSChasing Mays Points: 642 More actions · Answer 2

bambang

SSChasing Mays

Points: 642

September 9, 2006 at 9:13 pm

#659513

Thx for your helps ^^.

bambang SSChasing Mays Points: 642 More actions · Answer 3

Once again thx for ur helps, bnordberg. This connection string works fine:

con.ConnectionString = "Location=" + server + ";Initial Catalog=" + database + ";Provider=MSOLAP.2;Roles=";

But there is one problem or something strange if u have multiple role. For example, i have cubeProduction that has 2 role. Each role correspond to a branch (eg. LA role means LA branch), and each role has multiple membership, right?

Let's say in LA role we have Anne, Tom, and Scott as the member and in NY role we have Brad, Jamie.

Now, the interesting part is: if Brad (from NY) login to OLAP report using Tom's user_id and password, Brad can access LA branch report. But, in the cube role manager for LA role, Brad is not in the membership list.

Anyone could explain to me why this happen? Why the membership role is not working?

bambang SSChasing Mays Points: 642 More actions · Answer 4

Here's the illustration ^^;

Brad(from NY) login to OLAP report through login page: he has to

fill out user_id and password.

(this login page correspond to a table in DB. The table consist of 3 col: user_id, password and role. Role column correspond to Role in Cube Role Manager).

Then, login page will get the role(from role col) and pass it to other page (eg. production page). Production page will use this 'user_role' as a parameter for "Roles:" in connection string, like i type above.

Thx for any explanation or may be solution

bnordberg SSCertifiable Points: 5379 More actions · Answer 5

bnordberg

SSCertifiable

Points: 5379

September 14, 2006 at 8:07 am

#660402

Glad I could help. Sorry I'm confused with Brad logging in with Tom's ID. If Brad uses Toms ID/pwd, then he will have Tom's access absolutly. What happens when Brad logs in as himself? Also do you have both/all roles defined in the Roles = ?

bnordberg SSCertifiable Points: 5379 More actions · Answer 6

No fair adding another post while I am typing my response to the first! So you are setting the Roles = as a variable, then doing a lookup on their account and passing the roles value back to this variable in the connection string? I guess I would echo back your connection string first to ensure that you have the roles truely going back to the variable correctly.

Also have you tested the roles on the cube to ensure you have your dimensional security setup properly? And I mean test in the AS cube browser, so your skipping any other potential issues.

bambang SSChasing Mays Points: 642 More actions · Answer 7

If Brad login with his ID, well than, that's exactly what i want . There will be no problem with that.

Nope, i defined Roles = . user_role is a variable, which get its value from the login page.

Here is what happen at login Page:

a. Brad has to type user_id and password.

b. This user_id and password is sent to Database, login_table.

Login_table has 3 column: user_id, password and role.

For Brad case: user_id = brad, password = 123, role = NY.

c. login page accept and send Brad's role (which is NY) to the other page.

Now, if Brad login using Tom (LA branch), that means login page will send Tom's role (which is LA). But, in the LA role membership list there is no Brad name. Oh yeah! I add this membership list from windows network.

So, Brad's ID in windows network is totally diferrent from Tom's ID, right? And also, Brad's ID is from different server from Tom's ID, but still in one network.

Oh my god, this is getting more confusing. I'm not good in explaining it >.<".

Hope u get the idea