Using Reporting Services in an Internet/Extranet Environment

Several months ago, I worked with a customer who was rolling out a new version of an ecommerceWeb portal.The application, which is used primarily by the customer's partners, exposes sales data and other confidential information. The customer wanted to use SQL Server 2005 Reporting Services, but was concerned about security and report proliferation. Specifically, the customer wanted answers to two key technical questions: How can we use Reporting Services in an environment in which Windows Authentication isn't available and how do we deploy a common set of reports for the entire application—without the risk of one partner seeing another's data?

Because these questions are common in environments that deploy applications across the Internet or extranets, I'm going show you how to first, deploy a custom authentication scheme for Reporting Services and second, develop reports that filter data at run time based on which end user is viewing the reports.You can also use this filtering technique with Reporting Services' default security model.

Authentication and Authorization

Let's first quickly review the concepts of authentication and authorization. Authentication is the process of establishing who you are, whereas authorization is the process of determining what you can do. For example, when entering the fitness club, I use my membership card to establish my identity (authentication). Once inside, I have the privilege of using certain parts of the club (e.g., pool, tennis court) based on the authorizations I've been granted. Sadly, I rarely make it the fitness club—but that's a topic for another article...

By default, Reporting Services 2005 uses Windows Authentication mode to establish identity and its own set of roles to grant privileges (referred to as Tasks) to Windows users or groups.This security model works well for companies that use Windows Authentication within the organization.

However, when it comes to providing reports outside the company firewall (or when a company is trying to integrate with a different internal authentication standard), Windows Authentication is often not an option. Fortunately, the Reporting Services security model is extensible (like many other parts of the product). In this case, the product's extensibility lets you replace the default Windows Authentication mode with a custom authentication provider. Specifically, a developer can implement the .NETbased IAuthenticationExtension security extension interface to handle the job of authentication. We're going to do just that (and a bit more) in this article. (For a discussion of using Web Services as an alternative solution, see the sidebar "What About Web Services?")

Reporting Services Security Extension Basics

Reporting Services is built on top of the .NET Framework and uses a modular architecture designed for extensibility.You can use .NET code to extend several areas of the product, including report data sources, delivery, rendering, and security. To leverage custom authentication, you implement the IAuthenticationExtension security extension interface, and to use custom authorization, you use the IAuthorizationExtension interface. From a deployment standpoint, your implementation of these interfaces is compiled into a DLL.You deploy the DLL to the Reporting Services directory and modify a set of configuration files accordingly.

Reporting Services 2000 used this security extension architecture, and it hasn't changed much in the 2005 release. However, Microsoft has improved the architecture's documentation and now provides a sample security extension. I'll let you read the detailed explanation of the security extensions in SQL Server 2005 Books Online (BOL) yourself, but here's a summary of the two extension interfaces we'll be implementing:

IAuthenticationExtension. This interface represents an authentication extension and contains the following three methods:

IAuthorizationExtension. This interface handles authorization through three methods:

When I first reviewed the security extensions, I wondered whether it was really necessary to implement IAuthorizationExtension. For example, suppose I'm interested in replacing Windows Authentication with Forms Authentication, but I don't want to replace the built-in roles and tasks that my authentication mode uses for authorization. It turns out that you do need to implement the authorization extension. Fortunately, you can still use the roles and tasks within your authorization logic, and this is what we'll do in our samples. Now that we have a basic understanding of the interfaces, we're ready to dive into a sample implementation.

Getting Started: Installing the Sample Security Extension

We're going to start by installing the CustomSecurity sample security extension that ships with SQL Server 2005. (Note that samples aren't installed automatically during setup. For instructions about how to install the samples, see the BOL topic "Installing Samples.") The default location of the CustomSecurity sample, which uses Forms Authentication, is the Extension Samples FormsAuthentication Sample subdirectory of the Reporting Services Samples directory. So the path looks like this:

C:Program Files Microsoft SQL Server90Samples Reporting Services Extension Samples FormsAuthentication Sample.

The sample is available in both C# and VB.NET versions.

A few words of caution before you begin. First, be aware that implementing a security extension isn't necessarily complex, but it does require several manual steps. Except where I've noted in my tips below, you'll want to carefully follow each step of the BOL topic "Security Extension Sample [Reporting Services]" to deploy the sample. In the sample subdirectory, you'll also find a Readme file that's the same as the BOL topic except that the Readme file incorrectly repeats some of the initial deployment steps.

Second, you'll see that BOL warns, "Reverting back to Windows Authentication after migrating to a different security extension is generally not recommended." I suggest that you install a new named instance of Reporting Services 2005, which you can uninstall later without affecting any existing instances.

Third, when I went through the sample, I encountered a few problems that I don't want you to have to work through. To save many hours, while installing the sample, keep the following tips close:

Once you've built and deployed the sample code and modified the necessary configuration files, you should be able to navigate to the sample screen that lets you create and register an initial administrative account and log on from the Report Manager. Microsoft also provides a second logon screen for the ReportingServices URL. These sample logons obviously aren't secure or production-ready, but they offer a simple way to get started.Take some time to create additional non-administrative accounts (e.g., add them to the built-in Browser role in the Home folder) to verify the authorization extension logic is also working properly.

Not Quite What We Wanted

I distinctly remember my first impression after working through the sample: This isn't quite how my customer wants things to work.The customer already had an application with its own login screens, so we didn't want to create additional login screens within Reporting Services. My customer also didn't want end-users to have to log in twice (once for the application, again for Reporting Services).

After further review of the sample extension, we began exploring the option of having the application and Reporting Services share a cookie to manage requests after a client has been validated. ASP.NET supports forms authentication in a distributed environment, so this is the route we decided to take. (For information about this functionality, see the Microsoft article "Forms Authentication Across Applications." ) The key to configuring this distributed authentication (which you can see in action in my second downloadable sample available at the top of the page) is setting a few identical attribute values in the Forms element of each application's web.config file (and adding an identical MachineKey element).=

The Second Sample

To illustrate what my customer's environment ended up looking like, I built another integrated sample of the CustomSecurity project (with three major parts) for you to download and evaluate.The first part of this sample consists of a custom ASP.NET Web site, FormsAuthSample, which uses Forms Authentication along with the new ASP.NET 2.0 membership classes and login controls.The membership classes provide a standardized way of validating user credentials, and managing user settings against a custom data store such as SQL Server.The login controls work on top of the membership classes. (For information about how to use these features, see Rick Dobson's article "Making the Most of Login Controls with ASP.NET.") Usernames and passwords are stored in a SQL Server 2005 relational database, which I created by using the Aspnet_regsql.exe tool (which creates a schema that the SQLMembershipProvider can use—see Visual Studio 2005 documentation for more details).

To force the ASP .NET Web site to issue a persistent cookie to the user, I set the RememberMeSet property of the Login-Control to true and set the DisplayRememberMe property to false. This means that in a production environment, you'd need to first determine whether the user's browser is configured to accept cookies.

The FormsAuthSampleWeb site uses the AdventureWorks sample database as a data source for reports and is designed as if the AdventureWorks company decided to set up an extranet for its vendors. Thus, when you're using the Web site, it will be as if you're a vendor.

Note that when you're testing or debugging this Web site, you need to make sure you run it inside Microsoft IIS—don't use the Visual Studio 2005 built-in Web server. This point is important because, as I previously mentioned, you need to avoid the use of localhost in your URLs and instead target your machine name. You can still debug inside of Visual Studio, but make sure you open the Web site from Local IIS, not from the File System, as Figure 2 shows. You'll also want to change the StartUrl (located on the Start Options tab of the Project Properties dialog box) to reflect your machine name.

The second part of my updated sample is an implementation of the CustomSecurity project. My changes to the original version resulted in three key differences:

To work with the updated version of the CustomSecurity project, you can simply copy the updated .dll to the two Reporting Services bin directories. You'll also need to update the authentication element and add a machineKey element to the ReportServer web.config file, as the code in Listing 1 shows.

Finally, the third part of the updated sample is the set of reports we'll use to demonstrate report filtering (RSFormAuth-Sample). In the article download at the top of the page, find and open the RSFormAuth-Sample project in Visual Studio and update the Target-ServerURL property accordingly.Then, deploy the project to your report server. You'll be prompted to enter credentials; use the Admin username and password you created.

With everything deployed and configured, you should be able to start the FormsAuthSample Web site, login, and view reports from the My Account page, the ReportManager, or the Report Server URL without having to login again. If you later navigate directly to the ReportManager and your cookie has expired or you've deleted the cookie, you should be redirected to the FormsAuthSample login page. Pretty cool!

Report Filtering

Once forms-based authentication is working, we can focus on the problem of how to filter reports so that users see only their own data. The solution to this problem is actually fairly straightforward. Every time a user executes a report, Reporting Services exposes a set of global values, one of which is User!UserId, which represents the name of the person executing the report. Depending on the nature of your source data, you might be able to pass this value into your query to filter the resulting dataset. Reporting Services 2005 makes this approach a bit easier than Reporting Services 2000 does because it lets you specify parameters as "Internal." Thus, you can set the default value of a parameter to User!UserId and use the parameter in your SQL statements. All the while, the user will never see (nor be able to modify) this parameter.

To see filtering in action, first log in to the FormsAuthSample Web site as an Administrator, noting the two Vendor user accounts listed at the bottom of the Login screen. In the Report Manager, add these two accounts to the Home folder as Browsers.Then, log out and log back in to the application, this time using one of the Vendor accounts. Next, take a look at any of the three sample reports provided. As Figure 3's example shows, you'll see only the information that's allowed for the Vendor account you selected.

Putting Extensibility to Use

The broad extensibility of Reporting Services 2005 is something you don't always find in other software applications. I hope that after reviewing this article and its accompanying code samples, you're excited and comfortable with the idea of using Reporting Services both within and outside your organization.