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 BUILTINAdministrators
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 textbox and the listing of two default system roles:
name
-
System Administrator – grants the ability to view and modify
system role assignments, alter Report Server properties (via mentioned earlierSite 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 Settings
page and removing its hyperlink from theHome
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
(viaEditlink on thePropertiestab of its page), and
uploading them (viaUpload File
button in the toolbar on the target folder’sContent
page), managing folders and their content (including creating, moving, and
deleting them), as well as creating (via theNewtoolbar button) and modifying (via the
Data SourceGeneralsection 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 theMy Reports
subfolder residing in theHome
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.
