Reporting Services System and Item Security

Monday Aug 10th 2009 by Marcin Policht

Recent installments of our series have focused on SQL Server 2005 Express Edition's implementation of the Reporting Services component. This article addresses our temporary disregard of security restrictions by describing authentication and authorization functionality that can be leveraged to control access to published content and management characteristics.

In recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have been focusing on its implementation of the Reporting Services component. So far, we have presented information pertinent to its installation, initial configuration, and content management. Until now, throughout our examples, we have been relying on a privileged account with administrative access to the computer hosting Reporting Services database and its virtual directories. This allowed us to temporarily disregard security restrictions applicable to standard users. In this article, we will address this intentional oversight by describing authentication and authorization functionality that can be leveraged to control access to published content and management characteristics.

As we have mentioned earlier, responsibility for facilitating Reporting Services security is shared between Report Manager, which provides the interface for its configuration (note that, unlike its full-fledged counterparts, Reporting Services in SQL Server 2005 Express Edition can not be managed via Management Studio console) and Report Server, which enforces configured settings. The implementation employs a role-based security model, which transparently associates underlying permissions with a set of predefined tasks that designated users are supposed to be able to perform. These tasks are grouped in two main categories, referred to, respectively, as system and item-level security, with the first one handling site-wide administration and the second one dealing with content management. Both of them function (for the most part) independently, allowing you to control access to various components of your Reporting Services installation. In order to become effective, each role needs to be assigned to an arbitrary Windows user or group (either local or an Active Directory-domain based) account.

It is important to note that it is not possible to alter permissions associated with predefined roles or create new, custom ones, even though such functionality exists in Reporting Services incorporated into SQL Server 2005 Standard, Enterprise, and Developer editions. This design decision reflects a distinct set of goals that each of these products was supposed to deliver and, while it results in limited flexibility (which might be viewed as a drawback), it also yields considerably simplified access control management mechanism (sufficient in most of the scenarios that Express Edition is intended for).

A list of system roles can be accessed using the Report Manager interface (which is reachable via http://localhost/Reports$SQLExpressInstanceName URL, where SQLExpressInstanceName designates the name of the target SQL Server 2005 Express Edition instance). Its home page (as well as any page within its site hierarchy) includes the Site Settings hyperlink appearing in the upper right corner (providing that you are logged on with an account that has the ability to view system configuration), which takes you to the Site Settings page, with the Configure site-wide security hyperlink (in the Security section towards the bottom). Once you click on it, you will be directed to the System Role Assignments page, which provides you with the listing of current Windows users and groups that have been granted system-wide roles. This should include, at the minimum, two System Administrator entries, associated with the BUILTIN\Administrators local group and the user account that was used to install Reporting Services. After activating the Edit option, you will be presented with a page from which you can change or delete them (the latter can also be accomplished using the Delete button in the toolbar). The New Role Assignment button takes you to an interface containing the Group or user name textbox and the listing of two default system roles:

  • System Administrator - grants the ability to view and modify system role assignments, alter Report Server properties (via mentioned earlier Site Settings page), including its security (which also applies to content management). Executing report definitions and administering shared schedules, associated with this role in full-fledged editions of SQL Server, are not applicable in the context of our discussion (since this functionality is not available in SQL Server 2005 Express Edition).
  • User Administrator - according to the description on the New System Role Assignment page, this role is supposed to allow viewing Report Server properties (shared schedules mentioned there, are inconsequential in our case). However, as it turns out, the outcome does not yield the expected results, blocking corresponding Windows accounts from reaching the Site Settings page and removing its hyperlink from the Home page.

Item-level security applies to specific content (the most common approach involves assigning item-level permissions on the folder level). Permissions are assigned to individual items within Reporting Services Web site (folders or even designated reports, if such granularity is needed). By default, permissions set on a parent container are inherited by its children (folders and reports). Unfortunately, since the interface does not provide a method to change this behavior, altering the resulting security settings is fairly cumbersome and involves manual changes to individual subfolders (although switching back to the default is simplified by the Revert to Parent Security command button available in the Security page of any item with custom permissions). Effectively, however, folders not only accommodate organizing reports into a logical, hierarchical structure, but also form security boundaries, used to restrict access based on arbitrary permissions. Starting with the Home top level container down to individual reports and data sources, you have the ability to assign local or domain users or groups to one of the following five pre-defined roles (via the Security section within the Properties tab of that item's page):

  • Browser - the most restrictive, intended for users, who are only supposed to be able view reports published throughout a site hierarchy. It combines permissions to view folders and reports. In full-fledged editions of SQL Server 2005, it also permits viewing report models, resources, as well as managing subscriptions; however, these options are not applicable in the context of our discusssion (since the corresponding features are not available in SQL Server 2005 Express Edition).
  • Report Builder - from the perspective of SQL Server 2005 Express Edition, this role is, in essence, equivalent to the Browser (due to a lack of support for Report Builder functionality). In general, the set of permissions includes the same privileges (allowing viewing folders and reports), while the ability to consume reports is inconsequential to us.
  • Publisher - allows viewing reports and their definitions, editing (via Edit link on the Properties tab of its page), and uploading them (via Upload File button in the toolbar on the target folder's Content page), managing folders and their content (including creating, moving, and deleting them), as well as creating (via the New Data Source toolbar button) and modifying (via the General section of a data source's Properties page) data sources. This is also a role that grants permissions required when deploying new reports via Business Intelligence Development Studio (as described in one of our earlier articles).
  • My Reports - ties to the feature we have discussed in our previous article, which is intended to provide a personalized view of reports (based on their ownership) hosted within the Report Manager site. While such a view (represented by the My Reports subfolder residing in the Home folder) is also accessible to accounts designated as Browser and Report Builder, this role facilitates also publishing and creating linked reports, as well as management of data sources (creation, redefinition, or deletion) and folders. The role also permits modifying security on folders or reports by their owners.
  • Content Manager - gives full permissions to the content of target item, allowing you to perform all management tasks associated with it, including the ability to grant equivalent permissions to other users and groups. Note that assigning this role (along with the earlier described System Administrator) to designated accounts is a standard part of the installation procedure of SQL Server 2005 Express Edition-based Reporting Services on computers running Vista operating system (as outlined in the Microsoft Knowledge Base article 934164).

In the next article of our series, we will describe other ways of protecting content published via Reporting Services site in SQL Server 2005 Express Edition.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site