ne of the most useful features of Microsoft Reporting Services is its extensibility model. Just about any aspect of Reporting Services can be custom-tailored to meet your specific requirements. Part one of this two-part series explains how to replace the default Windows-based security model of Reporting Services with forms authentication security. First, you’ll learn the ropes of implementing forms authentication security and how you can leverage it for Web-based reporting. Then you’ll enhance the form’s authentication extension by adding role-membership features to simplify the security maintenance.

With Reporting Services (referred as RS for short through the rest of this article), your reports are Web-enabled by default. Once a report is deployed to the report server, it can be requested in one of two ways:

• URL addressability: The report is requested by submitting an HTTP-GET request to the report server. In its simplest implementation, URL addressability can be initiated by clicking on a static hyperlink in which the report URL has been embedded. For example, to request the sample customer orders report, the hyperlink can be set as follows:

  http:///ReportServer?//Customer Orders, where

  ComputerName specifies the machine name where the report server is installed. FolderPath specifies the full folder path where the report is deployed.

• The RS Web service is where the report is requested by submitting SOAP request to the RS Web service.

What follows is a discussion of how both options stack against each other.

URL Addressability
URL addressability provides better user experience because it supports all RS interactive features, such as drilldown, drillthrough, document maps, etc. In addition, when a report is requested by URL, the report server can optionally generate the handy HTML Viewer toolbar, minimizing your development effort by automating mundane reporting chores, such as parameter handling, paging, searching, zooming, etc.

On the downside, URL addressability presents a potential security risk. First, it mandates a direct access to the report server. Typically, with URL addressability, the HTTP-GET request is initiated on the client-side of the application and submitted directly to the report server. Second, a malicious user could easily find out the report URL and try to exploit it?for instance, generate a confidential report by guessing the report parameters. For this reason, you should always take extra steps to protect sensitive reports that can be requested by URL. For example, you can implement horizontal data filtering at the data source based on the user identity, as the sample customer orders report will demonstrate.

RS Web Service
The RS Web service excels in functionality and flexibility. With URL addressability, you are limited to requesting reports only. The RS Web service exposes the full report server feature set through a vast number of SOAP APIs, allowing you to programmatically manage the report catalog. For example, you may need to programmatically generate a report and upload it to the report catalog. In this case, once the report definition file is generated, by using XML DOM, the client can call the CreateReport SOAP API to deploy the report.

Moreover, the RS Web service supports flexible reporting scenarios. You can generate reports on the server-side of an ASP.NET application or report off custom data structures produced in the application middle tier, like ADO.NET datasets. Or, you can generate reports in an unattended mode. Finally, RS Web service provides a more secure environment since the report request is not automatically exposed to the end user. For instance, strict security requirements may dictate that certain business rules must be validated before a report is generated. To respond to this need, an ASP.NET application can verify the report request on the server-side of the application before submitting it to the report server.

The major tradeoff of using the RS Web service is that it doesn’t support report interactive features. It’s also more involved than URL addressability because you need to take care of certain reporting tasks taken for granted when reports are requested by URL, such as loading the report images, rendering the report to the end user, and cookie management.

Internet Reporting
Spend some time carefully weighing both access options against your reporting requirements to find the best match. Generally, I recommend URL addressability whenever possible because of its easier implementation and interactive feature support.

The two access options are not mutually exclusive. For example, while a Web application may render most of its reports via URL, some reports may need to be generated on the server-side by calling down to the RS Web service.

Security Considerations
Many Web developers will opt for URL access to provide the best user experience to their users and minimize the RS integration effort. However, URL addressability presents a unique challenge for Internet-based reporting; by default RS is configured to use Windows security, so the user is authenticated and authorized based on its Windows identity. This fine for intranet applications where the Report Server and the end users are usually on the same domain and it?s possible to take advantage of the Active Directory infrastructure already in place. However, Windows-based security is almost always not a good option for Internet-facing applications. First, maintaining creating and maintaining thousands of Windows user accounts can overwhelm the network administrator. Second, the users will be confronted with the standard Windows logon dialog which pops up when a Web site configured for Windows security is requested and the user belongs to a different domain than the Web server.

For many Web applications, custom application security models provide the practical means to authenticate and authorize users. For example, ASP.NET developers are well familiar with the ASP.NET forms authentication mechanism.

The administrator configures the entire site or only a portion as protected. If the end-user requests a protected resource and has not been previously authenticated, s/he is re-directed to a logon page that the developer has created beforehand. Once the logon page collects the user credentials, it verifies them against a user profile store, typically located in a database. Upon successful user authentication, a ticket is issued in a form of a session cookie. The ASP.NET forms authentication framework verifies this authentication ticket with each subsequent request.

RS Custom SecurityFrom a security perspective, when you’re report-enabling your Internet applications, you should ideally be able to plug in RS seamlessly into the custom security model that your Web application uses. Can you integrate RS with Web applications that leverage custom security and require URL addressability? You bet. To accomplish this, you need to replace the default RS Windows-based security with a custom security extension.

By definition, a custom security extension is nothing more than a .NET assembly that implements some required security-related interfaces. These interfaces are defined in the Microsoft.ReportingServices.Interfaces assembly found in the C:Program FilesMicrosoft SQL ServerMSSQLReporting ServicesReportServerin folder, as shown in Table 1.

Table 1. Required interfaces for custom security extensions.

In addition, since both IAuthenticaionExtension and IAuthorizationExtension interfaces inherit from the IExtension interface, you need to implement the IExtension methods so your custom security extension can be successfully registered with the report server. The IExtension interface specifies only two methods:

• SetConfiguration: The report server calls SetConfiguration to pass the extension configuration section as specified in the configuration file. For example, the sample custom security implementation uses a configuration section in the RSReportServer.config file ( element) to specify the administrator credentials and connection string to the user profile store. Before the report server calls the IAuthenticationExtension methods, it gives the extension a chance to configure itself by passing the configuration section as a XML fragment. In this case, the authentication extension loads the section in XML DOM and sets the appropriate class-level members.
• LocalizedName: This returns the name of the extension based on the thread culture. Neither the report author nor the administrator are meant to see the names of the authentication and authorization extensions. Therefore, the implementation of LocalizedName returns null. However, if you were to develop a custom data extension, you’d implement LocalizedName to return a descriptive and even more localized name of the extension, which would be shown both in VS.NET (when configuring the report data source) and the report manager.

Understanding RS Forms Authentication
Just like the RS Windows-based authentication or other popular security models, your RS Forms Authentication security extension should provide the necessary infrastructure for:

• User authentication: During the authentication stage, the custom security extension determines the user’s identity by obtaining it from a trusted authority?by querying the user profile store. A successful outcome of this phase is an authentication ticket in a form of a session cookie sent to the client.
• User authorization: Authorization occurs after authentication and determines what the user can do. For example, if the user has submitted a report request, during the authorization phase your custom security extension must verify if the user has indeed rights to do so. Typically, to simplify the security maintenance effort, the report administrator will use the Report Manager to create role-based security policies just as s/he would if Windows-based authentication is used.

Figure 1 depicts an overview of how the RS custom security extension works.

Figure 1. The RS Custom Security Extension: This sequence flow is responsible for authenticating and authorizing all requests going out to the Report Server.

.NET developers familiar with the ASP.NET forms authentication will probably find the RS custom security model similar. Figure 1 shows sequence flow between the client and the report server, configured to use a custom security extension.

User Authentication

1. The client application displays a logon form to prompt the user for credentials, (user name and password). In the case of ASP.NET applications, ASP.NET forms authentication can be used to redirect the user to the login form automatically if the user has not been authenticated.
2. Once the user credentials are collected, the application invokes the LogonUser RS Web method to log the user to reporting services.
3. Next, the report server asks the custom security extension to authenticate the user by calling its implementation of IAuthenticationExtension.LogonUser. How the custom security extension does this is of no concern to the report server. Typically, with a large number of users, a database store will be used to store the user profile data and credentials.
4. If the user is successfully authenticated, the LogonUser SOAP API call returns a ticket in the form of a session cookie which the report server expects to find in subsequent calls from the client. When a browser is used as a client, the session cookie will be automatically passed back with all subsequent requests. When other types of clients are used, you will need to take an extra step to pass the cookie explicitly with the call to the report server.

User Authorization

1. The client submits a report request either by URL or SOAP.
2. The report server asks the custom security extension to authorize the user request by calling the appropriate IAuthorizationExtension.CheckAccess overload method.
3. If the request is successfully authorized, the report server generates the report and sends it back to the client.

Although the scenario depicted in Figure 1 specifically refers to requesting reports, any type of action against the report catalog will be subject to custom authorization checks. For example, if the client is the report manager, each time the user initiates a new action from the portal, the report server will call down to the custom extension to authorize the request.

Introducing the Adventure Works Portal
This article uses the example of a fictitious company called Adventure Works Cycles. This company would like to enhance their Internet Web portal by allowing its customers to generate reports. Adventure Works manufactures and sells bikes and bike accessories throughout the world to individuals and retails stores. As typical with many popular online stores, one of the first reports which should be added to the Adventure Works Internet portal is the order history report which will allow the customers to see the items they have ordered in the past (see Figure 2).

Figure 2. The Order History Report: This report has interactive features and includes the HTML Viewer toolbar.

As shown on Figure 2, Adventure Works has decided to leverage the interactive features supported by RS to provide the best reporting experience to their customers. Specifically, the report allows the end user to drill down a given order by clicking on the plus sign to expand it and see the order items. In addition, the report includes the handy HTML Viewer toolbar at the top that allows the users to easily export the report in different popular formats, such as PDF, Excel, etc. Had this report been authored to take parameters, the HTML Viewer would have included placeholders for each parameter. Please note that with RS all this comes out of the box without writing a single like of code.

Report interactive features are available only with URL addressability and require direct access to the report server. For example, when the user drills down a given order, the HTML Viewer framework submits an HTTP-GET request to the report server to fetch and render the order details. Therefore, to integrate successfully the Web front-end with RS, you need to implement the following reporting requirements:

1. The customer has to be authenticated before s/he can request reports.
2. Report-enabling the application should not compromise security. Implementing a custom security extension enforces restricted access to the report server.
3. Authenticate end-users against a user profile store. In this case, the profile store is represented by the table Individuals in the AdventureWorks2000 database.
4. Implement horizontal data filtering at the data source based on the user identity to ensure that a customer can see only her orders. The customer orders report passes the customer identifier to the WHERE clause of the report query. The custom security extension uses the customer identifier to authenticate the user, and you obtain the customer identifier from the standard RS User collection (User!UserId).

Implement the necessary infrastructure to provide administrator level access to the report server using a designated admin account. For easier maintenance, the admin credentials are specified in the RSReportServer.config file.Support assigning customers to application-defined groups for easier maintenance. Creating role-based security policies for individual Web customers is often impractical. Instead, a better approach is to assign customers to groups, e.g. Gold and Platinum groups.

Implementing RS Forms Authentication
Developers familiar with interface-based programming should find implementing RS custom security straightforward. I highly recommend you review the “Using Forms Authentication in Reporting Services” whitepaper by Microsoft for additional information about RS custom security.

Setting Up the Forms Authentication
Once you download and unzip the sample code package accompanying this article, open the FormsAuthentication.sln file in VS.NET 2003. You will see three projects: AdventureWorks.Extensibility, Reports, and Web. The customer security extension is implemented in the AdventureWorks.Extensibility project. The Reports project is a business intelligence project that contains the customer orders report sample. Finally, the Web project simulates the Adventure Works Web portal.

Figure 3. RS Role-based Security: Configure RS role-based security by logging as an administrator to the Report Manager portal.

Since the most difficult part in implementing custom security is configuring the security extension properly, detailed step-by-step instructions are provided in the readme.htm file. In addition, you can find copies of the report manager and report server configuration files in the Configuration Files folder. Please use these files as reference only! Do not just copy them and replace your configuration files.

Once the custom security extension is configured properly, it is time to configure the RS role-based security and grant selected end users Browser rights to the folder that contains the Customer Orders report (the folder name is FormsAuthentication by default). To configure the RS security, follow these steps:

1. Open your browser and navigate to the report manager portal. Make sure to specify the computer name (not localhost): http://
2. You should see the report manager login form (the UILogon.aspx page in the AdventureWorks.Extensibility project) as shown in Figure 3. Log in using admin credentials (both ‘admin’ as user id and password).
3. Navigate to the FormsAuthentication folder. If you don’t see it, deploy the Reports project to the report server.
4. Click on the Properties tab and then on the Security link. Grant a few customers Browser rights to the Forms Authentication folder, as shown in Figure 4.

Figure 4. The Forms Authentication Folder: Use the Report Manager to set up role-based security with RS Forms Authentication.

Each time you add a new customer, the report server will call IAuthenticationExtension.IsValidPrincipalName in the custom security extension to verify the user name. This implementation of IsValidPrincipalName simply queries the Individual table in the AdventureWorks2000 database to determine if a record with this customer identifier is found. Though this code sample uses the customer identifier (column CustomerID) to identify Web customers, you can choose whatever identifier you want, e.g. Employee SSN.

Please note that the purpose of the IsValidPrincipalName method is just to verify that a user with such an identifier exists, it is not meant to authenticate the user. This is similar to verifying the user identity or group name when the RS Windows-based security is used. The actual user authentication is performed in the IAuthenticationExtension.LogonUser method.

Once the Adventure Works customers have been granted access, they are all set to request reports. However, to do so they have to first log in to the Adventure Works Web portal.

User Authentication
To request a report, the end user has to navigate to the default.aspx page. The actual customer orders report is rendered with the help of the report viewer control, included with the RS samples. The report viewer control renders the report in an IFRAME by submitting an URL request to the report server.

The Adventure Works Web portal is configured for forms authentication. If the user has not been already authenticated, the user is redirected to the Logon.aspx page, which looks similar to the report manager login page shown in Figure 3. The user credentials consist of the customer identifier (the same as the one you use in the report manager) and password (this example uses the PasswordSalt column in the Individual table for the password value).

Once the user submits the page, the application needs to authenticate the user. Typically, you would perform the same authentication check as you would in the custom security extension. True, this will result in duplication of code but currently RS doesn’t have any provisions to share the application authentication mechanism, not even ASP.NET forms authentication. To make things simpler, this example doesn’t verify the user credentials at the application level.

To log in the user to the report server, the application must invoke the LogonUser SOAP API. The Login.aspx page does this:

ReportServerProxy server = new ReportServerProxy();server.Url="/ReportService.asmx";server.LogonUser(customerID, TxtPwd.Text, null);

First, the code initiates the report server proxy. Next, it calls LogonUser API. It is an open-ended argument you can use to pass an additional info that you need to authenticate the users. For example, suppose the customer identifier is not unique across all customers and you have to use an additional identifier, such as the company name, to identify the customer uniquely. In this case, you can pass the company name under the third argument.

As you can see, the signature of the LogonUser API matches exactly the signature of the IAuthenticationExtension.LogonUser method. This should come to no surprise considering the fact that in response to the LogonUser SOAP API, the report server calls your implementation of IAuthenticationExtension.LogonUser to allow the custom security extension authenticate the user. As a result, all arguments that you pass to the LogonUser SOAP API are be passed to IAuthenticationExtension.LogonUser.

This implementation of IAuthenticationExtension.LogonUser is straightforward. It executes the uspValidateUser stored procedure in an attempt to find a customer match for the given customer identifier and password and returns a Boolean result to the report server. If IAuthenticationExtension.LogonUser returns true, the report server is proceed by issuing an authentication ticket in the form of a cookie. To relay the cookie to the browser after the call to LogonUser, overwrite the RS Web service proxy as shown in the Microsoft Forms authentication sample.

As with ASP.NET forms authentication, you can set the cookie properties using the familiar declarative syntax in the report server web.config configuration file. For example, the following configuration sets the name of the cookie to sqlAuthCookie, its timeout to 60 minutes, and tells the report server to renew the cookie lifetime with each report request (slidingExpiration=”true”).

Because the RS forms authentication relies on cookies, you need to first verify whether the browser passes the cookie to the report server when troubleshooting authentication errors. When a subsequent request is submitted to RS, the report server checks for the existence of the cookie. If the cookie is not present, the report server redirects the user to the page specified in the element. For example, if the cookie has expired the browser won’t send it with the report request and the user will need to log in again.

When the report server fails to find the cookie, it redirects the user to the Logon.aspx page. Having to deal with so many authentication-related login pages may be confusing. Table 2 clarifies their purpose.

Table 2. Login pages and their purpose in forms authentication.

The Logon.aspx page simply informs the user that the cookie session may have expired and provides a link to the application login page (Login.aspx). Of course, the Logon.aspx page and give the user an option to log in to RS just like s/he would do by using the application login page. This could be useful if there is no application front-end to take care of authenticating the user or where you have static HTML pages with report hyperlinks.

User Authorization
Upon inspecting the incoming request, if the report server validates the authentication cookie successfully, it proceeds by asking your extension to authorize the user request by calling one or several of the CheckAccess overloads. The type of CheckAccess overload called depends on the type of the attempted action against the RS report catalog. For example, if the user requests a report, the report server calls the following CheckAccess overload:

public bool CheckAccess(string userName, IntPtr userToken, 	byte[] secDesc, ReportOperation requiredOperation)

All CheckAccess overload methods have the same signature except the last enumeration argument, which specifies the type of the action attempted. For this reason, it makes sense to re-factor the authorization logicin the helper function CheckOperations.

First, the code in this function determines whether the user name matches the administrator name. If it does, access is granted to the user regardless of the requested operation. If not, the CheckOperations proceeds by inspecting the role-based security policy defined for this user.

The user password is not passed to CheckAccess because the report server has already asked your extension to authenticate the user when the request was first submitted. Once the user is authenticated, the report server inspects the authentication cookie to find out if the user has been authenticated or not. This eliminates the need of authenticating the user with each request.

The report server may decide to call a single CheckAccess overload several times. In the previous example, when the user requests a report, the report server first calls the CheckAccess overload to determine whether the user has permission to the given report (requiredOperation is ReadProperties). The report server then calls the same overload to determine whether the user has rights to execute the report (requiredOperation is ExecuteAndView). The authorization extension tells the report server if the user is permitted to perform the requested action. If the user is not authorized, an error “Not authorized” message is sent to the client.

The authorization extension determines whether the user is authorized to perform a given action by inspecting the role-based policy set up for this user. When the report server calls CheckAccess, it also passes the role-based security policy (as a byte array under the secDesc argument) defined for the catalog item. For example, suppose the user has requested the customer orders report. Under the role-based policy shown in Figure 4, the security descriptor is deserialized, after which the access control list collection contains four elements, one for each user defined. Each user element has additional collections that contain the permissions defined for all supported operations, e.g. FolderOperation, ReportOperation, and so on. The report server simply tells you “Here is the item security policy that you have defined in the report manager (or programmatically) for user A. Now please tell me, is this user really authorized to perform action B?”

Developers will probably appreciate the flexibility that the RS custom security framework provides. At its simplest implementation, authorization logic just needs to enumerate through the access control list in an attempt to find a match for the logged on user. At the same time, it supports more involved authorization scenarios. Part II of this series will demonstrate how to modify the authorization extension to support role membership.

The last authorization detail that deserves more attention is the IAuthorizationExtension.GetPermissions method. The report server calls this method when the GetPermissions SOAP API is invoked. For example, if the user opens the report manager and navigates through the folder structure, the report manager calls the GetPermissions SOAP API to find out if the user has the required permissions to view or change catalog items. When custom security is used, the report server redirects the call to your implementation of IAuthorizationExtension.GetPermissions. Again, the report server passes the role-based security policy as defined in the report catalog. In its simplest form, the GetPermissions implementation filters out the permissions defined for the given user and returns them to the report server. More complicated authorization requirements may call for validating additional business rules to determine if the role-based security policy set up for this user should be honored or not.

Custom Security Implementations
Often, internet reporting requirements will call for URL report addressability without compromising security. Thanks to the RS extensibility architecture, developers can replace the default RS Windows-based security with custom security implementations in the form of security extensions.

Part II of this series will discuss how to reduce the management effort for maintaining hundreds of Web users by assigning them to application-defined roles.