Reporting Services System and Item Security

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
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

Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles