Export (0) Print
Expand All

Test Lab Guide: Configure Excel Services

SharePoint 2013
 

Applies to: SharePoint Server 2013 Enterprise

Topic Last Modified: 2013-12-18

Summary: Configure Excel Services in a test lab environment.

This article explains how to set up Excel Services in SharePoint Server 2013 in a test lab that is based on the Configure SharePoint Server 2013 Preview in a Three-Tier Farm and Test Lab Guide: Create a Business Intelligence Baseline Environment test lab guides.

ImportantImportant:
This scenario applies only to Microsoft TechNet Test Lab Guides.

In this article:

This test lab guide explains how to configure Excel Services. This includes:

  • Creating an Active Directory account to run the Excel Services application pool

  • Starting the Excel Calculation Services service

  • Creating a Excel Services service application

  • Configuring data access for Excel users.

Before starting, make sure you have completed the steps in Test Lab Guide: Configure SharePoint Server 2013 in a Three-Tier Farm, including the prerequisite test lab guides discussed in that document. This test lab uses the three-tier infrastructure that is created in the Configure SharePoint Server 2013 in a Three-Tier Farm test lab guide.

You must also complete all the steps in Test Lab Guide: Create a Business Intelligence Baseline Environment. This test lab guide includes configuring Excel Services trusted locations for libraries in the Business Intelligence Center.

The Excel Services service application requires an application pool to operate. The application pool requires an Active Directory account to run. Use the following procedure to create an Active Directory account for the application pool.

To create an Active Directory account
  1. Log on to DC1 as Corp\Administrator.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand corp.contoso.com.

  4. Right-click ServiceAccounts, click, New, and then click User.

  5. In the Full name text box, type Excel Services Application Pool.

  6. In the User logon name text box, type ExcelAppPool.

  7. Click Next.

  8. Type and confirm a password for the account.

  9. Clear the User must change password at next logon checkbox.

  10. Select the Password never expires checkbox.

  11. Click Next.

  12. Click Finish.

Once the account has been created, the next step is to register it as a managed account in SharePoint Server 2013. Use the following procedure to register the managed account.

To register a managed account in SharePoint Server
  1. Log on to APP1 as Corp\User1.

  2. Click Start, click All Programs, click Microsoft SharePoint 2013 Products, and then click SharePoint 2013 Central Administration.

  3. On the Central Administration home page, in the left navigation, click Security.

  4. On the Security page, in the General Security section, click Configure managed accounts.

  5. On the Managed Accounts page, click Register Managed Account.

  6. In the User name text box, type CORP\ExcelAppPool.

  7. In the Password text box, type the password for the CORP\ExcelAppPool account.

  8. Click OK.

In order for Excel Services to work, the application pool account must have access to the SharePoint Server 2013 content database. Use the following procedure to grant the needed access to the content database.

To grant content database access to the managed account
  1. On APP1, click Start, click All Programs, click Microsoft SharePoint 2013 Products, right-click SharePoint 2013 Management Shell, and then click Run as Administrator.

  2. At the Windows PowerShell Command Prompt, type the following (press Enter after each line):

    $w = Get-SPWebApplication -identity http://WFE1
    $w.GrantAccessToProcessIdentity("CORP\ExcelAppPool")
    

Once the CORP\ExcelAppPool account has been registered as a managed account and granted access to the content database, the next step is to start the Excel Calculation Services service and create an Excel Services service application.

Use the following procedure to start the Excel Calculation Services service on APP1.

To start the Excel Calculation Services service
  1. On the Central Administration home page, in the System Settings section, click Manage services on server.

  2. Above the Service list, in the Server drop-down list, ensure APP1 is selected.

  3. In the Service list, click Start next to Excel Calculation Services.

Once the service has been started, the next step is to create an Excel Services service application.

To create an Excel Services service application
  1. On the Central Administration home page, in the Application Management section, click Manage service applications.

  2. On the Manage Service Applications page, click New, and then click Excel Services Application.

  3. In the Name box, type Excel Services.

  4. Select the Create new application pool option and type ExcelServicesAppPool in the text box.

  5. Select the Configurable option, and, from the drop-down list, select CORP\ExcelAppPool.

  6. Click OK.

Excel Services has now been configured. The next step is to configure trusted locations.

Excel Services will only load workbooks from trusted file locations. Use the following procedure to create a trusted file location in the Business Intelligence Center.

To configure a trusted file location
  1. On the Central Administration home page, under Application Management, click Manage services applications.

  2. In the list of service applications, click Excel Services.

  3. Click Trusted File Locations.

  4. On the Trusted File Locations page, click Add Trusted File Location.

  5. In the Location section:

    1. Type http://WFE1/sites/BICenter/Documents in the Address text box.

    2. Select the Children trusted check box.

  6. In the External Data section:

    1. Under Allow External Data, select the Trusted data connection libraries and embedded option.

    2. In the Automatic refresh and Manual refresh text boxes, type 0.

      NoteNote:
      In a production environment, you would not typically set these values to 0. (Setting the cache values to 0 can cause additional resource utilization on the server.) However, in this series of labs, we are testing data refresh for data-connected workbooks, and setting the cache settings to 0 enables us to see data refresh results immediately.
  7. Click OK.

By creating a trusted file location, we can now publish data-connected workbooks with embedded data connections. However, for Excel Services to correctly render data-connected workbooks with external data connections, we must configure a trusted data connection library.

To configure a trusted data connection library
  1. On the Central Administration home page, in the Application Management section, click Manage service applications.

  2. In the list of service applications, click Excel Services.

  3. Click Trusted Data Connection Libraries.

  4. On the Trusted Data Connection Libraries page, click Add Trusted Data Connection Library.

  5. In the Address box, typehttp://WFE1/sites/BICenter/Data%20Connections.

  6. Click OK.

When you have completed all of the previous procedures, Excel Services configuration is complete. The next step is to configure data access for the users who will create data-connected Excel workbooks.

In the following Excel Services labs, we will be configuring data refresh for Excel workbooks by using Secure Store. However, you should know that there are differences in the way that Excel Services and the Excel client application authenticate with data sources.

Although Excel Services can use Secure Store to authenticate with a data source when rendering a data-connected workbook, Excel does not use Secure Store for data authentication. Excel requires that workbook authors have direct database access in order to access external data from the workbook.

In this case, we will create an Active Directory group to contain our workbook authors, and then create a SQL Server logon for that Active Directory group.

To create an Active Directory group
  1. Log on to DC1 by using the CORP\administrator account.

  2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

  3. Expand the corp.contoso.com node.

  4. Right-click Users, click New, and then click Group.

  5. In the Group Name box, type ExcelWorkbookAuthors, and then click OK.

Next, we need to add users to the group. In this case, we will add the user Susan Burk to the group because Susan Burk has Contribute access to the Business Intelligence Center where we will publish our data-connected workbooks in successive Excel Services labs.

NoteNote:
Although we are only adding a single user to the group in this lab, in a production scenario you would add all the workbook authors to the group. (Using a group provides easier administration than adding individual logons for each user.) Depending on how your data is organized in your production environment, you may want to use multiple groups if different sets of users need access to different databases.
To populate the ExcelWorkbookAuthors Active Directory group
  1. In Active Directory Users and Computers, double-click the ExcelWorkbookAuthors group.

  2. On the Members tab, click Add.

  3. In the Enter object names to select box, type CORP\Susan.Burk, and then click OK.

  4. Verify that Susan Burk appears in the Members list, and then click OK.

Now that the Susan Burk account has been added to the ExcelWorkbookAuthors group, the next step is to create a SQL Server logon for that group.

To create a SQL Server logon
  1. Log on to SQL1 by using the CORP\USER1 account.

  2. Click Start, click All Programs, click Microsoft SQL Server 2012, and then click SQL Server Management Studio.

  3. Connect to the Database Engine.

  4. Expand the Security node.

  5. Right-click Logins, and then click New Login.

  6. On the Login – New page, click Search.

  7. On the Select User or Group dialog box, click Object Types.

  8. On the Object Types dialog box, select the Groups check box, and then click OK.

  9. On the Select User or Group dialog box, in the Enter the object name to select box, type CORP\ExcelWorkbookAuthors, and then click OK.

  10. On the Login – New page, click OK.

When you have completed all of the previous procedures, Excel Services will be operational and ready to use. The next step is to configure security access to your data sources. The following labs cover how to configure data refresh using Secure Store:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft