The Project Data Service and Microsoft Project Server Security Architecture

The Project Data Service and Microsoft Project Server Security Architecture

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Summary

This article provides developers with the background and details of the Microsoft® Project Server security architecture, and explains the role of the Project Data Service (PDS) in accessing project data.

Introduction

This article provides developers with the background and details of the Microsoft Project Server security architecture and, more specifically, explains the role of the Project Data Service (PDS) in accessing project data. Any client solution that needs to access data from the Microsoft Project Server database views should rely on this article as the basis for maintaining data security.

This article describes how a client application queries the PDS for connection information to the Microsoft Project Server database. Using this information, a client application connects to the Microsoft Project Server database and queries views to retrieve and update information for the projects and resources that the application has access to.

The Project Data Service

The PDS is a new component of Microsoft Project 2002. Clients such as Microsoft Project Professional, Microsoft Project Web Access, or a client written by a solution provider developer all access the PDS to obtain portfolio data. Requests to the PDS are made through an Extensible Markup Language (XML) interface and the responses to the client are likewise in XML.

In Figure 1, you can see the relationship between Microsoft Project clients and the Microsoft Project Server. The PDS acts as the gateway for accessing the Microsoft Project Server. It is a middle-tier component hosted within Microsoft Internet Information Services. The PDS receives XML requests, maintains a connection on behalf of the client to the Microsoft Project database, queries the database, and then returns an XML response to the client.

Aa164673.PDR_pds01(en-us,office.10).gif

Figure 1. The Project Data Service acts as a gateway between Microsoft Project clients and the Microsoft Project Server database

The PDS ensures that Microsoft Project data stored in the database remains consistent. The PDS opens a connection to the Microsoft Project database on behalf of the client. The PDS authenticates the client and provides access to the Microsoft Project data that particular client is authorized to view. The client sees only data relevant to its project.

However, you can query the Microsoft Project Server database directly through views, which gives you access to rich project and resource information. The PDS provides methods to obtain the database connection information needed to connect to the Microsoft Project Server database. You then can use views provided by the Microsoft Project Server database to run queries, for example, to gather data for a custom report. These views ensure that clients only see data that they have permission to see. Clients never access the actual database tables themselves.

Accessing Microsoft Project Data

Client applications that need to access Microsoft Project Server data will need to operate within the Microsoft Project Server security constraints.

To access Microsoft Project data:

  1. Log on to the Microsoft Project Server.
  1. Call the PDS to request the database connection information.
  1. Make a connection to the Microsoft Project Server database using that information and gather the SQL Process ID (SPID) for that connection.
  1. Call the PDS to request access to the project or resource that the client needs to access and pass along the SPID from the previous step.
  1. Make queries against the appropriate set of Microsoft SQL Server™ views to gather and/or update the required information.
  1. Call the PDS when the access is no longer required to maintain the security of the data.
  1. Disconnect from the Microsoft Project Server database.

Aa164673.PDR_pds02(en-us,office.10).gif

Figure 2. How a client accesses the Microsoft Project Server database views

Requesting Database Connection Information

A client can query the PDS to obtain the information needed to create a connection to the Microsoft Project Server database.

Once logged on to the Microsoft Project Server, the client calls the GetLoginInformation method by sending the following XML using the Simple Object Access Protocol (SOAP) protocol, which is passed to the PDS object through the SoapXMLRequest interface:

  <Request>
       <GetLoginInformation/>
  </Request>

The response from GetLoginInformation is similar to the following XML:

  <Reply>
       <HRESULT>0</HRESULT>
       <STATUS>0</STATUS>
       <UserName>Administrator</UserName>
       <GetLoginInformation>
            <DBType>0</DBType>
            <DVR>{SQL Server}</DVR>
            <DB>ProjectServer</DB>
            <SVR>MYPROJECTS</SVR>
            <ResGlobalID>1</ResGlobalID>
            <ResGlobalName>resglobal</ResGlobalName>
            <UserName>MSProjectUser</UserName>
            <Password>password</Password>
       </GetLoginInformation>
  </Reply>

The elements in the GetLoginInformation node contain the information needed to make a database connection using, for example, Microsoft ActiveX® Data Objects (ADO). Once you've connected to the Microsoft Project Server database, you can then query the views to obtain more detailed information about your projects.

Notice that the password is sent in clear text. Because of this, it is recommended that your Microsoft Project Server administrator require Secure Sockets Layer (SSL) when connecting to the Microsoft Project Server. Although your connection to the server will be slightly slower, your database connection information will be encrypted. This is especially important when your Microsoft Project Server can be accessed over the Internet.

PDS Management of View Access

The PDS manages access to the Microsoft Project Server database views by tracking connections based on the SPID of the client connection. After you've connected to the Microsoft Project Server database, you can gather your connection's SPID by executing the following query:

  SELECT @@SPID

The Microsoft Project Server database has two tables for managing access to views: the MSP_PROJ_SECURITY table for project views and the MSP_RES_SECURITY table for global resource views. For each table, access is tracked separately for read and read/write views for a given SPID.

The PDS provides methods for requesting access to views: ProjectsAccess for project views and ResourcesAccess for resource views. (See "Client Access to Views" for details on calling these methods.) You call the access method, specifying the SPID, the timestamp of the SPID creation, and the access mode (read or read/write).

For example, if you want to access project views for a report, you must first call the ProjectsAccess method of the PDS with the appropriate parameters. If the passed-in parameters are a ProjectID of 1, an SPID of 51, an SPIDTimestamp value of 20011017105500, and a mode of 0 (read-only), the following row would be created in the MSP_PROJ_SECURITY table:

  PROJ_ID SEC_SPID SEC_SPIDDATESTAMP        SEC_READCOUNT SEC_WRITECOUNT
  ------- -------- -----------------------  ------------- --------------
  1       51       2001-10-17 10:55:00.000  1             0

The read-only project views check the value of SEC_READCOUNT for the row corresponding to the connection SPID that is attempting to access the views. In this case, the value of SEC_READCOUNT is 1, which allows the client to retrieve data from the view. However, if the client attempts to access a read/write view, the view contains no data because the value of SEC_WRITECOUNT is 0. The client first has to call ProjectsAccess again, this time specifying a mode of 1 (read/write). This example illustrates the role of PDS in view security. A client cannot retrieve data from the Microsoft Project Server database views without first logging on to the Microsoft Project Server and requesting access via the PDS to a view. This additional security check ensures that a Microsoft Project client only sees portfolio data accessible to the specified Microsoft Project user logon ID.

Client Access to Views

Once a client has a database connection, the client then logs on to Microsoft Project Server and requests permission to access project views through the ProjectsAccess method of the PDS. In this example, the database connection SPID is 58, the SPID timestamp is 2001-10-17 10:55:00 (notice that the value passed in corresponds to the date numerals without formatting), and the project ID is 3. This call to ProjectsAccess is made using the following XML:

  <Request>
       <ProjectsAccess>
            <Mode>0</Mode>
            <SPID>58</SPID>
            <SPIDTimestamp>20011017105500</SPIDTimestamp>
            <Project>
                 <ProjectID>3</ProjectID>
            </Project>
       </ProjectsAccess>
  </Request>

Note that the actual value of the SPID creation timestamp is up to the client and does not have to be the actual creation time of the connection SPID. Any unique timestamp will do, for example, the current time.

A successful reply from the ProjectsAccess method is similar to the following XML:

  <Reply>
       <HRESULT>0</HRESULT>
       <STATUS>0</STATUS>
       <UserName>Administrator</UserName>
       <ProjectsAccess>
            <Mode>0</Mode>
            <ResGlobalID>1</ResGlobalID>
            <ResGlobalName>resglobal</ResGlobalName>
       </ProjectsAccess>
  </Reply>

To access enterprise resource views, a client calls ResourcesAccess, which is similar to ProjectsAccess except that it also allows a particular resource ID to be specified.

Additionally, the SQL Server user account specified in the database connection information must have permission to access the views. Typically, this user is MSProjectUser (although the name may vary for a particular installation), which is the default SQL Server user account created by the Microsoft Project Server setup program. The MSProjectUser user belongs to the MSProjectRole role, which is given permission to use the database views.

If you examine the Microsoft Project Server database tables in SQL Server Enterprise Manager, you will notice that users in MSProjectRole do not have access to any database tables. You should never change these setting by granting users direct access to the tables. Doing so circumvents the PDS security and opens the possibility that inconsistent data will be written to the database.

Ideally, client-side applications should only perform reads on the Microsoft Project Server database. If you need to update data, consider using a server-side PDS extension. If you absolutely must update Microsoft Project Server database tables directly, refer to the "Microsoft Project 2002 Database Information" topic in the Projdb.htm file of your Microsoft Project Server installation. In particular, the "Ensuring Data Integrity" section contains guidelines to follow that prevent the database from becoming corrupted when the tables are being updated.

Microsoft Project Views

The Microsoft Project Server database defines many views for retrieving information about projects, resources, tasks, assignments, and more. For example, the MSP_TASKS_PROJ_READVIEW is defined as follows:

  CREATE VIEW dbo.MSP_TASKS_PROJ_READVIEW
  AS
       SELECT *
       FROM MSP_TASKS
       WHERE PROJ_ID IN 
       (SELECT PROJ_ID FROM MSP_PROJ_SECURITY
            WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)
       WITH CHECK OPTION

There is also a corresponding read/write view for project tasks named MSP_TASKS_PROJ_WRITEVIEW. Additionally, similar views exist for resources (MSP_TASKS_RES_READVIEW and MSP_TASKS_RES_WRITEVIEW). If you look at the column definitions, these resource task views appear to be identical to the project task views; however, these views apply to enterprise resources, and are not specific to a given project. This distinction can be seen in the definition of MSP_TASKS_RES_READVIEW:

  CREATE VIEW dbo.MSP_TASKS_RES_READVIEW
  AS
       SELECT *
       FROM MSP_TASKS
       WHERE PROJ_ID IN
             (SELECT PROJ_ID FROM MSP_PROJECTS WHERE PROJ_TYPE = 3)
       WITH CHECK OPTION

The PROJ_TYPE value of 3 corresponds to the enterprise resources (resource global).

These views allow a client to create sophisticated queries. For example, the following query provides task information for a given project and displays the resources assigned to those tasks:

  select p.TASK_ID 'Task Number', 
            p.TASK_NAME 'Task Name',
            CONVERT(VARCHAR(4), p.TASK_DUR_VAR/(60*60)) + 'd' 'Duration',
            r.RES_NAME 'Resource Name'
       from MSP_TASKS_PROJ_READVIEW p
       inner join MSP_ASSIGNMENTS_PROJ_READVIEW a
            on (p.TASK_UID = a.TASK_UID)
       inner join MSP_RESOURCES_PROJ_READVIEW r
            on (a.RES_UID = r.RES_UID)
       order by TASK_OUTLINE_NUM

This query produces output similar to the following:

  Task Number Task Name     Duration Resource Name
  ----------- ------------- -------- -------------
  1           Write outline 1d       Writer
  2           Write draft   2d       Writer
  3           Create art    1d       Artist

Remember, that you first must request read access to the project views by calling ProjectsAccess for the particular connection SPID. Otherwise, the query does not return any data because of the following check found in project views:

  WHERE PROJ_ID IN
  (SELECT PROJ_ID FROM MSP_PROJ_SECURITY
       WHERE @@SPID = SEC_SPID AND SEC_READCOUNT > 0)

Disconnecting from Views

Along with disconnecting from the Microsoft Project Server database, you must also notify the PDS that you no longer need to access the views. For project views, you do this by calling the ProjectsAccessCompleted method, specifying the access mode, connection SPID, and project ID.

  <Request>
       <ProjectsAccessCompleted>
            <Mode>0</Mode>
            <SPID>58</SPID>
            <Project>
                 <ProjectID>3</ProjectID>
            </Project>
       </ProjectsAccessCompleted>
  </Request>

ProjectsAccessCompleted removes the corresponding row in the MSP_PROJ_SECURITY table. The PDS then returns a reply similar to the following:

  <Reply>
       <HRESULT>0</HRESULT>
       <STATUS>0</STATUS>
       <UserName>Administrator</UserName>
  </Reply>

After ProjectsAccessCompleted is successfully called, you can no longer access view data for that particular database connection.