Managing and Securing Reports

After you have authored reports and published them to the report server, you need to consider how to make sure that the proper people have access to them.

SQL Server 2005 comes with two tools for managing items in the Reporting Services catalog: a Web-based tool called Report Manager and the Windows-based SQL Server Management Studio (SSMS). SSMS is the same tool that you use to manage your other SQL Server components.

Using SQL Server Management Studio

SQL Server Management Studio is the environment to use for the following type of tasks:

• Defining security role and report permissions

• Defining subscriptions

• Defining shared schedules

• Defining report cache policies

• Defining linked reports

• Defining new data sources

• Scripting report server operations

You connect to your report server in Management Studio (SSMS) by selecting Reporting Services in the Server Type drop-down in the connection dialog (see Figure 17-11) and supply the name of your server, using either ServerName\InstanceName syntax or a URL (e.g., http://servername/reportserver), as shown in Figure 17.11.

Figure 17-11. Connecting to SQL Server Reporting Services.

After you are connected to a report server, you can see the contents of the server displayed in the SSMS Object Explorer (see Figure 17-12). From here, you can bring up the Property dialog for any object in the report server catalog.

Figure 17-12. Reporting Services objects in Object Explorer.

Securing Reports

Managing Reporting Services security requires an understanding of the different ways that content can be secured on the report server. The first level of security is on the objects in the report server catalog (reports, models, data sources, resources), which can be secured in a role-based security mechanism. Roles are sets of permissions that can be granted to users or groups for specific items in the report server catalog. Reporting Services comes with several built-in roles, called Browser or Content Manager, that you can assign to your users by using SSMS or Report Manager (see Figure 17-13).

Figure 17-13. Report Designer Security Property dialog.

To set security on an item, you need to break the inheritance of the security roles from the parent and then modify the roles for the item.

Tip

Like other dialogs in SQL Server Management Studio, Reporting Services property dialogs can be scripted. This makes it easy to create a set of operations that you can execute later. Although the scripts generated cannot be directly executed in SSMS, you can save them for later execution through the Reporting Services script host, rs.exe.

In addition to setting security on the reports themselves, the data source connection information can also be managed after a report is published to the server. Although reports can be built to take advantage of Windows integrated security to access the data source, this limits their usefulness. For example, if your report server is not on the same system as the data source that you are using, you cannot use integrated security unless you have implemented Kerberos delegation (for more information about delegation, see the Microsoft Knowledge Base on MSDN).

Most users use a single set of stored credentials (either SQL or Windows) to access back-end data sources. They then use the name of the user accessing the reports (available in the report through the global expression User!UserID) to potentially limit the amount of data coming back from the report queries.

Using Report Manager

Report Manager is the Web-based viewing and managing application that comes with Reporting Services (see Figure 17-14). Report Manager enables you to view and manage reports on your report server (see Figure 17-15).

Figure 17-14. Reporting Services Report Manager.

Figure 17-15. Reporting Services Report Manager (details).

Report Manager is the environment to use for the following types of tasks:

• Run and navigate reports

• Upload report to a server

• Define security role and report permissions

• Define subscriptions

• Define shared schedule and snapshot schedules

• Define caching policies

• Invoke Report Builder

• Define linked reports

• Define new data sources

Report Manager includes many of the tasks that can be accomplished through SQL Server Management Studio. So you may wonder, why the need for Report Manager? Reporting is an activity that is frequently done through a Web browser interface. As such, Report Manager allows you to perform many of the management activities remotely without the need to install SSMS. Also, many of the management tasks can be delegated to end users (e.g., scheduling snapshots, defining a new subscription). What if you don't like the look and feel of Report Manager? Report Manager was designed as a sample to demonstrate the use of the Reporting Services Web service API. As a result, every single capability of the Report Manager can be integrated in a your own custom-built application.

Now that you understand some fundamentals about the toolset, it is time to look at how it you can use it to address problems in some common business scenarios.