Monday, December 6, 2010

Building a single server Project Server 2010 with SQL Server Part 6

Configure SQL Server

In order for the report author to be able to access the Project Server 2010 Reporting database from Microsoft Excel, you must configure Microsoft SQL Server access and add a SQL Server login. The login must enable specific access to the Project Server 2010 Reporting database to gain access to schema information and data. Use the domain group that you created for report viewers (SDRED\ProjReportAuthors).

To add a login for a report author

  1. Log on to Dev-SQL using the sdred\SQLAdmin account. 01-project_server_2010_sql_login
  2. Click Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio
  3. On the Connect to Server dialog box, type localhost in the Server name box, and then click Connect.
  4. Expand Security, right-click Logins, and then click New Login. 02-project_server_2010_new_login
  5. On the General page, click Search.
  6. Click Object Types, and select the Groups check box.
  7. Click OK.                                                                        03-project_server_2010_projrepauthors               
  8. Type SDRED\ProjReportAuthor
  9. Click Check Names.
  10. Click OK.
  11. Select the User Mapping page.
  12. In the Users mapped to this login list, select the row that contains the Project Server 2010 Reporting database (ProjectServer_Reporting).
  13. Select the Map check box for the Project Server 2010 Reporting database.
  14. Select the db_datareader database role membership check box. 04-project_server_2010_user_mapping


  15. Click OK.

Configure Excel Services

Project Server 2010 requires Excel Services in Microsoft SharePoint Server 2010. The first step to configuring Excel Services is to turn on the Excel Calculation Services service.


To turn on the Excel Calculation Services service
  1. On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.
  2. In the Service list, click Start next to Excel Calculation Services.
Once the Excel Services service is running, the next step is to create an Excel Services service application.

To create an Excel Services service application

  1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
  2. On the Service Applications tab, click New, and then click Excel Services.
  3. In the Name box, type Excel Services Service App.
  4. In the Application pool name box, type ExcelServicesAppPool.
  5. Choose SDRED\SVCApp from the Configurable list.
  6. Click OK.
Once Excel Services has been configured, you must configure trusted file locations for the Project Server 2010 Sample Reports and Templates libraries.

Follow this procedure two times: one time for each library.

To configure a trusted file location

  1. In Central Administration, in the Application Management section, click Manage service applications. 05-project_server_2010_system_settings
  2. Click the Excel Services service application. 06-project_server_2010_excel_service_applications
  3. On the Manage Excel Services page, click Trusted File Locations.                                                                                   
  4. Click Add Trusted File Location.        10-project_server_2010_trusted_file_locations
  5. In the Address box, type:

    For the Templates library:

    http://dev2010/pwa/ProjectBICenter/Templates/

    or

    For the Sample Reports library:

    http://dev2010/pwa/ProjectBICenter/Sample%20Reports/ 11-project_server_2010_trusted_file_location
  6. Under Trust Children, confirm that the Children trusted check box is selected.
  7. In the External Data section:

    1. Under Allow External Data, select the Trusted data connection libraries and embedded option.
    2. Under Warn on Refresh, clear the Refresh warning enabled check box.           
12-project_server_2010_trusted_location_configuration


Click OK.

You must configure trusted data connection libraries in order to give users access to the connectors that link the report spreadsheets to the data in the Project Server 2010 Reporting database and OLAP databases. Perform the following procedure to set up the trusted data connection libraries.


To set up trusted data connection libraries
  1. In Central Administration, in the Application Management section, click Manage Service Applications.
  2. Click the Excel Services service application.
  3. Click Trusted Data Connection Libraries.
  4. Click Add Trusted Data Connection Library14-project_server_2010_trusted_data_connection


  5. In the Address box, type:

    http://dev2010/pwa/ProjectBICenter/Data%20Connections%20for%20PerformancePoint/English%20(United%20States)                           15-project_server_2010_data_connection_library
  6. Click OK.

Configure Secure Store

In SharePoint Server 2010, the Secure Store Service enables users to access multiple system resources without having to provide authentication credentials multiple times. SharePoint Server 2010 implements Secure Store Service authentication by including a Windows service and a secure credentials database.

The next step is to configure Secure Store by turning on the Secure Store Service and creating a Secure Store Service service application. Perform the following procedure to turn on the Secure Store Service.

To turn on the Secure Store Service

  1. On the SharePoint Central Administration Web site, in the System Settings section, click Manage services on server.
  2. In the Service list, click Start next to Secure Store Service.    17-project_server_2010_secure_store_service

Once the Secure Store service is running, you must create a Secure Store Service Application. Use the following procedure.

To create a Secure Store Service service application

  1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
  2. On the Service Applications tab, click New, and then click Secure Store Service. 19-project_server_2010_secure_store_service
  3. In the Name box, type Secure Store Service Application.   20-project_server_2010_new_secure_store_app
  4. In the Application pool name box, type SecureStoreAppPool.
  5. Choose a managed account from the Configurable list.
  6. Click OK.
  7. When the service application has been successfully created, click OK.

When the Secure Store Service Application has been created, you must generate a Secure Store Service key before the Secure Store service can be used. Perform the following procedure to generate a key.


To generate a Secure Store Service key
  1. On the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.
  2. Click the Secure Store service application.
  3. On the Edit tab, click Generate New Key. 24-project_server_2010_generate_new_key
  4. Type and confirm a Pass Phrase, and then click OK. 25-project_server_2010_new_key

Once Secure Store is configured, you must create a Secure Store target application.
  26-project_server_2010_new_key_generated
To create a Secure Store target application
  1. On the SharePoint Central Administration Home page, in the Application Management section, click Manage Services Applications.
  2. Click the Secure Store Service.
  3. On the Secure Store Service page, select the Edit tab.
  4. Click New.
  5. On the Create New Secure Store Target Application page:

    1. In the Target Application ID box, type ProjectServerApplication. (Value is case sensitive)

    2. In the Display Name box, type ProjectServerApplication.

    3. In the Contact Email box, type an e-mail address.

    4. From the Target Application Type drop-down list, select Group. 30-project_server_2010_target_application_id

    5. Click Next.

  6. On the Specify the credential fields for your Secure Store Target Application page, click Next.31-project_server_2010_new_secure_store_target_app                                                        

  7. On the Specify the membership settings page:

    1. In the Target Application Administrators box, type SDRED\Developer. 32-project_server_2010_target_app_admins

    2. In the Members box, type SDRED\ProjReportViewers.

    3. Click OK.

  8. On the Secure Store Service Application page, select the check box for the target application that you just created.
  9. On the ribbon, click Set Credentials. 33-project_server_2010_set_credentials
  10. On the Set Credentials for Secure Store Target Application (Group) dialog box, type the user name and password of the SDRED\ProjDataAccess account.  This account must have db_datareader permissions on the Project Server Reporting database. This can be achieved by adding the SDRED\ProjDataAccess account to the SDRED\ProjReportAuthors group. 77-project_server_2010_report_viewers


  11. Click OK.
The Project Server 2010 reporting and business intelligence functionality is now configured and ready for use. 59-project_server_2010_business_intelligence_center

61-project_server_2010_sample_reports


For additional
SharePoint or Project Server
related articles please visit my website at: