Using Project Server Security in SQL Server Reporting Services Reports
25 minutes to read
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)
The book addresses both Project Server 2007 and Microsoft Project Server 2010. This article is adapted from a chapter in that book.
Overview of Reporting Capabilities in Project Server 2007
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.
ReportingPsiSecurity High-Level Design
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.
Figure 1. High-level logic overview of ReportingPSISecurity
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.
Developing the PsiProxies 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
Open Visual Studio 2008.
Create a Class Library project named PsiProxies. Name the solution ReportingPsiSecurity, and select the Create directory for solution check box. Click OK.
In Solution Explorer, right-click the PsiProxies project, and then click Properties. In the PsiProxies dialog box, on the Signing tab, select the Sign the assembly check box, and then create a new keyfile. Close the PsiProxies dialog box.
In Solution Explorer, rename Class1.cs to be ProjectList.cs.
In Solution Explorer, right-click the PsiProxies project, and then click Add Reference. In the Add Reference dialog box, add a reference to the Microsoft.Office.Project.Server.Library.dll assembly. Following is the default location of the assembly: %ProgramFiles%\Microsoft Office Servers\12.0\Bin\
If you are developing on a 64-bit server, you should manually browse to [Drive]\Program Files instead of using the %ProgramFiles% environment variable. Because Visual Studio 2008 is a 32-bit application running under the WOW64 x86 emulator, %ProgramFiles% resolves to [Drive]\Program Files (x86) in a 64-bit environment.
In the Add Reference dialog box, on the .NET tab, add a reference to System.Web.Services.dll.
In the Add Web Reference dialog box, add a Web reference to the Resource Web service and name it ResourceSvc. Following is the default location of the Resource Web service: http://servername/instancename/_vti_bin/psi/resource.asmx
To access the Add Web Reference dialog box in Visual Studio 2008, right-click the PsiProxies project in Solution Explorer, and then click Add Service Reference. In the Add Service Reference dialog box, click Advanced. In the Service Reference Settings dialog box, click Add Web Reference.
Add a Web Reference to the Project PSI Web service and name it ProjectSvc. Following is the default location of the Project Web service: http://servername/instancename/_vti_bin/psi/project.asmx
Open AssemblyInfo.cs, and then add the following assembly attribute:
You must change the preceding constants to match your environment. To obtain the Project Web Access site ID, see Procedure 4.1 in How to: Write a Simple Impersonation Application. To obtain the LCID, see Locale ID (LCID) Chart. To obtain the group UID for a Project Server security group, open the Server Settings page in Project Web Access, click Manage Groups, and then click the group that you want. The group UID is the GUID of the groupUid option in the URL. For example, the following URL has a group UID of 69fc9d0d-0b5f-4f4a-a9ef-c05a60eb5236: http://servername/instancename/layouts/PWA/Admin/AddModifyGroup.aspx?groupUid=69fc9d0d-0b5f-4f4a-a9ef-c05a60eb5236
I simplified parts of this article's sample code. If you intend to deploy this sample code in production, consider retrieving the Project Web Access site ID, group UIDs, and URLs dynamically at run time instead of storing them as global constants in the ProjectList class.
Add the following private class field to the ProjectList class.
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.
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
In Solution Explorer, add a class named ProjectDerived.cs to the PsiProxies project.
Replace the contents of ProjectDerived.cs with the following code.
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
In Solution Explorer, add a class named ProjectInfo.cs to the PsiProxies project.
Remove the following lines from the global references section of ProjectInfo.cs.
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
In Solution Explorer, add a class named ResourceInfo.cs to the PsiProxies project.
Remove the following lines from the global references section of ProjectInfo.cs.
Add five automatic properties to the ResourceInfo class: the first stores a Boolean value that indicates whether the user has a Windows authentication account, the second stores the user UID, the third stores the UIDs of the user groups, the fourth stores the user account name (for example, DOMAIN\User), and the fifth stores the user display name.
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
In ProjectList.cs, create a method named IsProjectManagerOrAdmin.
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
In ProjectList.cs, create a method named BuildResourceFilter.
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
In ProjectList.cs, create a method named GetResourceInfo.
Develop the GetResourceInfo method by using the Resource Web service. The method retrieves the user account, display name, UID, whether the user is a Windows authentication user, and the user security groups. It then populates a ResourceInfo object with this information and returns it to the calling method.
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
In ProjectList.cs, create a method named GetAllowedProjects.
Develop the GetAllowedProjects method by using impersonation against the Project PSI Web service. This method retrieves a list of projects that the current user may access and extracts the name and UID of each project into a ProjectInfo object. This object is then added to a class field named _allowedProjects.
After you add the public interfaces for the ProjectList class, the PsiProxies class library is complete.
Developing the Project Report
Your next task is to develop a report that uses the PsiProxies class library to communicate with the PSI. You use the following procedures:
Create the report server project and Project Work Report.
Deploy the PsiProxies assembly for use in Report Designer.
Add the PsiProxies assembly to the Project Work Report.
Configure the @ProjectList report parameter.
Deploy the PsiProxies assembly to the report server.
Deploy the Project Work Report to the report server.
Procedure 11. To create the report server project and Project Work Report
In Solution Explorer, right-click the ReportingPsiSecurity solution. Click Add, and then click New Project. Create a.NET Framework 3.5 Report Server project and name it ReportingPsiSecurity.
In Solution Explorer, right-click the Shared Data Sources folder, and then click Add New Data Source.
In the Shared Data Source Properties dialog box, name the data source instancenameRdb (for example, LitwareRdb). Set the Type to Microsoft SQL Server, and then enter a connection string for the Project Server instance Reporting database in the Connection string text box. For example, to connect to a database named Litware_ProjectServer_Reporting on a computer running SQL Server EPMDEMO, type Data Source=epmdemo;Initial Catalog=Litware_ProjectServer_Reporting
In Solution Explorer, right-click the Reports folder, and then click Add New Report. On the Select the Data Source page of the Report Wizard dialog box, select the shared data source that you created in step 3.
On the Design the Query page of the Report Wizard dialog box, enter the following query in the Query string text box, and then click Next.
MERUV.ResourceName AS ProjectOwnerName,
ON MEPUV.ProjectOwnerResourceUid = MERUV.ResourceUid
MEPUV.ProjectUID IN (@ProjectList)
On the Select the Report Type page of the Report Wizard dialog box, select Tabular, and then click Next.
On the Design the Table page of the Report Wizard dialog box, select ProjectName, ProjectOwnerName, ProjectWork, and ProjectActualWork from the Available fields list, click Details, and then click Finish.
On the Completing the Wizard page of the Report Wizard dialog box, name the report Project Work Report, and then click Finish.
Format the report as you want. At a minimum, I suggest that you set the Format property for the ProjectWork detail cell and the ProjectActualWork detail cell to n2.
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
Save all changes and close Visual Studio 2008.
Copy PsiProxies.dll and Microsoft.Office.Project.Server.Library.dll from the build location of the PsiProxies project to the Report Designer folder. Following is the default location of this folder for Visual Studio 2008: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
You must change the Url attributes to match the correct path for your environment.
Save and close RSPreviewPolicy.config.
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
Open the ReportingPsiSecurity solution in Visual Studio 2008.
In Solution Explorer, open Project Work Report.rdl.
On the Report menu, click Report Properties.
On the References tab of the Report Properties dialog box, under Add or remove assemblies, click Add . Click the ellipsis button next to the new row that appears.
Browse to the Report Designer folder and select the PsiProxies.dll assembly. Following is the default location of this folder for Visual Studio 2008: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.
Under Add or remove classes, click Add. Type PsiProxies.ProjectList as the Class Name and projList as the Instance Name, and then click OK.
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.
Deploying the Solution to the Report Server
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
Copy PsiProxies.dll from the build location of the PsiProxies project to the report server bin folder. Following is the default location of the report server bin folder: %ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin.
You must change the PublicKeyBlob attribute in the previous XML to match the public key of your PsiProxies.dll assembly. Do not use the public key token. The entire public key is required. The PublicKeyBlob attribute must appear completely on a single line. You can extract the public key of an assembly by using the sn.exe -Tp command. For more information, see Strong Name Tool (Sn.exe).
If you installed SSRS on a server where SharePoint or Project Server are already installed, the rssrvpolicy.config file should already contain a CodeGroup element named SharePoint_Server_Strong_Name. If not, or if the CodeGroup is missing, copy the Microsoft.Office.Project.Server.Library.dll assembly to the global assembly cache and add the following entry to rssrvpolicy.config directly above the CodeGroup that you added in step 3.
You must remove the carriage returns from the Description attribute and the PublicKeyBlob attribute. The Description and PublicKeyBlob attributes must appear completely on a single line.
Save and close the rssrvpolicy.config file.
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
In Solution Explorer, right-click the ReportingPsiSecurity project, and then click Properties.
On the General tab of the ReportingPsiSecurity Property Pages dialog box, type the URL of the report server in the TargetServerUrl property field, and then click OK. For example, type http://epmdemo/reportserver.
In Solution Explorer, right-click the ReportingPsiSecurity project, and then click Deploy.
The report should now be available from your target 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.
For more information, see the following resources: