Exam 70-466 is on the horizon as I work towards Microsoft’s MCSE certification in Business Intelligence. 70-466’s content is approx. 70% SSAS and 30% SSRS. Included in its syllabus (here), is a comprehensive section on security in SSRS.
In this post I work through that section, expanding on each of its requirements. At the time of writing it reads as follows:
Phew! That does need some pulling apart. I consider each point separately below. My sources for this post are MSDN (for SQL Server 2014) and Brian Larson’s book on Microsoft SQL Server 2012 Reporting Services (Fourth Edition).
Unless otherwise stated, the content below refers to a Reporting Services native mode installation. (For a SharePoint integrated mode installation, authentication and authorization are handled on the SharePoint site, before requests reach the report server.)
This is about authorisation rather than authentication, i.e. who can access what and how they can access it. Role-based security centres upon roles that are assigned to users, where a role consists of the rights to perform one or more tasks. Examples of tasks are “View Reports” and “Manage Folders”.
There are predefined roles in SSRS and System Administrators can also create custom roles. The predefined roles are:
The last 2 roles are notable in that they have the rights to a special set of “system-wide” tasks, where “Manage Roles” is an example of one of these. Of the remainder, Content Manager is the most privileged since it has the rights for all tasks except the system-wide tasks. The Browser role is the least privileged and is aimed at users who view reports only. This role and the Report Builder role will be the most commonly assigned roles, where the latter is for report authors.
By default, Content Manager is assigned to the local Administrators group on the machine hosting the Report Server for all items – this assignment can be seen when looking at the security settings in Report Manager for any item, such as a report or folder. System Administrator is also assigned to the local Administrators group – this can be seen when looking at the security settings under Site Settings in Report Manager.
So back to the title of this section, configuring server-level (“system-level” is more accurate I think) role-based security is regarding creating role assignments (e.g. user X with the System User or System Administrator role) via the Site Settings/Security page in Report Manager, and configuring item-level role-based security is regarding assigning role assignments (e.g. user Y with the Browser role) to folders, reports or resources via their respective Security pages in Report Manager (e.g. under “Folder Settings” for folders).
Setting up Reporting Services service security is done by running the Reporting Services Configuration Manager at the end of the installation process, and selecting an appropriate account in the Service Account tab:
The addition of a new role (as well as modifying or deleting existing roles) is performed via SQL Server Management Studio. After connecting to Reporting Services via SSMS we see an Object Explorer window similar to this:
Creating a new item-level role is performed by right-clicking the Roles node and selecting “New Role…”. There, a new role can be given its name and assigned its tasks. Creating a new system-level role is the same except being accessed via the System Roles node.
Modifying a role is performed by right-clicking the role and selecting Properties and making changes there, and deleting a role is performed by right-clicking the role and selecting Delete.
When creating a new data source, 3 types of information are required: the data source type, data source connection information and credentials.
Credentials are managed independently from data sources however. This means for example that the credentials used to view a report in the development environment can be different from the credentials required to run the same report from its published location on the report server. So the credentials for a report may need to be changed once it is published – this can be done via the data source’s Properties page in Report Manager (accessible via the host report for embedded data sources or via the data source itself for shared data sources).
The relevant part of this Properties page is:
For each of the first 2 options, the credentials can be either a Windows account or a database login. The first option is recommended when reports contain confidential data and requires that a username and password is entered each time the report is run. For the second option, storing credentials in the report server is required when a report runs unattended, e.g. as part of a subscription that emails reports to users. In this case they should be entered within the Credentials section of a data source definition as shown above (so that they are encrypted securely) and not as part of the connection string. The third option passes the security token of the user accessing the report to the server hosting the external data source, and the fourth option uses the credentials of the unattended Execution Account that’s defined in Reporting Services Configuration Manager.
In its default configuration, Reporting Services depends upon integration with Windows security – it does not maintain its own list of user names and passwords. When a user accesses either the Report Manager web application or the Reporting Services web service, they must first authenticate with the report server. Once this authentication is complete then it is known which items the user can access and which tasks they can perform. For SSRS deployments where Windows integrated security is not viable, then a forms-based custom security extension can be used instead.
Regarding site-level security, rights can be assigned to each folder and to each item within a folder, e.g. a report or a resource such as a shared data source. To facilitate the management of this security, rights are automatically inherited recursively from the top level (Home) folder downwards. This inheritance can be over-ridden if required. The rights referred to here are actually role assignments, i.e. a Windows user or group that has one or more roles assigned to it.
These are covered in sections (2) and (1) above respectively.
Creating a new role assignment and assigning Windows users to roles is applicable to both system-level security and item-level security. It takes place in Report Manager via the Security page accessible via Site Settings for system roles, and via the Security page of items such as reports and folders for item roles. The Report Manager dialog for the latter is:
Here a Windows group or user name is entered, and one or more roles are assigned to it. Once the role assignment is saved, then it secures the item according to the assignment’s content.
To manage permissions regarding which SharePoint groups can access an individual report, model, or data source, see the instructions on MSDN here.
This point goes back to the accessibility of role definitions in SSMS as mentioned in section (2) above. The Properties dialog for a role can be used to define varying content for it: