Using Project Server Security in SQL Server Reporting Services Reports
Summary: Learn how to use the programmability features of Microsoft SQL Server 2008 Reporting Services to incorporate the security model of Microsoft Office Project Server 2007 in your reports. (20 printed pages)
Stephen C. Sanderlin, MSProjectExperts
Applies to:Microsoft Office Project Server 2007, Microsoft SQL Server 2008 Reporting Services
You can download the code sample for this article from the MSProjectExperts site that describes the forthcoming book, Developer's Guide to Microsoft Project Server 2010.
The book addresses both Project Server 2007 and Microsoft Project Server 2010. This article is adapted from a chapter in that book.
Although Microsoft Office Project Server 2007 includes reporting functionality, many customers want to expand or customize their business intelligence experience beyond the default capabilities. It is not surprising that users often choose Microsoft SQL Server 2008 Reporting Services (SSRS) because of its rich feature set and ability to integrate directly with Windows SharePoint Services 3.0. Project Server 2007 uses Windows SharePoint Services 3.0 as a platform foundation.
Project Server 2007 includes a dedicated reporting database; however, the Project Server system provides no way to restrict access to the information in the reporting database, other than by using the security capabilities of SQL Server 2008. This can be a burden on administrators.
In this article, I present a scenario to demonstrate how developers can combine the programmability features of Project Server 2007 and SSRS to secure the data presented by SSRS reports.
Code for this article was developed on a server running Windows Server 2008 x64 Service Pack 2, Microsoft Office SharePoint Server 2007 x64 Service Pack 2 with the October 2009 Cumulative Update, Project Server 2007 x64 Service Pack 2 with the October 2009 Cumulative Update, Microsoft Visual Studio 2008 Service Pack 1, and SQL Server 2008 Service Pack 1 with Reporting Services and the Business Intelligence Development Studio installed.
You are a senior developer in your organization's Microsoft Enterprise Project Management (EPM) practice. This practice designs, deploys, and customizes Project Server 2007 and related products for both internal customers and external customers.
You are currently working on a Project Server 2007 deployment engagement for one of your company's largest clients, Contoso. Moments ago, you received an e-mail message from your team's technical lead informing you of a new "must-have" requirement from the customer.
The customer requested an SSRS report that displays various information about the projects that are contained within their Project Server 2007 deployment. The report must display information only to users who are members of either the Project Managers group or the Administrators group, and must display information in compliance with the user permissions in Project Server.
The @ProjectList parameter in the report calls two methods in the ProjectList class: GetAllowedProjectLabels, which retrieves the project names for use as parameter labels, and GetAllowedProjectValues, which retrieves the project UIDs for use as parameter values. Both of these methods return string arrays to the report.
The ProjectList class communicates with the Project Server Interface (PSI) to retrieve the following information about the current user:
Resource authorization information
A list of accessible regular projects
A list of accessible master projects
A list of accessible subprojects
The ProjectList class stores the name and UID of each project within the _allowedProjects private member. Both methods called by the @ProjectList report parameter extract the data from _allowedProjects and convert it into a string array for use by SSRS.
When SSRS renders the @ProjectList parameter, the ProjectList instance goes out of scope, which destroys the data stored in _allowedProjects. Because the list of projects that the current user can access is assembled by the class only once per report execution, you can be confident that the name and UID of a project will match in the report parameter.
If the current user is not a member of the Project Managers group or the Administrators group or does not have access to at least one project in Project Server, the ProjectList class returns an empty array and the SSRS report parameter does not contain any values. This behavior prevents the report from rendering and fulfills the customer's security requirements.
Although you can also return the list of allowed projects from the ProjectList class and process the data by using code embedded in the report, I prefer to consolidate all of the code into a single class library.
With the design finalized, you can start to develop the PsiProxies class library that your report uses to communicate with the PSI. You use the following procedures:
Create the class library project and set references.
Prepare the ProjectList class for further development.
Create a ProjectDerived class for impersonation.
Develop the ProjectInfo class.
Develop the ResourceInfo class.
Test whether a user is a project manager or administrator.
Filter resource data for the current user of the report.
Retrieve resource information from the PSI.
Retrieve a list of projects that the current user can access.
Create public interfaces for the ProjectList class.
Procedure 1. To create the class library project and set references
The Microsoft.Office.Project.Server.Library namespace contains enumerations and classes used by the PSI and event base methods.
Procedure 2. To prepare the ProjectList class for further development
SSRS executes any custom assemblies that it invokes in the security context of the process identity account of the SSRS instance. To retrieve a list of projects that the current user can access, you must use impersonation when communicating with the Project PSI Web service. Otherwise, the Web service simply returns a list of projects that the process identity account of the SSRS instance can access.
For more information about impersonating another user by using the PSI, see Using Impersonation in Project Server.
Although most of the sample code in this article is fully compatible with Project Server 2010, the method of impersonating the credentials of another user through the PSI has changed significantly since Project Server 2007. The impersonation functionality was under development as of the date of this article.
Procedure 3. To create a ProjectDerived class for impersonation
You must now create a simple class to store the information about each project that the current user can access. Because the project information is loaded into a report parameter, you need to store only the name and UID of the project.
Procedure 4. To develop the ProjectInfo class
Now that you have a class to store project information, you must create a similar class to store information about the current user. You use this information when communicating with the Project PSI Web service through the impersonation class that you created in Procedure 3.
Procedure 5. To develop the ResourceInfo class
Because the customer has specified that this report may present data only to users who are in either the Project Managers group or the Administrators group, you must parse the Groups property of the ResourceInfo class to ensure that the current user is a member of one of these groups.
Procedure 6. To test whether a user is a project manager or administrator
The PSI uses various UIDs to identify entities in the system (for example, resources). Because you are interested in retrieving information about only the current user, you can use the filter functionality of the ReadResource method in the Resource Web service to limit the information that you retrieve. The preferred approach to building a filter is to create a dedicated method that assembles the filter and returns the filtering XML.
Procedure 7. To filter resource data for the report's current user
After you create a method to build the filter that you want, your next task is to create a method that uses the filter to communicate with the Resource Web service to retrieve and extract the information that you want.
Procedure 8. To retrieve resource information from the PSI
Now that you have all of the information necessary for impersonation and for determining whether the current user is permitted to execute the report, your next task is to build a method that communicates with the Project PSI Web service by using impersonation, to retrieve a list of projects that the user may access.
Procedure 9. To retrieve a list of projects that the current user may access
With your internal code for the PsiProxies class library complete, you must now add public interfaces to the ProjectList class so that your SSRS report can interact with it.
Procedure 10. To create public interfaces for the ProjectList class
After you add the public interfaces for the ProjectList class, the PsiProxies class library is complete.
Your next task is to develop a report that uses the PsiProxies class library to communicate with the PSI. You use the following procedures:
Procedure 11. To create the report server project and Project Work Report
With the base report complete, your next task is to deploy the PsiProxies assembly to the Report Designer folder so that you can test the PSI interface through Visual Studio 2008.
Procedure 12. To deploy the PsiProxies assembly for use in Report Designer
Now that Visual Studio 2008 has access and permission to the PsiProxies assembly, you must add the appropriate references to the report.
Procedure 13. To add the PsiProxies assembly to the Project Work Report
Now that the report has a reference to the PsiProxies assembly, you must add the necessary invocations of the ProjectList class to the appropriate report parameter.
Procedure 14. To configure the @ProjectList report parameter
You should now have a fully functional report. Click the Preview tab to render the report.
To render the report, you must be running Visual Studio 2008 under an account that is a member of the Project Managers group or the Administrators group in Project Server and that has access to at least one project in Project Server. Otherwise, the Projects parameter is empty and the report does not render.
To deploy the ReportingPsiSecurity solution, you must first perform an XCopy deployment of the PsiProxies assembly to the report server.
Procedure 15. To deploy the PsiProxies assembly to the report server
After deploying the PsiProxies.dll assembly, your next task is to deploy the Project Work Report by using Visual Studio 2008.
The report solution requires the identity account for the target instance of SQL Server Reporting Services to have administrator access to Project Server. You must also add the identity account to the Shared Services Provider (SSP) instance of Project Server as a process account.
Procedure 16. To deploy the Project Work Report to the report server
Combining Project Server with SQL Server 2008 Reporting Services provides organizations of all sizes with powerful business intelligence capabilities. Although each product already offesrs an impressive array of programmability features, you can use the extensibility features of SQL Server 2008 Reporting Services to interface with the Project Server API and create compelling reporting solutions for your customers.