SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mohamed IDTTALBE / Securing Reporting Services Reports / Latest Posts

RE: Securing Reporting Services Reports

amit_adarsh — Fri, 21 May 2010 07:38:49 GMT

By this way you are not able to hide this report by user Because when user log he will be able to see all the reports name(menu) and when he click on the report it will show your customized message or error .But Best we to deal with these types of issue is "Hide this report from menu" because he has no permission to see this report .you can do it by using report manager.

RE: Securing Reporting Services Reports

amit_adarsh — Fri, 21 May 2010 07:16:08 GMT

HI Hust try these steps 1) Double click on data source 2) select credentials and then fill the required user info like windows authonication or other way if its ok then you have to check previlages on the system where you want to deploy your reports .

RE: Securing Reporting Services Reports

JP-470 — Wed, 19 May 2010 11:11:12 GMT

We also have an "ACL", but it provides access control through the main application rather than Report Manager. These users see an interface which captures their location and ID to assess their authorization to access portions of the application and limit the data viewed in standard reports : facility; region; all; facility and region cross-over; HR; admissions; billings; add; edit; view; etc.However we have a group of non-IT users who are empowered to write their own adhoc and standard reports that may or may not be made accessible through the applications and may have a different audience than the application users. This group has use of development tools, deploy their own reports and access Report Manager directly. This group is sub-divided into various organizational units as they see fit, which correspond to AD groups: each group then is granted control over their leg of the Report Manager tree, their tables and their views (SELECT only). It is the user responsibility to control access via security requests implemented by the I.T. department.As an aside: this has been a boon for us as it satisfies the psyche of the user who always complained that I.T. was not on the ball; now they can complain among themselves when a report is late.

RE: Securing Reporting Services Reports

Mohamed IDTTALBE — Wed, 19 May 2010 04:42:50 GMT

Hi thank you all for your comment.:-)I think it is available in standard edition too.I am not sure for the express edition?

RE: Securing Reporting Services Reports

rama.mathanmohan — Wed, 19 May 2010 03:22:34 GMT

Hi It is a greate article. Am I correct in saying this fearure (ability to capture the user) is only avilable in Enterprise Edition ? Can somebody help me on this

RE: Securing Reporting Services Reports

rbarbati — Tue, 18 May 2010 14:11:29 GMT

Groovy, I'm following you."ACL" is actually how we name tagged our implementation as well (although it's a custom hybrid of sorts, in a very dynamic and ever changing environement)Transactional data in the end, along with control lists, dictates the rendered dataset And this actually negates the need for any additional security. reports access is granted to everyone, but if you are not who you are supposed to be, your report runs for 1 second and returns nothing.Thanks for the posts..tapering off here ...

RE: Securing Reporting Services Reports

Jim Barnes — Tue, 18 May 2010 14:04:11 GMT

like i said, we just use AD to authenticate users, not authorize view access. Authorization is handled locally via an ACL (access control list) table that's refreshed nightly from HR app data. Our requirements were to show/not show reports and sections of reports based on a business unit hierarchy and who could see what part of that tree (hence the need for an ACL table). Our ACL maps nodes in the tree to users. If you have an entry for a node you see that node and all siblings. No node = no see. We built a ui to mantain that list and mapping. I work in a huge enterprise.

RE: Securing Reporting Services Reports

rbarbati — Tue, 18 May 2010 13:51:22 GMT

Never one to shoot down an opportunity to learn, I'd be curious how AD has been implemented to address the granularity of securables needed in a large enterprise ?Just as examples, lets say:You have a regional sales rep, a regional sales manager, a corporate sales director, and a president.(to keep it limited - but feel free to expand that realistically about 20 fold)The Rep can see just his/her data, the regional mgr only data for their region, the sales director all "sales" data for all regions, the president any and all data.Now add complexity.The regional mgr is now a member of a specialized task team which expands visibility beyond that of other regional mgrs.The sales rep now works dual roles and part time supports another region or another division .Note that I mention both reports AND data.We secure data, not just the reports used to view it.One report , based on automated security will expose differing levels of data.Not 10 reports with different filters, one report with dynamic data level security.How does AD address this ?Is there a slick solution we might be exploring ?It is something I'd be very keen on :o)(and to address the other comments, yes, thousands of users and hundreds of reports...)Thanks all !

RE: Securing Reporting Services Reports

Jim Barnes — Tue, 18 May 2010 13:41:56 GMT

We use AD lookups to authenticate users. As opposed to authorization to view/not view, which is handled in an assembly. Given that security is a fairly common piece of functionality for all of our reports it's easier to maintain 1 dll than N code blocks. We do keep a local user table that the assembly calls to figure out who can see what that's populated from a daily SSIS job that runs against our HR database which is maintained daily.

RE: Securing Reporting Services Reports

JP-470 — Tue, 18 May 2010 11:23:37 GMT

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.

RE: Securing Reporting Services Reports

rbarbati — Tue, 18 May 2010 11:22:16 GMT

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 !

RE: Securing Reporting Services Reports

dforck — Tue, 18 May 2010 11:16:17 GMT

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.

RE: Securing Reporting Services Reports

thomas.briscoe — Tue, 18 May 2010 10:26:38 GMT

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.

RE: Securing Reporting Services Reports

SQLRNNR — Tue, 18 May 2010 09:56:38 GMT

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.

RE: Securing Reporting Services Reports

Craig-315134 — Tue, 18 May 2010 09:15:47 GMT

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.

RE: Securing Reporting Services Reports

Wes W. — Tue, 18 May 2010 07:13:47 GMT

Personally I'm not a fan of even allowing someone [i]any [/i]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.

RE: Securing Reporting Services Reports

gavindux — Tue, 18 May 2010 07:04:32 GMT

Forgetting about whether you can publish the report, are you able to login to the http://yourreportserver/reports site?

RE: Securing Reporting Services Reports

Raghavender — Tue, 18 May 2010 06:42:39 GMT

[quote][b]gavindux (5/18/2010)[/b][hr]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.[/quote]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.

RE: Securing Reporting Services Reports

Luke L — Tue, 18 May 2010 06:40:07 GMT

[quote][b]gavindux (5/18/2010)[/b][hr]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.[/quote]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.

RE: Securing Reporting Services Reports

gavindux — Tue, 18 May 2010 06:38:58 GMT

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.

RE: Securing Reporting Services Reports

Raghavender — Tue, 18 May 2010 06:35:37 GMT

[quote][b]HiThat looks to me like the credentials of the datasource you published. You can (1) edit the datasource and lock down some credentials on the Report Server or (2) prompt for the credentials and ensure you users have sufficient user/pass access to the data. If you're on Active Directory then you can also use that. Far to much to discuss here - lots of options - pro's and cons to all.[\b] [/quote]I have tried to Edit the datasource credentials, tried 3 types but no use, still its asking for credentials when I am deploying the project.

RE: Securing Reporting Services Reports

gavindux — Tue, 18 May 2010 06:30:18 GMT

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.///EDIT///I see what youre getting at Luke, in the ERP/Financial systems I am reporting on I will capture the AD login of the user against the resources/accounts/items/GL Accounts. In the query/SP that returns the report data, I filter the USERID.VALUE against that "hard coded" login. When they login to SSRS with their AD login, I use that as a parameter and only return their records.

RE: Securing Reporting Services Reports

Luke L — Tue, 18 May 2010 05:56:41 GMT

Just out of curiosity, what keeps a user who is not allowed to view a report from sending another username via a URL, or via some other method? As in the following [url=http://msdn.microsoft.com/en-us/library/ms155391.aspx?PHPSESSID=ca9tbhkv7klmem4g3b2ru2q4d4]http://msdn.microsoft.com/en-us/library/ms155391.aspx?PHPSESSID=ca9tbhkv7klmem4g3b2ru2q4d4[/url].It seems to me this security model is rather easy to bypass. Additionally, it increases the time it takes to maintain the security (having to enter a user in AD and then again in you table). I don't believe it's one I would recommend.-Luke.

RE: Securing Reporting Services Reports

gavindux — Tue, 18 May 2010 05:49:38 GMT

HiThat looks to me like the credentials of the datasource you published. You can (1) edit the datasource and lock down some credentials on the Report Server or (2) prompt for the credentials and ensure you users have sufficient user/pass access to the data. If you're on Active Directory then you can also use that. Far to much to discuss here - lots of options - pro's and cons to all.

RE: Securing Reporting Services Reports

Raghavender — Tue, 18 May 2010 05:45:48 GMT

I have developed a SSRS project, and checked the preview and working fine.When I tried to deploy that project at the end it is asking for login and passwords, I dont know which login and password to provide. I have tries to provide my domain account and password, sa password and different ways.But at last it is showing you are not authorized to view this page.I have set the url in report properties as http://localhost/reports.Any help regarding this issue.

RE: Securing Reporting Services Reports

Pyay Nyein — Tue, 18 May 2010 03:22:25 GMT

I normally use SP to restrict what user can and can't see, by passing the user ID as parameter from the SSRS report. The soluting in this article is another way to control the report and user interaction. nice one.

RE: Securing Reporting Services Reports

gavindux — Tue, 18 May 2010 03:12:37 GMT

I used a similar approach to restrict the data that sales reps could see, and limit them to their accounts only.

RE: Securing Reporting Services Reports

Alasdair Thomson — Tue, 18 May 2010 01:07:35 GMT

Good article, I too had to do something similar but the approach I took was to apply the SSRS security and also create a report that queried the security setup on the reports indicating whether the user had access or not. That way the security can still be managed easily using the SSRS security but also gave the customer a 1 stop shop at seeing what reports there are and what they do and don't have access too...

Securing Reporting Services Reports

Mohamed IDTTALBE — Mon, 17 May 2010 20:26:42 GMT

Comments posted to this topic are about the item [B]Securing Reporting Services Reports[/B]