AboutIn 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 Settingspage), 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 Assignmentpage, 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 theSite Settingspage and removing its hyperlink from theHomepage.
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
Editlink on thePropertiestab of its page), and uploading them (viaUpload Filebutton in the toolbar on the target folder'sContentpage), managing folders and their content (including creating, moving, and deleting them), as well as creating (via theNew Data Sourcetoolbar button) and modifying (via theGeneralsection of a data source'sPropertiespage) 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 Reportssubfolder residing in theHomefolder) 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.