users can see only their data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

users can see only their data

Author

Topic

sundog
Starting Member

4 Posts

Posted - 2006-01-06 : 23:05:02

I'm working on an ASP.Net application that displays Reporting Services reports in the application's browser window. The calls to the reports are generated from C# code and URLs. Here's an example of a call to the report server:

<code>

Response.Write("<script>parent.frames('mainFrame').location = 'https://jnuaobdev1/ReportServer?/Sandboxes/kdsmith/Reminders&rs:Command=render&UserName=" + strUserName + "&UserPwd=" + strUserPwd + "';</script>");

</code>

Each user should be able to see only his group's data. The datasets are based on stored procedures that require parameters for the username and password. So, the question:

How can I pass the username and password to the report without exposing them in the URL querystring?

Thanks for any assistance

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-01-06 : 23:18:21

why don't you setup up database roles instead. This way you don't have to use a stored procedure and pass it users/passes (very bad idea btw). You are trying to implement a feature that is already a fundamental feature of every modern database engine.

If you are running in a domain environment and your end users can use windows authenticaiton to connect to the database then using db roles is even simpler. You can simply create AD groups and associate these groups with the different roles you have created in the database. Giving people access to your database is as simple as having someone added to the AD group.

Using windows authentication with a web app can be a little more difficult, but if you can get that running the added benefit of being able to use the AD groups will really make administration easy.

-ec

sundog
Starting Member

4 Posts

Posted - 2006-01-11 : 14:59:14

quote:
Originally posted by eyechart

why don't you setup up database roles instead. This way you don't have to use a stored procedure and pass it users/passes (very bad idea btw). You are trying to implement a feature that is already a fundamental feature of every modern database engine.

Thanks for the suggestions, but we have to use forms authentication for this web app. And we plan to use roles, but it's not clear to me how to authenticate a request to the database without sending the user's credentials along with the request.

/Ken

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-11 : 15:03:41

Ken,

We are in the same boat as you: having to use Forms Authentication for the reports. MS has told us that the answer to your question (which we asked yesterday) was to use parameterized views, which I guess means UDFs. We haven't implemented it yet.

Tara Kizer
aka tduggan

sundog
Starting Member

4 Posts

Posted - 2006-01-11 : 16:24:11

quote:
Originally posted by tkizer

MS has told us that the answer to your question was to use parameterized views

Tara Kizer
aka tduggan

I don't understand how it makes a difference if you use a stored proc or UDF. The main problem, I think, is how to pass the user's credentials to RS without exposing them on the web.

I guess an option would be to do some type of insert into the db values that would then be referenced by the stored proc/UDF. But I'm hoping there's a better way.

Thanks for the response. I've never communicated with an acutal goddess before. It's a pleasant thing to consider. I didn't see your post from yesterday; was it on this forum?

/Ken

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-11 : 16:26:09

I didn't understand their solution either. We have entered a support ticket with them to get this going as we are in a time crunch for that application.

Tara Kizer
aka tduggan

sundog
Starting Member

4 Posts

Posted - 2006-01-11 : 16:49:05

Please share the info if you can.
Thanks
/Ken

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-01-12 : 03:32:31

I'd be interested in any response to this too. Good luck Tara!

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-12 : 12:04:32

I'm not the one specifically working on it here. If I here that the other person finally got it working, I'll inquire about the details. I've been involved with the meetings, so that's why I know some details.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-01-12 : 16:24:23

I dont see any reason for a UDF to work much like the sproc solution.
What is it you are having an issue exactly with. You know you can hide the resulting URL so as to not show the parameters or have users change them.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-12 : 16:30:14

Jon,

Are you using Forms Authentication? If so, then perhaps you could help us out on this. The issue is that our users aren't Active Directory users, so we have to go down the Forms Authentication route according to MS.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-01-12 : 16:40:24

quote:
Originally posted by tkizer

Jon,

Are you using Forms Authentication? If so, then perhaps you could help us out on this. The issue is that our users aren't Active Directory users, so we have to go down the Forms Authentication route according to MS.

Tara Kizer
aka tduggan

Forms Authentication regarding like your web app? I am using forms authentication

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-12 : 16:49:26

Forms Authentication in Reporting Services. It's what you use when your users aren't Active Directory users. The MS guy said there was a sample report that used that method, although I haven't looked to confirm.

Tara Kizer
aka tduggan

jhermiz

3564 Posts

Posted - 2006-01-12 : 16:51:07

Tara,

I know you said you are not working on this part of the application.
But send this link over to that developer, it saved me a lot of time.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

jhermiz

3564 Posts

Posted - 2006-01-12 : 16:55:12

quote:
Originally posted by tkizer

Forms Authentication in Reporting Services. It's what you use when your users aren't Active Directory users. The MS guy said there was a sample report that used that method, although I haven't looked to confirm.

Tara Kizer
aka tduggan

Here also is the sample for you:

http://download.microsoft.com/download/a/b/0/ab01e0ba-e9bb-4c3d-aa82-35d6e2916dab/FormsAuthenticationSample.msi

Jon

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-12 : 16:55:35

Thanks Jon! I'm sending it now.

Tara Kizer
aka tduggan

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-01-13 : 03:36:20

I hadn't seen those before. Thanks Jon

steve

-----------

Facts are meaningless. You could use facts to prove anything that's even remotely true!

wall
Starting Member

4 Posts

Posted - 2006-03-28 : 18:46:08

Hello.

I am hoping that someone can help me with this. We (also) are using forms authentication with Reporting Services 2005. The forms auth works very well when accessing the ReportManager website or requesting a report via URL from the ReportServer website.

But we need to display a list of reports (filtered for the authenticated user) and create our own report parameters form for users to select run-time values for a selected report. This involves calling web methods in the Reporting Services web services - for example, ListChildren(), GetReportParameters(). This works nicely with Windows authentication.

But with forms authentication, I am having a problem getting the Reporting Services web methods to run.

When my code attempts to execute one of these web methods I get an error that reads:

<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/ReportServer/logon.aspx?ReturnUrl=%2freportserver%2fReportExecution2005.asmx">here</a>.</h2>
</body></html>

I have a week old incident with Microsoft and it is difficult to get their time. Our Reporting Services project has come to a grinding halt.

Can anyone help me with this?

Thank you, thank you.

gamerguy76
Starting Member

1 Post

Posted - 2006-05-26 : 12:26:23

<html><head><title>Object moved</title></head><body>
<h2>Object moved to <a href="/ReportServer/logon.aspx?ReturnUrl=%2freportserver%2fReportExecution2005.asmx">here</a>.</h2>
</body></html>

I get that exact same error. Anyone find a resolution?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources