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:

GetUser—Returns the User Identity. In an Internet environment, you'll typically get the user identity from the Web server's http context (e.g., HTTPContext.Current.User.Identity).

IsValidPrincipalName—Called when the Report Server sets security on an item. You'll configure this method to query your custom store of users.

LogonUser—Used to submit credentials to the Report Server for authentication. After a successful call, the Report Server uses HTTP headers to pass an authentication ticket (i.e., a cookie) from the server back to the client.The client then uses this cookie for subsequent requests during the remainder of the session. Figure 1 shows a graphical flow representation of this process.

IAuthorizationExtension. This interface handles authorization through three methods:

CheckAccess—An overloaded method that determines whether a user is authorized to access an item (e.g., a report) for a given catalog operation (e.g., ExecuteReportDefinition). CheckAccess actually has 10 different signatures, so to save time, I strongly recommend using the sample code as a starting point.

CreateSecurityDescriptor—Used to serialize the access-code list applied to a report server item. Similar to an access control list in Windows, the access code list contains the access rights each user has to particular Report Server database items, such as folders or individual reports.This method is used by the CheckAccess method.

GetPermissions—Used by the Web service GetPermissions method, this method returns the set of permissions granted to a specific user for a given item.

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:

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:

The VB.NET sample (Authorization .vb) contains errors in its implementation of IAuthorizationExtension. To work around these errors, use the C# sample code or download the updated version of Authorization .vb that's included in this article's download .zip file at the top of the page via the "Download the Code" link.

The Building the Sample section of the sample directs you to generate a strong key file but doesn't tell you what to do with the key file. After you generate the file, you need to update the Strong key file path of the Project. To point to your new key file, select the Project, CustomSecurity Properties menu item, navigate to the Signing tab, then select the <Browse...> item in the Choose a strong name key file: drop-down box.

Don't use localhost in the RSWebApplication.config file or during testing. Instead, always use your machine name (e.g., http://yourmachinename/ReportServerFA).

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:

The two logon pages are left in place but they redirect the user to our Web site's login page.

The LogonUser method calls a Web service (hosted in our Web site) to verify the username and password. Note that LogonUser is now no longer invoked by end users (or our Web site), because our Web site handles user verification and the issuing of cookies. But Visual Studio or the Reporting Services Web service uses the LogonUser method when you're deploying reports from Visual Studio; the method would be called if you wanted to use a Reporting Services Web service API.

The IsValidPrincipalName method calls a Web service (hosted in our Web site) to verify usernames.

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.

Great article. Had a problem that others might come across, for which there are large volumes of info on the web-- when in fact my solution was rather simple. With the Forms Authentication Sample implemented, including Tyler's fixed VB file, BIDS could not Deploy my work to the server. While SSMS seemed fine with the forms authenticated user, BIDS did not seem to work with the new extension at all. It would over and over again prompt for "User name" and "password" in a Reporting Services Login box, yet it would not accept any valid credentials. Many many hours down the tube. Solution for me was to develop, and run Deploy, of my Report Model from BIDS on a SQL Server 2008 CTP installation on a different server. Worked on the first try. Tested from another 2005 box, does indeed appear that 2008 client tools was the fix. Hope it works for others as well.

This is good article, however, it didn't show the step by step of how to install the sample project, and besides, cannot there is no project attached to the RSFormsAuthSample download, it only has the aspx and aspx.bv files. So, a bit loss of how to implement this sample code for testing/viewing.

Hi
Thanks for finding this. We're uploading new files with the complete set of code files. Also, I see the "Rate this Article" problem you pointed out. We'll take care of that too!
Diana May
Technical Editor - SQL Server Magazine

Comments from the author:
Hello. In the article I walk through 2 “phases” – phase 1 involves Installing the Sample Security Extension that ships with SQL Server 2005. After (and only after – because there are quite a few steps/config settings/etc. that can trip you up) you have this sample extension installed and working properly, you are ready to move to phase 2 – the “Second Sample”. The actual security extension of the second sample is a complete standalone project (CustomSecurity.vbproj) and can be found in the RS_External_Code\Sample2 - IntegratedApp\FormsAuthentication directory. The web site example of the second sample is called RSFormsAuthSample – and as you pointed out, there are no associated project or solution files. This is because I have this configured as a web project to ensure the IIS (and not the VS 2005 built-in Web Server) is used. You will need to configure the RS_External_Code\Sample2 - IntegratedApp\RSFormsAuthSample folder as an virtual directory within IIS, and then open the “project” using the File-Open-Web Site menu item within VS 2005. In the Open Web Site dialog box, make sure you have selected “Local IIS” (and not File System), and then select the RS_External_Code\Sample2 - IntegratedApp\RSFormsAuthSample folder. Assuming you’ve configured this folder properly in IIS, it should open up in VS 2005 – and you’ll be ready to go.
Tyler

Thanks so much for your feedback jamie@jtu.com! I will bring up the possibility of updating this article at our next article acquisitions meeting.
Megan Bearly
Assistant Editor, SQL Server Magazine
mbearly@sqlmag.com

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More