SQL Server 2008 Reporting Services for Internet deployment

Solutions providers can use this chapter excerpt to find out about the various Internet deployment options available when using SQL Server Reporting Services.

Internet Deployment Considerations

Reporting Services is not specifically designed for Internet-facing scenarios. This is, partially, because the default authentication mechanism of Reporting Services is Windows integrated security. For security reasons, SQL Server setup does not provide options to deploy SSRS with anonymous access to reports.

Several deployment options are available to an SSRS administrator to make reports accessible over the Internet:

  • Keep only public data in the SSRS catalog and enable Report Server for anonymous access.
  • Deploy SSRS with Windows authentication and leverage Kerberos delegation to authenticate users.
  • Use programmatic options (such as custom security extensions) to authenticate and authorize users.

Internet Deployment Option 1: Enable Report Server for Anonymous Access

About the book:
This chapter excerpt on Reporting Services Deployment Scenarios (download PDF) is taken from the book Microsoft SQL Server 2008 Reporting Services Unleashed. This book can help solutions providers to use SQL Server 2008 Reporting Services to deliver business intelligence services to customers, learn about new features in SQL Server 2008 R2, manage report data sources and models and more.

This scenario is designed to distribute public information. In this scenario, none of the reports are secured, and all the users would get the same information. When accessing Reporting Services deployed in this fashion, Internet users will not be prompted for login credentials. Best practice for this scenario is to place the SSRS catalog database on the same server with an instance of the Report Server. Because the Report Server has web components, this option means that the SQL Server 2008 instance that hosts catalog data will also be running on the web server and there are no queries that cross boundaries of the web server.

To reduce data exposure in this scenario, the catalog must contain only a limited subset of public data. To further reduce data exposure, reports can be configured to be rendered from an execution snapshot; in this latter case, the SSRS catalog would contain only the snapshot data.

To configure a report's rendering from a report-execution snapshot, an administrator can use the Report Manager, navigate to a report that needs to be configured, then navigate to the Properties tab, Execution screen, and select the Render This Report from a Report Execution Snapshot option.

Because this scenario does not protect data from unauthorized access, it might only be used when a company intends to publish public data, such as a product catalog. Secure Sockets Layer (SSL) configuration is not required for this scenario.

To provide public data (or snapshots with public data) to the SSRS catalog in this configuration, an administrator can use replication or SQL Server Integration Services to "copy" public data (or snapshots) from an internal data source to the SSRS catalog placed on a web server.

Internet Deployment Option 2: Deploy Report Server with Windows Authentication

This scenario leverages a default authentication mechanism of SSRS and uses a corresponding security extension.

In this scenario:

1. A company would have a domain associated with web-facing servers and use Kerberos delegation to validate a user by interacting with a corporate domain inside the firewall.
2. Customers can configure Reporting Services virtual directories with either Windows integrated or basic authentication.
3. When accessing Reporting Services deployed in this fashion, Internet users are prompted for credentials. After users are validated, they have the level of access to a report corresponding to their credentials.

If this option is chosen, an administrator must configure SSL for proper security, especially for basic authentication.

Internet Deployment Option 3: Use the Programmatic Approach

Situations in which a programmatic approach can be used include the following:

  • Users do not have Windows accounts.
  • User IDs and passwords are stored in a third-party security provider, which, in turn, is used for user authentication.
  • Single sign-on technology (such as Microsoft Passport) is used in place of Windows authentication.

To programmatically handle security, a company can develop a custom security extension, handle security within a .NET application, or use the new ReportViewer control.

Remember that security breaches can have far-reaching financial consequences for a business. Therefore, use custom security solutions with caution, especially when a reporting solution is exposed on the Internet.

This book discusses some aspects of security extensions in Chapter 29, "Extending Reporting Services." An example of a security extension is provided with SQL Server 2008.

On a high level, to handle security within an application, a developer could:

  • Authenticate a user in the code by either collaborating authentication processing with a third-party security provider or perhaps simply comparing the user's identifier and password to the values stored in a database.
  • After the user has been successfully authenticated, the code would either query a third-party security provider or a database for the user's security access options.
  • The code needs to control access to a report, based on the user's security access options.

You have several options to control a user's access to a report. Depending on the need of the reporting application, a code can impersonate a Windows user who mapped to the SSRS Content Manager role (an administrative access). In turn, the code itself would control which reports can be accessed by a user.

About the authors:
Michael Lisin has more than 12 years experience in the software industry with expertise in SQL Server and .NET technologies. Lisin is a regular speaker on SQL Server at various events, including SQL Server PASS and SQL Server User Group meetings.  

Jim Joseph is the co-author of Microsoft SQL Server 2005 Reporting Services and is currently employed by Continental Airlines as a SQL Server DBA.  

Amit Goyal is the lead program manager for Microsoft's Reporting Services team and is a known expert on SQL Server 2008 Reporting Services.

Alternatively, depending on the actions that the code must take, the code may impersonate different Windows users who have finer granularity of permissions. In this case, there could be a Windows user who has access to just a single report.

After a user is impersonated, the code can, for example, use the function Render to access the report's data stream or use the ReportViewer control.

The ReportViewer control can process remote server and local reports. When the ReportViewer control processes local reports, it does it internally and does not need access to a Report Server.

Most data sources (like SQL Server) that a ReportViewer control uses require user identification and a password to access data. In this case, an application can collect, for example, a user's SQL Server credentials and pass those credentials to a data source, thereby restricting the user's access to data.

Enabling a Report Manager for Internet Access

As previously stated, Report Manager was never specifically designed to be an Internet facing application. But in case it is, a few tips can help make it more secure when exposed to the Internet. Figure 5.3 shows a possible Internet deployment scenario.

FIGURE 5.3 Internet deployment scenario.

The first of these is to see whether you can run Report Manager on its own server, separate from the Report Server web service, scheduling and delivery processor, and the database server. The key is to remember that SSRS 2008 consolidates all these services into a single Windows service. It is possible to turn off every feature of SSRS except for Report Manager and add the server to a scale-out deployment. This way, the server with Report Manager reaches out to another machine to render and process reports.

Another thing to consider is security. First, build a custom security extension that uses Forms authentication or another kind of technology. After authenticating your users, minimize their permissions on the Report Server. Two roles are required for viewing reports: Browser and System User.

In addition, minimize the footprint of the exposed server. Make sure Report Manager uses another Report Server to process reports by setting the ReportServerURL and ReportServerVirtualDirectory setting in the RSReportServer.config file. Also turn off any features you are not using. This may include My Reports, client-side printing, Report Builder, subscriptions, and so on.

If all of this fails, and you still end up running Report Manager on the same computer as the Report Server, go ahead and disable the defaultProxy. By default, this should be set to false, but go ahead and verify it. An example is shown here:

<defaultProxy enabled="false"/>

SQL Server Reporting Services deployment scenarios
  SQL Server 2008 Reporting Services for high-availability deployment
  SQL Server 2008 Reporting Services for Internet deployment
  SQL Server 2008 hardware and software requirements
  Key features in SQL Server 2008 Reporting Services editions

Printed with permission from Sams Publishing. Copyright 2009. Microsoft SQL Server 2008 Reporting Services Unleashed by Michael Lisin, Jim Joseph and Amit Goyal. For more information about this title and other similar books, please visit Sams Publishing.

Dig Deeper on Database software management