Harden MS Reporting Services Using Custom Extensions, Part 2

Harden MS Reporting Services Using Custom Extensions, Part 2

n Part 1 of this series, you learned that, by default, the Report Server uses Windows-based security to authenticate and authorize incoming requests based on the Windows identity of the interactive user. While Windows-based security works well for intranet applications, it is usually impractical for Internet-facing applications. This leaves you with two implementation choices.

The first option is to generate reports on the server-side of the application by calling the RS Render SOAP API. The advantage of this approach is tighter security. The report request cannot be hijacked and exploited since the report is generated entirely on the server-side of the application. On the downside, reports generated by SOAP will have a reduced feature set since most of the RS interactive features?including the report toolbar?rely on URL addressability.

The second option is to replace the RS Windows-based security model with a custom security extension in order to request reports by URL. The user authentication and authorization is then handled by a custom security extension which can be integrated with the application security model (the example in Part 1 used ASP.NET Forms authentication).

As useful as it is, this example custom security implementation has one caveat. It requires you to set up individual security polices for each user, which isn’t very practical for Web applications that may support thousands of users. Thankfully, application roles provide a practical means for you to group users with identical permissions?in the same manner that Windows groups simplifies maintaining Windows-based security.

Understanding Role-based Membership
Your application may already support assigning users to application-defined roles. For example, if your organization is doing business with several companies, you need to allow employees of these companies to request reports without compromising security. In this case, you can simplify security maintenance by assigning permissions on a per company basis?as opposed to by individual employees. In this example, your application roles will be scoped at the company level.

To bring a touch of reality to this code sample, assume that Adventure Works has introduced several levels of customer membership based on the customer order volume, e.g. Platinum, Gold, Silver, and Regular. Please note that it is entirely up to developer to determine the actual implementation and semantics of the application roles. The business rules behind the role membership are of no importance to the role-based implementation.

Adding another level of flexibility, this role-based membership implementation supports assigning users to multiple roles. The granted permissions are additive as well, which means that the user receives a superset of the permissions assigned to all the roles the user belongs to, plus the individual permissions assigned explicitly to the user.

Here are the high-level implementation goals for this custom role-based authentication:

  • Support for assigning users to multiple roles.
  • Allow the report administrator to set up both individual- and role-based security policies.
  • Authorize the user by granting a superset of individual- and role-based permissions assigned to the user.
  • Improve performance by caching the user role in the ASP.NET cache object.

Implementing Database Schema for Role Membership
As it stands, the Adventure Works database schema doesn?t include support for role membership (at least not for customers). For this reason, you’ll need to add two additional tables, CustomerRole and Role, as shown in Figure 1.

Figure 1. Two New Tables: The Adventure Works database schema now supports role membership.

You may find this role membership data model familiar. The table Role holds the application roles, while the table CustomerRole defines to which roles a given customer belongs. Remember, a customer may belong to more than one role.

There are two scripts in the Database folder of the code download for this article. The ddl.sql script creates the new tables and stored procedures, while the data.sql script populates the new tables with some sample data. Once again, please note that the Report Server doesn’t impose any restrictions on the role membership schema, so please feel free to enhance the schema to meet your requirements, like supporting nested roles, subsets of permissions, etc.

Implementing Role Authentication
There are two steps to updating the code extension to support role membership. First, you need to change the IAuthenticationExtension.IsValidPrincipalName. The second step is to enhance IAuthenticationExtension.LogonUser.

1) Validating the Principal Name
As you may recall, the Report Server calls IsValidPrincipalName each time a change is made to a security policy?for example, when you open the Report Manager, navigate to a folder, click on the New Role Assignments button, and assign a user to a given role(s). Before the new security policy is created, the Report Server calls IsValidPrincipalName in your custom extension to validate the principal name. However, the Report Server doesn’t validate the semantics of the principal name (role or individual user). In fact, the Report Server itself has no provisions to handle role assignments. This custom security extension is responsible for enforcing the role membership authentication and authorization rules. Once the security extension acknowledges that the principal name is valid by returning true from the IsValidPrincipalName call, the Report Server simply proceeds by recording the changes to the security policy in the report catalog.

Therefore, to validate the principal name, change the uspIsValidPrincipalName to handle both possibilities?the principal name as an individual user or as an application-defined role. This requires a simple change to the uspIsValidPrincipalName stored procedure:

CREATE PROCEDURE uspIsValidPrincipalName (  @PrincipalName nvarchar(50))ASIF ISNUMERIC(@principalName)  = 1   SELECT CustomerID FROM Individual WHERE       CustomerID = CAST(@PrincipalName AS INT)ELSE    SELECT RoleID FROM Role WHERE Name = @PrincipalName 

In the Adventure Works Web portal, the passed principle name could be either the customer identifier (individual policy change) or role (role policy change), you need to query the appropriate table. If a match is found, IAuthenticationExtension.IsValidPrincipalName returns true and the Report Server happily records the policy change.

Interestingly, even if a single policy is changed (created, modified, or deleted) the Report Server validates all security policies assigned for this item. This is because the Report Server rebuilds the entire security descriptor for every item that is changed. This works in the same way as the RS Windows security extension does. For example, if the suppose you create a new policy assignment to grant browser rights to a new principal for a given folder/ In this case, the Report Server calls IsValidPrincipalName as many times as policies have been assigned for this folder passing the principal name.

Now, suppose you?ve granted browser rights to all members of the Gold role. To view reports in the FormsAuthentication folder, Figure 2 shows what the Report Manager Security tab would look like.

Figure 2. The Report Manager Security Tab: Use the Report Manager to maintain both user and role security policies.

Granted, once you implement custom role-membership features in the custom security extension, you can remove the individual policies (say, customers 11003, 25863, and 28389) altogether. Figure 2 merely demonstrates that both individual and role security policies are supported.

2) Authenticating the User
Once the Web application collects the user’s credentials, it calls the LogonUser SOAP API and passes them to the IAuthenticationExtension.LogonUser method in the custom security extension.

You’ll need to decide upfront at what point your custom security extension retrieves the user roles. Of course, this has to happen before the user request is authorized. One approach is to retrieve the roles in each CheckAccess overload. However, this can generate excessive database traffic since a single action against the report catalog, for instance, requesting a report, may result in several CheckAccess calls. For this reason, it’s more efficient to retrieve the user roles in the IAuthenticationExtension.LogonUser and cache them in the ASP.NET cache object:

public bool LogonUser(string userName,        string password, string authority) {    string[] roles = null;   if ((0==String.Compare(userName, m_adminUserName, true,           CultureInfo.CurrentCulture)) &&           (password == m_adminPassword))      return true;   if (!AuthenticationUtilities.IsValidUser(userName,        password, m_connectionstring)) return false;  // user is authenticated, now get user roles  DataSet dsRoles = Util.GetUserRoles(userName);    if (dsRoles.Tables[0].Rows.Count > 0)    {     roles = new string[dsRoles.Tables[0].Rows.Count];     for (int i=0; i < dsRoles.Tables[0].Rows.Count; i++){       roles[i] = dsRoles.Tables[0].Rows[i][0].ToString();     }        // cache the user roles     HttpContext.Current.Cache.Insert(userName, roles);   } // if (dsRoles?.   return true;}

Once the user is authenticated successfully, retrieve the user roles by calling the GetUserRoles helper function. Next, load the roles into a string array, which is cached in the ASP.NET cache object. Since the security extension will always be loaded in the Report Server application domain, you know for certain that HttpContext.Current provides access to the ASP.NET cache object.

Author's Note: Because this example does not specify explicit cache expiration, the cached roles will remain in the ASP.NET Cache object for the lifetime of the Report Server application domain. If your application serves many concurrent users and you are concerned with excessive memory consumption, you may want to consider implementing a cache expiration policy.

Implementing Role Authorization
When the Report Server receives a request for a given action against the report catalog, it asks the security extension to authorize the request by calling one or more CheckAccess methods in the custom authorization extension. Which CheckAccess overload(s) are called depends on the type of the action requested.

The Report Server passes the principal name (the customer identifier in this case) and the security policy descriptor associated with the given report catalog item to IAuthorizationExtension.CheckAccess so the custom extension can evaluate the security policy and take a stand. The Report Server doesn't know or care if the security policy is an individual- or role-based policy. For this reason, the security descriptor may contain both individual- and role-based polices.

Enhancing CheckAccess
Implementing role-based authorization means checking not only if the user has been assigned explicit rights to the report catalog item, but also if the user belongs to roles that are permitted to perform the requested action. Fortunately, this enhancement to the CheckOperations helper function is trivial:

private bool CheckOperations(string principalName,         AceCollection acl, object requiredOperation) {    // check if the individual login has been   // granted rights to perform the operation  if (IsUserAuthorized(principalName, acl, requiredOperation))        return true;  // No individual policy established.   // Next, check user role membership.  string[] roles=HttpContext.Current.Cache[principalName]              as string[];  if (roles!=null){      foreach (string role in roles){       if (IsUserAuthorized (role, acl, requiredOperation))            return true;      }  }  return false;}

First, the code checks whether the user has an individual security policy assigned. For example, glancing back to Figure 2, if customer 11003 requests a report, then IsUserAuthorized returns true because this user has explicit rights assigned to browse reports. This wouldn't be the case for customer 14501 because this customer doesn't have an explicit security policy defined. However, 14501 may have been assigned to a role with the required permissions. To verify customer role membership permissions, the code retrieves the roles associated with the customer from the ASP.NET cache object (remember we cached the roles in LogonUser). Next, the code iterates through the roles in an attempt to find a role permitted to perform the action.

Enhancing GetPermissions
The Report Server also calls IAuthorizationExtension.GetPermissions when the GetPermission SOAP API is invoked. For example, the Report Manager needs to know if the user has the rights to browse a given folder so it can hide or show that folder. The Report Manager calls GetPermission in order to update the user interface based on the user?s security policy. Use the following code to make IAuthorizationExtension.GetPermissions role-aware:

 string[] roles = HttpContext.Current.Cache[userName] as string[];if (roles!=null)  {    foreach (string role in roles)   {      GetPermissions(role, acl, permissions);  }}

This is almost identical to the CheckAccess enhancement. The only difference is that, instead of calling IsUserAuthorized, it calls the GetPermissions helper method. GetPermissions checks the security descriptor for the given principal (user or role) and returns a superset of all permissions associated with the principal.

Testing Custom Security
If you've followed the setup instructions in the readme.htm file, all you have to do is open the Visual Studio.NET solution file (FormsAuthentication.sln) and compile the AdventureWorks.Extensibility project. For your convenience, there is a post-build event in the AdventureWorks.Extensibility project setting (Build Events) to copy the binary to the correct Report Server and Report Manager folders.

Next, open the Report Manager Web application and log in as an administrator (user name: 'admin,' password: 'admin'). Navigate to the FormsAuthentication folder and set up a role-based policy by assigning browser rights to a given role. If you want grant this user permission to use the Report Manager as a report rendering tool, grant the role Browser rights to the Home folder as well. Without this, the user cannot navigate to the FormsAuthentication folder since Home is the root folder.

Figure 3. Debugging the Security Extension: Attach to the ASP.NET process to debug the custom security extension under the Report Manager.

To debug the security extension from the Report Manager follow these steps:

  1. Run the Report Manager.
  2. In Visual Studio.NET, open the Processes window (found under the Debug menu). Select the Show System Processes checkbox. Locate the ASP.NET process (aspnet_wp for Windows 2000 or Windows XP). You may have several instances of the ASP.NET process running. Select the one which hosts the Reports application, as shown in Figure 3.
  3. Set breakpoints in the security extension, e.g. in LogonUser.
  4. Log on in the Report Manager using the credentials of the user you want to test. At this point, your breakpoint in the LogonUser should be hit.

Debugging the custom security extension when running the sample Web application is even easier. Open the FormsAuthentication.sln solution. Set the Web application as a startup project and default.aspx as a start page. Hit F5 to debug the solution. The application Forms Authentication security mode will redirect you to the Login.aspx page. Once you enter the user credentials, Login.aspx calls the LogonUser SOAP API, which in turn calls IAuthenticationExtension.LogonUser in our custom security extension. At this point your breakpoint in LogonUser should be hit.

Troubleshooting Custom Security
Judging by the feedback I get from the RS public forum, custom security is one of least understood features of Reporting Services. So here are some tips which may save you hours of debugging and head scratching when troubleshooting custom security.

To start with, I can't emphasize this fact enough: Just like ASP.NET Forms Authentication, RS custom security is cookie-based. If the browser doesn't pass the cookie back to the Report Server, the user will be redirected to the Logon page. What follows is a list of the most common reasons the browser fails to pass the cookie back and how to avoid or workaround them.

  • Using localhost
    Using localhost to launch the client tricks the browser into believing that the Report Server and your Web applications are on different domains. Instead, when testing custom security locally, specify the machine name, e.g. http:///adventureworks/default.aspx as opposed to http://localhost/adventureworks/default.aspx.
  • Restrictive Browser Policies
    Sometimes, the authentication cookie is not transmitted back because the browser privacy settings are configured to block cookies. This may sound like a no-brainer, but you will be surprised how often folks forget to glance at the browser status bar to see if the authentication cookie simply can't "get through." If this is the case, assign the Report Server Web site to the Trusted Sites zone.
  • Script Synchronization Issues
    Another reason for authentication failure is when the request to the Report Server is submitted before the browser retrieves the authentication cookie. This one got me really bad when implementing custom security in one of my projects. It turned out that, for some reason, the developer didn't want to use the ReportViewer control to render the report. Instead, the developer had decided to use a client-side Javascript function to change the window.location to the report URL. Eventually, I traced this down to a synchronization issue?the browser would submit the request before the page was fully loaded. The resolution in such a case is to use ReportViewer or make sure that your client-side script is called from the browser window.onload event.
  • Cross-domain Security Issues
    Suppose you have installed the Report Server and the Web application on two different domains?like, the Report Server machine belongs to the domain, while the Web application is on the domain. For security reasons, Internet Explorer doesn't permit cross-domain cookies, so the authentication cookie won't be sent to the Report Server.

    The most obvious solution to this problem is to move the applications to belong to the same domain You'll also need to change the TranslateCookie method in the overloaded proxy class and set the cookie Domain property to your domain ( in this example). Please note that both applications can be physically located on two separate servers as long as both machines belong to the same domain.

    If hosting both computers under one domain is not an option, another workaround is to invoke the LogonUser SOAP API on the Report Server machine. For example, once the custom application authenticates the user, it redirects to an ASP.NET page residing on the Report Server, which in turn calls LogonUser. As a result, the authentication cookie is be scoped at the domain to which the Report Server belongs. Of course, you need decide how the Web application will communicate to the Report Server that the user has successfully authenticated. This may present a security risk, so take extra steps to ensure that security is not compromised. For example, you could use IP address filtering and restrict access to the ASP.NET page that contains IP address of the Web application server.

  • Cookie Configuration
    RS Forms Authentication uses the same syntax to set the authentication cookie as ASP.NET Forms Authentication. For example, if you find out that the authentication cookie expires too soon, you may want to increase the cookie timeout. See the "Configuring ASP.NET Forms Authentication" link in the Resources section.

    If the Report Server is deployed to a Web farm environment, the cookie configuration settings of the cluster machines may not match. Check all instances of the RS web.config configuration files and make sure that you use identical cookie settings. Please see the "Forms Authentication Across Applications" link in the Resources section.

  • Issues with Non-browser Clients
    While the browser automatically sends the cookie to the Report Server, you are on your own when integrating other types of applications, such as WinForm clients, with a Report Server configured for Forms Authentication. Basically, you need to store the authentication cookie after the LogonUser call and send it back with each subsequent request (see "Using Forms Authentication from WinForm Applications").

    If you find out that the Report Manager doesn't pass back other application-defined cookies, apply RS Service Pack 1. It supports a special PassThroughCookies element to support transmitting cookies other than RS-related cookies.

When you're through troubleshooting custom security, it's time to verify that the cookie is successfully sent by the client application.

In Search of Cookies
By now, it should be clear to you that the Holy Grail of RS Forms Authentication is successful cookie management. But suppose that after following the above troubleshooting tips, the browser still prompts you with the standard Windows login dialog. The most likely reason for this behavior is that the Report Server doesn't receive the authentication cookie from the browser. Therefore, it is essential to verify that the authentication cookie has been sent back to the Report Server. You can do this using a tracing utility like tcpTrace or Microsoft SOAP Trace. Both utilities are available free of charge and work in the same way. They intercept the TCP traffic between two nodes and dump it to the screen.

Figure 4. tcpTrace: Use tcpTrace to troubleshoot RS custom security.

The steps below explain how to use tcpTrace to verify that the authentication cookie has been successfully transmitted. The steps to set up Soap Trace are similar:

  1. Once tcpTrace is downloaded and installed, run tcpTrace.
  2. Start a formatted trace from the File?>Start Trace.
  3. In the tcpTrace Settings dialog, change the destination host to your application server's name or TCP/IP address. For example, if the application is externally accessible as, enter as a destination host. If it listens on a different port than the default HTTP port (80), change the value of the destination port accordingly.
  4. Append port 8080 to the Report Server end point in the report page of your Web application. For example, in the AdventureWorks code sample, open Default.aspx and change the ReportViewer ReportPath property to http://:8080/ReportServer, where ReportServerMachineName is the Report Server machine name.
  5. Open the browser, and type http://localhost:8080/, where the AppVroot is the virtual root name of your Web application (AdventureWorks in this example).
  6. At this point, tcpTrace should intercept the HTTP request to your application and should output the trace messages. Once your application calls the LogonUser SOAP API, you should see a response message in the bottom right pane. This message should include the authentication cookie (see Figure 4). The name of the cookie should be the same as the one specified in the RS web.config file (sqlAuthCookie by default).
  7. Now, navigate to the page that requests a report (default.aspx in our code sample). If everything is OK, tctTrace should intercept the outbound HTTP request and the authentication cookie should be included in the request.

If the authentication cookie is not included in the client request, Forms Authentication will fail because the Report Server cannot find the authentication ticket. As a result, you will be redirected to the Logon.aspx page.

Now you know how to leverage RS Forms Authentication to report-enable Internet-facing applications by preserving report-interactive features without compromising security.

Hopefully, this article has demystified RS custom security and given you the practical implementation skills you need to implement custom security successfully, should your reporting requirements call for it.


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist