SSRS Reports Manager Security Configuration

Question

I happen to be setting up a reporting server at work from scrach. Having very little almost no experience of sql server administration I am finding it really difficult to configure the security settings for the Reports Manager.

I do understand all the users who will be accessing Reports Manager will need permissions on Two Levels

1 – Permission to Access Data from sql server. (for which after some research I have decided to make use of datareader role.

2- Permission to Access Reports Manager for which I have choice to choose from Reports Manager Roles such as (Browser, Content Manager etc etc.)

For reports manager I have created a new Role with only permissions to View Reports.

Problem

With all these Security Filters in place yet when I added a Domain User Account to Reports Manager, The user can navigate anywhere on the Reports Manager (Can access folders , DataSources and reports they don’t have permissions on).

I do not want Users to view Data Sources or any other folders or Reports that they don’t have permissions on.

What I have Tried So Far

1- Added a user to One folder on the reports Manager, Yet they can navigate to any other Folder on Reports Manager.

2- Added a user to just one report , again they can navigate to any other Folder on Reports Manager.

3- I was using a virtual account for SSRS Service Account, Removed that and tried on all other 3 built in accounts for service account, LocalService, LocalNetwork & LocalSystem. (Not sure if this would have made difference).

All these efforts in vain as , whenever a user is added to any one report or folder they can navigate to all the other folders and reports and can actually execute reports.

Users had the datareader role assigned in Sql Server and a Custom Role on Reports Manager who has permissions only to View Report.

I have set the reporting services to use the default windows authentication. It is Sql

Server 2008 R2 Reporting Services. Data Centre Edition.
Please any advice or any direction would be a great help. I have been reading BOL but nothing has helped as yet.

Answer 1

Normally in SSRS you would use the SSRS management interface (/ReportsManager IIRC) to set user roles/perms by folder. They are normally inherited by subfolders though you can override that. The role 'browser' is most appropriate for end users as it allows them to run / view reports but not much more. If you grant a user 'browser' access for folder but not B then they will not be able to access folder B unless it is either a sub-folder of A (by default granted the same perms as A) or unless B is a sub-folder of another folder the user has 'Browser' access to.

You probably should not be granting domain users any rights directly with-in SQL Server. The various accounts that SQL Server itself uses as well as administrative accounts should be configured with-in SQL Server. End user access to SSRS is controlled by SSRS based on rules entered into SSRS.

Answer 2

1 – Permission to Access Data from sql server. (for which after some research I have decided to make use of datareader role.

When you create your report in SSRS you want to set up the data source with an account with adequate privileges within the database to run the report (In your data source under credentials). Typically we use a service account for this unless higher security is required. I have seen where this falls off after deploying a report to the report server so it is always good to check the data source in the report server and make sure the account is still there.

2- Permission to Access Reports Manager for which I have choice to choose from Reports Manager Roles such as (Browser, Content Manager etc etc.)

So are you adding browser access to the root folder itself? If so that is why that account can view all folders in the report server, if each folder in the report manager is inheriting permissions then that is why they can access further. I would remove that user from the root folder and try adding that user with browser permissions to the folder you want them to view.

I have seen sometimes when changing permissions on users that the new settings won't take to an account unless I remove it completely then set it back up with just browser access.

Answer 3

This isn't a complete answer, but I do not recommend having live reports passing user credentials to SQL Server.

Suppose a malicious report developer builds some kind of report that does Nasty Things that would require sysadmin privileges (vandalism, granting privileges, whatever). Just deploy the report, and trick someone with sysadmin to run it (CSRF might be sufficient to run the "report", though I've never tried it), and presto, your code is running with sysadmin rights.

The approach we use is to select Windows authentication on the data sources within Visual Studio. Developers can thus build reports against whatever they have access to. All of the data sources are deployed to a single location ("/Data Sources", naturally), and these copies of the data sources are configured with static credentials. Since Visual Studio doesn't overwrite data sources by default when deploying report projects, everything works nicely, assuming we don't forget to grant permissions to the account accessing the data, like I always do.

Now, back to the problem of excessive permissions. By default, any object (folder, report, etc.) that you create in Reporting Services will be set to inherit permissions from its parent. It's similar to Windows file permissions, though a bit simpler overall - an object's permissions have to be either all inherited or all explicitly set. Out of the box, Reporting Services has a few standard roles you can grant to objects. If you want to get more granular and customize the roles, use Management Studio: from Object Explorer, connect to Reporting Services, and specify the report server web service URL as the server name. Drill down to Security, Roles, and you can customize or add roles as needed.