Single Sign On in a Diverse Environment

Integration of SQL Server Reporting Services with openLDAP

Deer Valley Unified School District (DVUSD) came to Net Fusion seeking help implementing a custom Single Sign On (SSO) solution. As one of the largest school districts in Arizona, DVUSD touches thousands of students, parents, and teachers. The IT organization is responsible for managing those users and for the efficient distribution of information.

The high level business requirement was for users to log onto a portal site and view reports that were available to them. The list of reports needed to be trimmed based on the user’s access, and the security model needed to be strong enough to support external traffic. The environment consists of several disparate parts:

• A custom portal solution
• openLDAP, which houses user accounts for the portal
• Active Directory for certain internal systems
• Microsoft SQL Server Reporting Services (SSRS)

Net Fusion recommended a custom solution for several reasons. First and foremost, we have implemented similar solutions for customers and have seen that they offer the ultimate in extensibility. We have seen some of those solutions work in production environments for over five years; this is a strong indication of reliability, the strength of the security model, and the ability of the solution to continue to map to business requirements.

Additional reasons included:

• SSRS supports multiple authentication modes, but it is ideally used with Active Directory, not openLDAP.
• The openLDAP store to be used was not fully self-describing.
• Custom rules needed to be applied to report security; group or user permissions were not always flexible enough.
• Direct access to the SSRS website presented too many options to the user; a simpler view was desired.

Implementation

Certain implementation details (e.g. network topology and encryption methods) have been omitted to preserve the private integrity of the customer’s configuration.

Authentication

Net Fusion began by creating a simple .Net web service with a Microsoft SQL Server database. A web service only requires the consumer to understand XML with no knowledge of the underlying implementation. The service is private and only available to a short list of internal callers. Each caller has the ability to invoke the service and request a security token. The token can then be used as an assertion of user identity in subsequent operations. However, the token does not contain any security information; this is all mapped in a database which provides clean expiration of tokens, an audit path, and reliability (versus an in-memory solution).

This token is then used to authenticate the user against a new website created for this solution. Transparent to the end user, their openLDAP profile is retrieved and access to SSRS reports is determined on the fly. They are then presented with a list of reports available to them.

Viewing Reports

Upon selecting a report, the user’s access to that report is again validated. Details of the report are retrieved directly from the SSRS database. It’s important to note that the solution only adds metadata to the reports; it does not replace information. This means that the solution can evolve along with SSRS.

The application then loads the Microsoft Report Viewer control for SSRS. The report is loaded on the server, and queried for the list of parameters it needs. Certain parameters must be hidden from the user. For example, a user must not be allowed to enter their own job title, as a malicious/incorrect value might completely change the results of the report and/or reveal privileged information. The application draws information from the user’s profile to fill the needed hidden parameters. Other parameters can be entered by the user; this entry is done using the Report Viewer control with no custom code required.

The user cannot browse to the SSRS server directly; it is not exposed publicly. When the report executes, it does so using a privileged Active Directory account that is allowed to access the reporting server. This leverages the impersonation capabilities built into the Report Viewer control, again fulfilling the goal of using existing security systems.

Controlling Access to Reports

Net Fusion created a simple administration user interface (UI) to allow a select group of individuals to configure access to each report in the SSRS database. Access can be designated by user, group, or a custom expression that can test any attribute of the user profile.

In addition, security rules that apply to the portal site can also be mapped back to a report, allowing similar nomenclature and group access to be shared across applications. This is accomplished via a SQL Server linked server connected to the portal’s MySQL database.

Access to the administration site is controlled using the same SSO mechanism; this keeps workflows from being interrupted as administrators switch between the portal and the new application.

Performance and Scalability

The application is designed to use out-of-process session state, which removes any programmatic barrier to load balancing. Caching is used to minimize calls to openLDAP and SQL Server; caches are automatically refreshed (even across multiple sites) when a change to report security is made by an administrator. The overall footprint of the solution is small, and easily handles the high traffic generated by the portal. Of course, as the number of reports grows, the hardware running SSRS will have to be scaled accordingly.

Visual Customization

The user-facing pieces of the site are simply best-practice ASP.Net pages, so branding and visual customization can be applied quickly and centrally.

Conclusion

DVUSD was able to rapidly deploy a solution which met all business and technical goals by choosing to make Net Fusion Services a trusted partner.