Configure the Excel Services unattended service account for a BI test environment

 

Applies to: SharePoint Server 2010, Excel Services

Important

This article is part of the Configuring a BI infrastructure: Hands-on labs series. To complete the steps in this article, you must first have completed:

Excel Services in Microsoft SharePoint Server 2010 provides three methods of using Secure Store to refresh the external data source in a workbook:

  • The unattended service account

  • A data connection embedded in the workbook

  • A data connection through an Office Data Connection (ODC) file

This lab covers the unattended service account option. For more information about how to use an embedded connection, see Configure Excel Services data access by using embedded data connections. For more information about how to use an ODC file, see Configure Excel Services data access by using external data connections in a BI test environment.

Using the unattended service account involves configuring an Active Directory account access to your data, storing the credentials for this account in Secure Store, and configuring Excel Services to use this account when it needs to refresh the data in workbook.

The following steps are required to configure the unattended service account in Excel Services.

  • Configure a data access account

  • Configure the Secure Store Service

  • Configure the Excel Services Global Settings

In this lab, we will also create a sample PivotTable report in Microsoft Excel that uses the ContosoRetailDW database and publish it to the Business Intelligence Center to confirm that data refresh is working.

Configure a data access account

When you configure the unattended service account, you have to grant the account access to the data source to which your Excel Services account will be connected. The unattended service account requires an Active Directory account for data access. Use the following procedure to create an Active Directory account for data access.

To create an Active Directory account for data access

  1. Log on to Contoso-DC as Contoso\Administrator.

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

  3. Expand the contoso.local node.

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

  5. In the Full name and User logon name boxes, type ExcelUnattended.

  6. Click Next.

  7. Type and confirm a password for the account.

  8. Clear the User must change password at next logon check box.

  9. Select the Password never expires check box.

  10. Click Next, and then click Finish.

Once the account has been created, the next step is to grant that account Read access to the required data. In a production scenario, you would grant access to whatever data source that you need to access from your Excel 2010 workbook. Use the following procedure to create a SQL Server logon and grant that logon Data Reader access to the ContosoRetailDW table.

To create a SQL Server logon

  1. Log on to Contoso-SQL as Contoso\SQLAdmin.

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

  3. On the Connect to Server dialog box, select Database Engine as the Server type, type Contoso-SQL in the Server name box, and then click Connect.

  4. In Object Explorer, expand Security.

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

  6. In the Login name box, type Contoso\ExcelUnattended.

  7. Under Select a page, click User Mapping.

  8. Select the Map check box for the ContosoRetailDW database, and then, under Database role membership for: ContosoRetailDW, select the db_datareader check box.

  9. Click OK.

With the account created and granted the appropriate data access, we can now configure Secure Store Service.

Configure Secure Store Service

Secure Store Service uses a target application to define connection parameters and authorized users. The unattended service account is generally considered a catch-all account that is used for general data access, so usually all users are granted access to the unattended service account through the target application.

In order to grant all users data access through the unattended service account, the account that is running the Excel Services application pool is added as a Member of the Secure Store target application. In Configure Excel Services for a BI test environment, we created the Contoso\ExcelAppPool account to run the Excel Services application pool. However, in a production environment you may not know what this account is, so we will describe the steps to determine the application pool account.

To find the Windows identity assigned to the Excel Services application pool

  1. Log in to Contoso-AppSrv using the Contoso\FarmAdmin account.

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

  3. On the SharePoint Central Administration Web site home page, click Security.

  4. On the Security page, under General Security, click Configure service accounts.

  5. On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application (in this case, Service Application Pool - ExcelServicesAppPool).

    When this option is selected, the name of the Excel Services Application appears in the box underneath the drop-down list (in this case, Excel Services).

  6. Note that Contoso\ExcelAppPool is displayed in the Select an account for this component drop-down list. This is the Windows identity that you will need for the Members list when you configure the unattended service account in Secure Store.

  7. Click Cancel

Once you have determined which account is being used to run the application pool for the Excel Services service application, the next step is to create a target application in Secure Store for the unattended service account. Use the following procedure to create the target application.

To create a target application for the unattended service account

  1. On the Central Administration home page, under Application Management, click Manage service applications.

  2. Click the Secure Store Service service application.

  3. On the ribbon, click New.

  4. In the Target Application ID box, type ExcelServicesUnattended.

  5. In the Display Name box, type ExcelServicesUnattended.

  6. In the Contact E-mail box, type farmadmin@contoso.local.

    Note

    Although e-mail notifications are not configured in this environment, Contact E-mail is a required field.

  7. In the Target Application Type drop-down list, select Group.

  8. Click Next.

  9. Leave the default credential fields, and then click Next.

  10. On the Specify the membership settings page:

    • In the Target Application Administrators box, type Contoso\FarmAdmin.

    • In the Members box, because we will use the target application to store credentials for the unattended service account, type the Excel Services application pool account that we located in the previous procedure (Contoso\ExcelAppPool).

  11. Click OK.

Once the target application has been created, you must specify which credentials should be used by it. The target application credentials are those to which data access has been granted — in our case, the Contoso\ExcelUnattended account to which we granted db_datareader access to the ContosoRetailDW database.

Use the following procedure to set the credentials for the target application.

To set the credentials for the target application

  1. On the Secure Store Service Application page, in the Target Application ID column, point to ExcelServicesUnattended, click the arrow that appears, and then click Set Credentials.

  2. In the Windows User Name box, type Contoso\ExcelUnattended.

  3. Type and confirm the password for the Contoso\ExcelUnattended account.

  4. Click OK.

With the credentials set for the target application, the Secure Store Service configuration for the unattended service account is complete. The next step is to configure Excel Services to use this target application for the unattended service account.

Configure Excel Services

The unattended service account configuration is part of the Excel Services Global Settings. Use the following procedure to configure the unattended service account in Excel Services.

To configure Excel Services Global Settings

  1. On the Central Administration home page, in the Application Management section, click Manage service applications.

  2. On the Manage Service Applications page, click Excel Services.

  3. On the Manage the Excel Services page, click Global Settings.

  4. On the Excel Services Settings page, in the External Data section, in the Application ID box, type ExcelServicesUnattended.

  5. Click OK.

With the Excel Services Global Settings configured, setup of the unattended service account is complete. In the next section, we create an Excel workbook with external data and publish it to the SharePoint document library in the Business Intelligence Center. We then test data refresh by using the unattended service account.

Publishing a workbook with an external data source

To test the configuration of the unattended service account, we must first create a workbook with an external data connection. Use the following procedure to create the workbook.

To create a workbook with an external data connection

  1. Log on to Contoso-Client by using the Contoso\Susan.Burk account.

  2. Click Start, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010.

  3. On the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server. This starts the Data Connection Wizard, which has three pages.

  4. In the Server name box, type Contoso-SQL.

  5. Under Log on credentials, click the Use Windows Authentication option, and then click Next to go to page 2 of the Data Connection Wizard.

  6. In the Select the database that contains the data you want section, select ContosoRetailDW. In the Connect to a specific table section, select DimStore, and then click Next.

  7. On the Save Data Connection File and Finish page, click Finish.

    Note

    If you are prompted to overwrite the connection file, click Yes.

  8. On the Import Data dialog box, select the PivotTable Report option, and then click OK.

  9. In the pane on the right side, in the Pivot Table Field List:

    1. Select the SellingAreaSize check box.

    2. Select the StoreType check box.

Leave the Excel workbook open for the next procedure.

We now have a data-connected workbook that contains a report. However, we must modify the Excel 2010 settings before we publish the workbook to the document library. By default, the Excel 2010 settings are configured to use Windows authentication, which is not the correct setting for using the unattended service account. Use the following procedure to modify the Excel 2010 authentication settings in the workbook that we just created.

To configure Excel Services authentication settings

  1. In the Excel workbook, on the Data tab, click Connections.

  2. On the Workbook Connections dialog box, click Properties.

  3. On the Connection Properties dialog box, on the Definition tab, click Authentication Settings.

  4. On the Excel Services Authentication Settings dialog box, select the None option, and then click OK.

  5. On the Connection Properties dialog box, click OK.

    Note

    If you see a warning that the link to the external connection file will be removed, click Yes. (Although Excel requires you to save a connection file, that file is not required by Excel Services for authentication.)

  6. On the Workbook Connections dialog box, click Close.

Leave the Excel workbook open for the next procedure.

Now that we have set the Excel Services authentication settings, the next step is to publish the workbook to the document library in the Business Intelligence Center and confirm that data refresh is working correctly.

To publish a workbook to a SharePoint document library

  1. In the Excel workbook, click File, click Save & Send, and then click Save to SharePoint.

  2. In the Locations section, click Browse for a location, and then click Save As.

  3. On the Save As dialog box, in the URL box, type http://Contoso-AppSrv/sites/BICenter, and then press Enter.

    Note

    It may take several moments for the file list to refresh.

  4. In the Document Libraries list, double-click Documents.

  5. In the File name box, type SellingAreas.xlsx.

  6. Ensure that the Open with Excel in the browser check box is selected, and then click Save.

Once the workbook has been saved to the document library, it will automatically load in a browser. The next step is to confirm that data refresh is working correctly.

Leave the browser that displays the SellingAreas.xlsx workbook open for the procedures in the next section.

Testing data refresh

To confirm that data refresh works with the unattended service account, we will follow these steps:

  • Refresh the data currently displayed in the browser on Contoso-Client

  • Change a value in the ContosoRetailDW database

  • Refresh the data displayed in the browser on Contoso-Client again and confirm that the new information is displayed

First, we will refresh the existing data. Use the following procedure to refresh the data in the browser.

To refresh a data-connected workbook by using Excel Services

  1. In the browser, on the Data drop-down list, click Refresh All Connections.

    Note

    If a warning message appears, click OK.

If the unattended service account is configured correctly, the data will refresh without error, although we will not see any changes in data values at this point. The next step is to change a value in the ContosoRetailDW database and confirm that the changes appear in the workbook.

To update data in the ContosoRetailDW database

  1. Log on to Contoso-SQL by using the Contoso\SQLAdmin account.

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

  3. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

  4. In the toolbar, click New Query.

  5. Copy the following query to the query window:

    USE [ContosoRetailDW]
    GO
    UPDATE dbo.DimStore set dbo.DimStore.SellingAreaSize = 500 where dbo.DimStore.StoreType = 'Catalog'
    GO
    
  6. In the toolbar, click Execute.

  7. Return to Contoso-Client where the SellingArea.xlsx workbook is displayed.

Return to the Contoso-Client virtual machine, where SellingAreas.xlsx is displayed in the browser.

Note the currently displayed number for the Catalog row in the spreadsheet. We updated this row to a value of 500 in the DimStore table, so the new value should appear when we refresh the workbook. Use the following procedure to refresh the workbook.

Note

If you see a warning that the page has expired, click OK and restart the procedure.

To refresh a data-connected workbook by using Excel Services

  1. In the browser, on the Data drop-down list, click Refresh All Connections.

    Note

    If a warning message appears, click OK.

The new value of 500 should now appear in the Catalog row of the report, confirming that data refresh through the unattended service account is working.

Note

The Excel Services cache settings affect how quickly the new results appear when you refresh the workbook. In the Configure Excel Services for a BI test environment lab, we set the cache settings to zero (0), allowing immediate refresh. In a production environment, refresh will likely take longer depending on how you configure the cache.