Enterprise Project Management with SharePoint
Which SharePoint technologies are right for you? In your effort to find the answer, you've probably sifted through a seemingly endless list of categories, including Collaboration and Social Computing, Portals, Enterprise Search, Enterprise Content Management, Business Processes and Forms, Business Intelligence, and Developer Platform Capabilities, and compared the features available in Windows SharePoint Services (WSS) 3.0, Microsoft Office Search Server 2008 Express, Microsoft Office Forms Server 2007, and Microsoft Office SharePoint Server (MOSS) 2007. However, there's one technology you may never have considered because Microsoft does not list it under SharePoint Products and Technologies—Enterprise Project Management (EPM), which is enabled through Microsoft Office Project Server (MOPS) 2007.
But MOPS 2007 is SharePoint technology; it builds on and extends WSS 3.0 in a way that is comparable to MOSS 2007. MOPS 2007 is the right choice if you want to increase the efficiency of team collaboration within and across departments through work, resource, and budget management beyond the lightweight task-management capabilities included in WSS 3.0 and MOSS 2007. With MOPS, you can turn team sites into project workspaces, manage team collaboration within and across departments, and establish a solid EPM foundation for an entire organization. First, however, you need to master the deployment challenges.
In this column, I discuss the deployment of MOPS 2007 in a Windows Server 2008 environment with SQL Server 2005 SP2 and WSS 3.0 SP1. First, I briefly review the MOPS architecture to show how the components integrate with WSS 3.0 from a system architect's point of view. This information makes it easier to follow the subsequent discussion of typical deployment and integration challenges you might encounter, such as application pool configuration issues, missing access permissions, Queue system startup errors, and issues related to SQL Server 2005 Analysis Services.
To illustrate the deployment and troubleshooting steps, I use a basic test environment with two WSS 3.0 servers in a server farm, a dedicated domain controller, and a separate computer running SQL Server, similar to the test environment I've been using so far for this SharePoint column. You can find corresponding worksheets with step-by-step instructions in the companion material for this column, located on the TechNet Magazine Web site.
Project Server Architecture
MOPS 2007 is one of the most advanced and complex SharePoint applications. It takes full advantage of the WSS 3.0 platform for centralized administration, site provisioning, authentication, and security. Also, it adds further components, such as 25 general and specialized MOPS Web Parts and a new collection of up to four MOPS databases per Project Web Access (PWA) site. Each is accessed through a set of 21 public and internal MOPS Web services that jointly form the Project Server Interface (PSI), as illustrated in Figure 1
. You can find more details about MOPS Web services on MSDN
Figure 1 SharePoint integration with MOPS 2007 (Click the image for a larger view)
The MOPS 2007 architecture relies on a variety of components distributed across client workstations, application servers, and database servers. I discuss the most important of these components in this column, but if you are interested in all the technical details, read the "Project Server Architecture
" documentation in the Project 2007 SDK.
Just keep in mind when reading the SDK that PWA Web Parts and Microsoft Office Project Professional 2007 do not access the PSI Web services directly. The SDK suggests that clients make direct calls to the PSI, but most applications actually use the PSI Forwarder, which is a component in PWA sites that provides indirect access to the PSI Web services. Only server-based components, such as the Queue service and the Events service, that run with system-level permissions make direct PSI calls. This little detail is important for you to remember in troubleshooting situations for several reasons, specifically:
- PWA sites define database context (every single PWA site has separate Draft, Published, Archive, and Reporting databases) and user permissions, but regular user accounts are not granted access to the PSI Web services.
- The PSI Forwarder does not support impersonation and uses the PWA site's application pool account to access the PSI Web services on behalf of the users.
- PSI calls do not necessarily use local PSI Web services if the farm includes multiple application-server instances.
Now let's take a closer look at the actual MOPS integration with SharePoint. From a SharePoint admin's perspective, MOPS is a shared Web app managed as a farm service in SharePoint 3.0 Central Administration. This is relatively straightforward for administrators familiar with Shared Service Providers (SSPs) in MOSS 2007.
But if you are a WSS 3.0 administrator and new to SSP administration, you'll want to check out the "Deploying Project Server 2007" worksheet, available in the companion material, for step-by-step instructions to create and configure a SharePoint farm's shared services and PWA sites. Following the MOPS installation and configuration, you can analyze the system implementation in IIS Manager. As illustrated in Figure 2, you should see separate Web sites for shared services, SSP administration, and site collections on a MOPS application server.
Figure 2 Project Server 2007 access through PWA and PSI Forwarder (Click the image for a larger view)
Clients access the PSI Web services through the _vti_bin/PSI virtual directory in a PWA site. However, the PSI Web services don't reside in this virtual directory. The _vti_bin/PSI virtual directory maps to the following physical path: %COMMONPROGRAMFILES %\Microsoft Shared\Web Server Extensions\12\ISAPI\PSI. You will find that this directory contains a web.config file that specifies in an <httpHandlers> section that all HTTP requests to *.asmx files (that is, ASP.NET-based Web services) should be passed to a custom HTTP handler, instantiated through Microsoft.Office.Project.Server.PSIForwarderHandlerFactory.
This custom HTTP handler is the PSI Forwarder. The PSI Forwarder establishes a new HTTP connection to the actual PSI Web services, forwards the client's HTTP request, and then returns the results to the client.
The PSI Web services are available to the PSI Forwarder via HTTP through the PSI virtual directory of the Shared Services Web application, which is hosted in the Office Server Web services site. This virtual directory maps by default to the physical path %PROGRAMFILES %\Microsoft Office Servers\12.0\WebServices\Shared\PSI, and this is where you can find the MOPS 2007 *.asmx files.
I will explain more about the Office Server Web services site later; for now, the most important fact to take away from Figure 2 is that the PSI Forwarder communicates with the PSI Web services by using the identity of the PWA site's Web application pool account instead of the user currently accessing the PWA site.
Server Farm Deployments
The PSI Forwarder also plays an important role in server farm deployments that use separate Web front-end servers and application servers to increase system scalability and availability. A MOPS front-end server is a WSS 3.0 server that does not host the PSI Web services or other MOPS services (such as Queue service and Events service) but provides clients with access to PWA sites, which include the PSI Forwarder, as illustrated in Figure 3.
Figure 3 A medium-size MOPS 2007 server farm (Click the image for a larger view)
An application server, on the other hand, is a WSS 3.0 server that has the full set of MOPS components and services installed. Application servers can host PWA sites, but more often than not they only provide back-end services to front-end servers and do not run the WSS Web Application service. You choose the server role during the MOPS installation.
Figure 3 shows the configuration for a medium-size server farm. You can add additional front-end servers to increase scalability as well as additional application servers for high availability, depending on your organization's requirements. It is not necessary to configure a load-balancing cluster for MOPS application servers because the PSI Forwarder automatically load-balances PSI requests if multiple MOPS application servers exist in the server farm. For detailed information regarding the MOPS deployment options, see the "Deployment Guide for Office Project Server 2007
Welcome to IIS 7
Enough theory! Let's tackle some real-world issues that you might encounter during the deployment of MOPS 2007. One of my favorite issues relates to host-named site collections for PWA sites. In this scenario, you successfully deploy MOPS, configure the SSP, provision a PWA site in host-header mode by entering the full PWA URL (for example, pwa) after selecting the Use Project Web Access path as host header checkbox on the Create Project Web Access Site page. Then, after all resources are provisioned successfully, you attempt to open the site, but, instead of PWA, you reach the standard IIS 7 Welcome screen.
This is what happens if the Default Web Site is not SharePoint-extended and there is no other Web site with an appropriate site binding for the PWA URL. Without an explicit site binding, IIS associates the pwa request with the non-extended Default Web Site, so you see the Welcome screen. You might have expected SharePoint 3.0 Central Administration to add the required binding to the SharePoint Web app that you selected to host the PWA site, but this does not happen.
To address this issue, you must SharePoint-extend the Default Web Site and then use this site collection to provision PWA sites, as outlined in the Troubleshooting IIS and PWA companion worksheet. Alternatively, you can add the missing site binding manually in IIS Manager to the Web application selected for PWA. Don't forget to perform this step on all WSS servers that host the PWA site.
Session Database Permissions
If you decide to extend the Default Web Site, make sure you use a domain account for the application pool—see "Plan for Administrative and Service Accounts (Office SharePoint Server)
"—otherwise, you will encounter permission-related issues after provisioning PWA sites, which usually manifest themselves in a typically uninformative SharePoint error message, such as the one displayed in Figure 4
Figure 4 Error accessing the SSP database (Click the image for a larger view)
To find more meaningful information, you should check out the trace logs, located in the %COMMONPROGRAMFILES %\Microsoft Shared\Web Server Extensions\12\LOGS directory. This can be a tedious task if your farm includes multiple load-balanced Web front-end servers.
For troubleshooting purposes, it is good to change the DNS record for the PWA host name and point it temporarily to a single front-end server. That way you know which server handles the client requests so you only need to check that one server's trace log files.
Open the most recent log file in Notepad and search for the entry "Cannot open database." If you find it, you know that you are dealing with a database permissions issue. For example, the trace log in Figure 4 shows that the account LITWARE\WSS02$ does not have access to the database SharedServices1_DB. This is a clear indicator that the PWA site is running with a Network Service identity.
LITWARE\WSS02$ is the computer account of the Web front-end server and SharedServices1_DB is the Shared Services Provider database. Among other things, the Web front-end servers use this database in order to maintain ASP.NET Session State data in the ASPStateTempSessions table, but LITWARE\WSS02$ does not have access to this database.
It's important to note that you must make sure you include the Shared Services Provider database in your weekly database maintenance activities to ensure stable system performance. For example, you should remove expired session state data from the ASPStateTempSessions table by using the SQL command TRUNCATE TABLE ASPStateTempSessions, as documented in "Deploy Project Server 2007 to a Server Farm Environment
Network Service-related configuration issues can be confusing, so I am going to take a closer look at them. Domain accounts (such as LITWARE\SspConfigAdmin) work for application pools in server farms because they are exactly the same on all computers. In contrast, the Network Service account (NT AUTHORITY\NETWORK SERVICE) is different on every single computer. On a front-end server that is named wss02.litware.com, the NT AUTHORITY\NETWORK SERVICE account translates into LITWARE\WSS02$. But on a server with the name sql01.litware.com, the NT AUTHORITY\NETWORK SERVICE account refers to LITWARE\SQL01$ instead. There lies the problem.
If you examine the SQL Server permissions for SharedServices1_DB in SQL Server Management Studio, you can see that the NT AUTHORITY\NETWORK SERVICE account has db_owner permissions in an attempt to grant application pools that use the Network Service account access to the Shared Services Provider database. But remember that this works only in a single-server installation.
You can fix the permission assignments by explicitly granting LITWARE\WSS02$ and all other WSS server accounts (perhaps LITWARE\WSS01$ and so forth) db_owner permissions for SharedServices1_DB, but it is better to use domain accounts for application pools instead so that all front-end servers use the same identity that SharePoint has granted the required database permissions.
Shared Services Permissions
If your PWA site's application pool uses the Network Service account for any reason, you will also encounter SSP-related permissions issues. Remember I mentioned that the PSI Forwarder accesses the PSI Web services on behalf of the user in the context of the PWA sites application pool account? If this account doesn't have permissions to access the Office Server Web services site, you are back at square one with the usual SharePoint error message. This time, the trace log on the front-end server states "The request failed with HTTP status 401: Unauthorized," as displayed in Figure 5.
Figure 5 The request failed with HTTP status 401: Unauthorized (Click the image for a larger view)
Keep in mind that this error does not refer to user permissions in PWA. SharePoint permissions granted in a PWA site determine which users can access that site's _vti_bin/PSI virtual subdirectory, but these users do not receive access permissions to the Shared Services Web app or the PSI Web services on the application server.
Even if you are a PWA site collection administrator, MOPS still fails if the PWA site's application pool account has no access to the PSI Web services. This is particularly the case if you ignore the recommendation to use domain accounts for application pools in a server farm and use the Network Service account instead.
To verify SSP access permissions on the application server, check out the web.config file in the root directory of the Office Server Web services site (by default, %PROGRAMFILES%\Microsoft Office Servers\12.0\WebServices\Root). You might notice the NT AUTHORITY\NETWORK SERVICE entry in the <authorization> section, which is supposed to authorize app pools that use the Network Service account. But again, it doesn't accomplish the task because the entry only refers to the local computer, which is not the front-end server.
The best strategy is to change the application pool configuration and use a domain account. But if you insist on using the Network Service account, you must authorize the front-end server accounts explicitly.
Don't edit the web.config file on the application server directly—SharePoint will overwrite your changes. Instead, use SharePoint 3.0 Central Administration to grant the missing permissions, as outlined in the "Testing Shared Service Provider Access Permissions" worksheet. Also, verify the configuration by using a simple ASP.NET app that establishes a direct HTTP connection to the PSI Web services, such as SSPCheck (which is included in the companion material). Just make sure you run the ASP.NET test application under the PWA site's application pool to obtain reliable results.
At this point, chances are good that you can open PWA—unless, of course, you are attempting to access PWA through a Web front-end server and the Windows Firewall on the MOPS application server blocks TCP ports 56737 and 56738. These are the default ports assigned to the Office Server Web services site for HTTP and HTTPS communication.
SharePoint doesn't open these ports automatically on the MOPS application server when creating the Office Server Web services site. If you have Windows Firewall enabled on the application server, you must manually create a firewall rule in order to allow traffic to these ports so that front-end servers can access the PSI Web services. If a firewall blocks these ports, you receive the error message displayed in Figure 6 and the trace log on the front-end server states "connected host has failed to respond."
Figure 6 Project Web Access cannot connect to Project Server (Click the image for a larger view)
MOPS Services and Service Accounts
Having solved the front-end/back-end communication issues, you should be able to access Project Web Access. Congratulations! Now it's time to focus on a more difficult MOPS-specific issue. Take a deep breath and then open the application event log on your MOPS application server, and don't be shocked if you see thousands and thousands of error messages stating that the "Queue system could not start," as shown in Figure 7. You might also notice that MOPS services cause a nearly 100% CPU utilization.
Figure 7 The Queue system could not start (Click the image for a larger view)
The Queue system is the backbone of the MOPS application infrastructure for MOPS database insert and update request handling, running clean-up and maintenance jobs and for updating the Reporting database for use in data analysis tasks. This is explained in great detail in the article "Microsoft Office Project Server 2007 Queuing System
." According to this article, the Queue system relies on a Windows service, implemented in the assembly Microsoft.Office.Project.Server.Queuing.exe and running under the identity of the SharePoint configuration administration and timer service account to access the farm's configuration database.
On startup, the Windows service retrieves the list of all SSPs from the configuration database, including the corresponding SSP administrator accounts and their encrypted passwords, and then starts an additional Microsoft.Office.Project.Server.Queuing.exe process for each SSP associated with PWA sites in the context of the corresponding SSP administrator account. In other words, Microsoft.Office.Project.Server.Queuing.exe starts multiple instances of itself under different accounts, so the total number of Microsoft.Office.Project.Server.Queuing.exe processes running on a MOPS application server is equal to the number of SSPs plus one.
The additional process instances are the queue worker processes. Each individual queue worker process determines its own set of PWA sites from its associated SSP, starts separate polling threads for each of the PWA sites, and begins processing the jobs queued for the corresponding PWA site databases. That's how the Queue system works, and you can verify this in Windows Task Manager.
On a MOPS app server in a farm with one SSP associated with PWA sites, you can see two Microsoft.Office.Project.Server.Queuing.exe processes—one running as the configuration administration account and the other running as the SSP administrator account. In my test environment, these accounts are WssConfigAdmin and SspConfigAdmin, as shown in Figure 8.
Figure 8 Inter-process communication fails because access is denied (Click the image for a larger view)
So why doesn't the Queue system start? The error entry in the application event log doesn't provide sufficient information, but you can obtain more details if you temporarily set the Least Critical Event to Report to the Trace Log for all categories to Verbose in SharePoint 3.0 Central Administration on the Operations tab under Diagnostics Logging.
Figure 8 shows a resulting trace log, and if you take a closer look, you can see that the ProjectQueueService (the overall Window service) starts a QueueExecService (a queue worker process), but the QueueExecService process fails because access is denied. As QueueExecService fails, ProjectQueueService attempts to restart it, but it again fails for the very same reason, and so it continues consuming CPU cycles and filling the event and tracing logs with thousands of errors. It's an infinite loop.
Unfortunately, even the most detailed trace log does not reveal the specific cause of the Access is denied error. But don't despair—through the process of elimination you can quickly get to the root cause.
If you change the SSP administrator account in SharePoint 3.0 Central Administration and specify the configuration administration account (WssConfigAdmin), the Queue system starts. It also works the other way around; if you simply leave the SSP administrator account unchanged (SspConfigAdmin) and use it as the service account for the Windows service, the Queue system starts as well.
It follows then that both the configuration administration account and the SSP administrator account have all required permissions; it's just that the Queue system doesn't start if ProjectQueueService and QueueExecService use different accounts.
This is a clear indicator of a permissions issue between separate processes that want to interact with each other on the local computer. After all, the ProjectQueueService and QueueExecService processes must monitor each other to ensure a consistent service behavior (notice the ProcessWatcher entries in the trace log in Figure 8). For example, when you shut down the ProjectQueueService Windows service, any QueueExecService worker processes must shut down as well.
It is the attempt to access a process running in a different security context that results in the error. Accessing a process in another security context requires elevated privileges. Even though the service accounts might have these privileges, Windows Server 2008 still denies access because User Account Control (UAC) is enabled by default, which causes processes to run with standard privileges.
As soon as you disable UAC, ProjectQueueService and QueueExecService processes can run with elevated privileges and the Queue system starts. The choice is yours between using the configuration administration account as the administrator account for all SSPs, thus running all queue processes with the same account, or weakening security on your MOPS application servers by disabling UAC.
Analysis Services Integration
Let's conclude this column with a SQL Server 2005 Analysis Services issue that you are likely to encounter if you follow the instructions outlined in "Requirements for using SQL Server 2005 Analysis Services with the Project Server 2007 Cube Building Service
" (dated 2007-04-05 at the time of this writing). If you follow the path to create the Analysis Services repository by creating a SQL Server 2005 database as documented, you will end up with the error displayed in Figure 9
when trying to build the cube in PWA.
Figure 9 Cube building error due to incorrect Analysis Services configuration (Click the image for a larger view)
The important point is that MOPS 2007 is designed for SQL Server 2000 Analysis Services. SQL Server 2005 Analysis Services require additional configuration steps for backward compatibility. The SQL Server 2000 version stores repository information about the cube build in a Microsoft Jet database, and although it is possible to migrate a Jet database to work with SQL Server 2005, it is not necessary in a fresh MOPS deployment.
The TechNet article to which I just referred explains how to configure SQL Server 2005 to emulate the functionality of the Jet database regardless of whether or not the Jet database is actually present. Yet, the article fails to mention that MOPS still checks repository locking information in the .dso file share on the database server regardless of whether Analysis Services is configured to use a Jet database (the old method) or a preconfigured SQL Server 2005 database (which is the preferred method).
Unless this file share exists and the SSP administrator account is given Full Control access to this file share, the cube build fails with the permissions error displayed in Figure 9. For SQL Server 2005 Analysis Services to function correctly with the MOPS Cube Building Service, follow the steps outlined in the "Configuring Cubes" companion worksheet.
MOPS 2007 is not easy to deploy. The architecture is complex, and a successful deployment involves numerous steps, ranging from proper planning of server farm configurations, installing binary files and running the SharePoint Products and Technologies Configuration Wizard on application servers and Web front-end servers, through provisioning application pools, shared services, SSP administration sites, and PWA sites in SharePoint 3.0 Central Administration, to finally configuring Analysis Services in SQL Server Management Studio.
Contributing to the deployment challenges are interfering security features of Windows Server 2008, such as Windows Firewall and UAC, weaknesses in the SharePoint admin tools, and omissions in the MOPS deployment documentation. You cannot rely on SharePoint 3.0 Central Administration to warn you if your application pools use the Network Service account in a server farm, apply all required configuration changes automatically (such as IIS site bindings and Windows firewall rules), or check the operational state of provisioned PWA sites.
Also, don't expect everything to work out of the box. Make sure you fully understand the MOPS architecture and dependencies, do not deviate from product recommendations, and thoroughly validate the MOPS configuration and functionality by creating test project plans and resources to ensure deployment success.
Despite these challenges, MOPS also inherits the strengths of SharePoint as an enterprise platform. By relying on SharePoint and Web services technologies, MOPS eliminates the need for direct database connectivity on client workstations. Through the Queue system, MOPS increases sustainability during peak hours (for some unexplainable reason, all program managers want to update their project plans on Monday morning), and through additional MOSS technologies, it is possible to integrate MOPS with further line-of-business applications.
Having developed business solutions for Project Server 2003 in the past, I find the MOPS 2007 deployment challenges minuscule in comparison to the past scalability issues, the old ODBC connectivity issues over slow network connections, and the pain of building database queries that involved so many JOIN statements that I had to use Excel to keep track of all the sub queries. MOPS 2007 is a significant milestone in the EPM evolution, and it is worth the deployment effort.
Pav Cherny is an IT expert and author specializing in Microsoft technologies for collaboration and unified communication. His publications include white papers, product manuals, and books with a focus on IT operations and system administration. Pav is President of Biblioso Corporation, a company that specializes in managed documentation and localization services.