Secure Access to Report Data

In the healthcare and financial industries, where database applications often process confidential information, using authentication and encryption to secure access to data has become a common practice. Indeed, for companies that are affected by industry regulations such as the Health Insurance Portability and Accountability Act (HIPAA), securing data is an absolute requirement. For many other industries, however, ensuring the security of reporting systems and the databases they access remains a low priority on system designers' and administrators' to-do lists because of its seeming complexity.

It doesn't have to be difficult, though, especially if you're using SQL Server Reporting Services (SSRS) as your reporting system. Bundled with SQL Server 2005, SSRS can simplify securing your reporting data by using a role-based access control (RBAC) model to leverage your existing Windows Server 2003 or Windows 2000 Active Directory (AD) infrastructure. Let's look at how you can configure SSRS to secure access to your data.

Roles and Their Tasks

To understand SSRS's access-control model, you must first understand the RBAC concept of a role. A role is a set of operations or tasks that a particular subset of users needs to be able to perform. The exact combination of tasks that constitute a role enables a particular type of user to do his or her work (and nothing more or less) in the context of a given application. For example, SSRS includes a Browser role for users who simply need to view reports and a Publisher role for users who must view, manage, and publish reports. To enforce RBAC, you link roles to an application's resources, then map roles to user identities.

To get you started quickly on defining your report application's RBAC model, SSRS includes a set of predefined roles. You can use the predefined roles to secure SSRS resources or define custom roles and delete the predefined ones. SSRS includes two predefined role categories:

SSRS predefines 16 item-level tasks and 9 system-level tasks. Together, these 25 tasks cover all actions a user or administrator can perform in SSRS. Web Tables 1 and 2 show the item-level and system-level task assignments for the predefined SSRS roles. Unlike with SSRS roles, you can't define your own tasks or delete predefined tasks.

Configuring Roles and Their Tasks

To view or edit the default SSRS roles and their task assignments or to define custom roles, you can use the GUI-based SQL Server Management Studio (SSMS) or the Web-based SSRS Report Manager.

To use SSMS to define SSRS roles, start SSMS. In the Connect to Server dialog box, type the name of your SSRS server, making sure that you select the Reporting Services option in the Server type drop-down box; enter your credentials and an authentication method, and click Connect. You'll see the SSMS configuration dialog box. To view the predefined item- and system-level roles, expand the Security container. The item-level roles are in the Roles container, and the system-level roles are in the System Roles container. To view the task assignments for a role, double-click the role, or right-click the role and select Properties.

To modify predefined roles and their task assignments, open Report Manager in Microsoft Internet Explorer (IE) by going to http://Your_SSRS_Server_Name/reports. Click Site Settings in the top right corner of the SSRS Home page. At the bottom of the Site Settings page, click Configure item-level role definitions or Configure system-level role definitions to bring up a list of the itemlevel or system-level role definitions. From this list, click a role to edit its description or to view and modify its task assignments. Figure 1 shows the task assignments for the predefined Publisher role.

Role Assignments

You can leverage roles to enforce access control on SSRS resources such as reports, report folders, and configuration settings. A nice, timesaving feature of SSRS is the ability to leverage the Windows user accounts and groups that are defined in AD to control access to SSRS resources.

When you first access a brand new SSRS installation, you immediately experience the effects of SSRS role assignments. Because by default only the BUILTINAdministrators group is assigned the Content Manager and System Administrator roles, only the members of the BUILTINAdministrators group (which in an AD domain environment includes enterprise administrators and domain administrators) can access an SSRS server's report content and configuration. If you want to allow any other user to access an SSRS system and its content, you must log on to Windows using an administrator account that's a member of the BUILTINAdministrators group, then assign the user's AD account an item- or system-level role from one of the SSRS role assignment configuration interfaces, a process which I describe later.

A user or group can have only one role assignment for any SSRS resource. Role assignments can, however, be nested. For example, an administrator can create a role assignment for a user who is a member of a group that has a role assignment on the same SSRS resource. In that case, SSRS grants the user permissions for all tasks in the role definitions of both the user and group role assignments.

SSRS role assignments can leverage permission inheritance. Permission inheritance means that a child object automatically receives the permissions that are defined on its parent object. In SSRS, all child folders and reports in the SSRS report folder hierarchy automatically inherit the role assignments of their parent folder, which greatly simplifies administration of role assignments. Instead of having to assign roles for each report item and folder, an administrator can assign roles just once, on the level of the parent report folder, and know that those roles will automatically apply to all child folders and reports.

Configuring Role Assignments

In an item-level role assignment, an administrator associates an item-level role with a Windows user or group and with an SSRS report folder or report. An item-level role assignment defines what a user or group can do with the associated report folder or report. Item-level role assignments are used to set SSRS user and data administrator permissions and can be defined from both Report Manager and SSMS.

To set an item-level role assignment in Report Manager, navigate to and open the folder or report for which you want to configure role assignments, then click the Properties tab. Click Security to view the resource's role assignments. To change the role assignments that the resource inherited from its parent folder, click Edit Security Settings. SSRS asks whether you really want to change the security settings on the item. When you click OK, a Web page is displayed on which you can configure item-level role assignments. To add a role assignment, click the New Role Assignment link to display a Web page like the one in Figure 2. Select the check box for each role you want to assign and click OK.

To configure an item-level role assignment in SSMS, access the folder hierarchy from the SSMS Home container and navigate to the folder or report for which you want to configure role assignments. Open the properties of the folder or report and click Permissions in the left pane. In the right pane, you can then define the itemlevel role assignments, as Figure 3 shows.

To set a system-level role assignment in Report Manager, click Site Settings in the top right corner of the SSRS Home page. (If Site Settings isn't available, you don't have permission to access these settings.) In the Security section at the bottom of the Site Settings page, click Configure site-wide security to open the System Role Assignments Web page. On this page you can delete and edit role assignments, or you can click New Role Assignment to configure access for a new user or group.

To set a system-level role assignment in SSMS, open the reporting server's properties. (The reporting server is represented as the SSMS root container.) Then select the Security container to open the interface from which you can edit system-level role assignments.

Indispensable Security Controls

With SSRS's easy-to-use role-based controls to secure the access to your organization's report data and the ability to leverage your existing AD infrastructure, it's simpler than you might realize to secure report data. These mechanisms can significantly ease the burden for organizations that need to configure their SSRS-based reporting system for compliance with government or industry regulations.