Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Securing Reporting Services Reports Expand / Collapse
Author
Message
Posted Tuesday, May 18, 2010 6:40 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:34 AM
Points: 2,651, Visits: 5,990
gavindux (5/18/2010)
My preferred method to block user's from seeing other peoples data - especially sales and financial data, is using AD logins on the report server. The AD policy forces password changes regularly so its not that easy for others to get in with "other" credentials. My end-users are generally quite IT illiterate so I depend on AD to lock them down and keep out of each others figures. So far its worked well.

But yes, if one of them had to "steal" or guess a login then they would get in to that users data. Same goes for their email or windows profile and documents. Worst case (on SSRS) they get to see one other user's sales figures. The data source they're connecting through is using fixed credentials, with execute on a single SP, so that should be secure enough to block a "hacker" from getting further than that result set.

I didn't want to give them too many user names and passwords to fill in as it does mean more support work for their IT department.


Sorry, I was referring to the method presented in the article. There's no problem using the Global USerid to filter records on a report because that would require the user to know both username and password to actually log in as that user. I was referring to the use of a parameter to store the username and filter based only on the parameter as the only security method in place. With the method in the article I can open the website via a URL and add &rs:Command=Render&User=domain\userwithprivs and bingo I'm in. I'm thinking that a SOX or HIPPA (or insert your country's Laws that define auditing regulations) auditor may find some very large issues with this.

This is compounded by the use of domain login names as email addresses in some companies. If I know your email address, then I know your Username so it makes it very easy to defeat it. I now have every piece of information that I need to defeat the security model presented in the article.

-Luke.


To help us help you read this

For better help with performance problems please read this
Post #923521
Posted Tuesday, May 18, 2010 6:42 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 6:35 AM
Points: 748, Visits: 905
gavindux (5/18/2010)
My apologies - I misunderstood - I thought you meant when executing a report. When deploying that would be credentials of a user that is allowed to publish on the server. Usually a reportserver\admin account, or, sql server sysadmin account, or or whichever accounts have been granted publisher rights are required.


Sorry to trouble you more...how to find out which user has the access to that reports... as we are the admins to that box and also to the SQL Server. But from our account we are failing.


Thanks and Regards!!

Raghavender Chavva
Post #923524
Posted Tuesday, May 18, 2010 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 1:33 AM
Points: 30, Visits: 209
Forgetting about whether you can publish the report, are you able to login to the http://yourreportserver/reports site?
Post #923548
Posted Tuesday, May 18, 2010 7:13 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 19, 2014 6:47 AM
Points: 10, Visits: 255
Personally I'm not a fan of even allowing someone any access to the report directory if they don't have sufficient rights for all the reports in the directory. For rights management of report data in reports, we use SSAS databases with appropriate segregation of ActiveDirectory roles for each database component (dimension, cube, etc.) to limit the data each AD role returns to the reports.

In summary, users either have access to a report directory and all reports within the directory, or none at all.
Post #923553
Posted Tuesday, May 18, 2010 9:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 219, Visits: 626
Maybe I'm missing the point of this article. Why not use the native SSRS security model instead? Our shop relies upon it, in conjunction with the AD-based report user groups we've created to limit user access rights to specific folders and reports.

Rather than administering individual users' access rights to individual reports, without benefit of a programmatic interface, rights are administered at the group level, using web-based AD interfaces, such as Quest's ActiveRoles Server.
Post #923661
Posted Tuesday, May 18, 2010 9:56 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 11:02 AM
Points: 17,843, Visits: 15,792
Interesting solution to security for SSRS. I think I would weigh heavily the comments made in this discussion when looking to secure reports in SSRS.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #923699
Posted Tuesday, May 18, 2010 10:26 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, May 27, 2011 4:13 PM
Points: 19, Visits: 87
Nice article. We have hundreds of reports with tens of agencies accessing those reports restricted to only their data. My solution to this was creating a stored procedure which looked up their user id from this parameter and returned their coresponding agency. There was an agency table with a listing of agencies and users and their user ids.
Then in the dataset that returned the report data, only the data for agency to which the user belong was returned.
This allows many users from different agencies to view the same report, but with only their data.


(58.30115757480578, -134.4143772125244)
Post #923724
Posted Tuesday, May 18, 2010 11:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:07 AM
Points: 94, Visits: 111
Seems like a good way to hide protions of a report from specific people, but i dont' understand why you would hide an entire report when you can just hide it using SSRS's security.
Post #923759
Posted Tuesday, May 18, 2010 11:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 25, 2013 5:45 PM
Points: 5, Visits: 69
We implemented a solution that combines the features mentioned here, along with a table sub-struture and temporary login tokens.
The user is detected, we drill into the transactional data itself to determine if they should have access (as our security model is quite complex with multiple user roles and such), we enter a token along with userID into a table and that is the validation key.

It is all sproc driven and adds little overhead to the reports themselves once established.

The URL backtracking mentioned above is a key factor and is why we introduced the tokens.
If not the same user, and not within a 30 min period, the URL itself actually expires. (but easily regenerated in the background for any valid user on return - if needed)

There is some setup and coding, but it's a nice combination of the different topics mentioned in this article.


Good reading - thanks !
Post #923763
Posted Tuesday, May 18, 2010 11:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:43 AM
Points: 12, Visits: 71
This method is good for a small and stable pool of users and reports. However, we find it more practical to control access through AD groups. We are too short handed to administer additions and deletions of individual access for a couple thousand users against several hundred reports.
Post #923765
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse