Export (0) Print
Expand All

Test Lab Guide: Configure the Excel Services unattended service account

SharePoint 2013

Published: December 18, 2012

Summary: Configure data refresh in Excel Services by using the unattended service account.

Applies to:  SharePoint Server 2013 Enterprise 

This article explains how to set up the Excel Services in SharePoint Server 2013 unattended service account in a test lab that is based on the Configure SharePoint Server 2013 Preview in a Three-Tier Farm test lab guide.

Important Important:

This scenario applies only to Microsoft TechNet Test Lab Guides.

In this article:

Scenario overview

This test lab guide explains how to configure the unattended service account for Excel Services. This includes:

  • Configuring sample data and a data access account

  • Configuring the unattended service account in Excel Services Global Settings

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

Before you begin

This test lab guide assumes that you have completed all the steps in the following test lab guides:

You must have completed all of the steps in the above-listed test lab guides before beginning the procedures in this test lab guide.

Create sample data

For the purposes of this lab, we will create a SQL Server database and table. The table contains some simple data that we can use in a data-connected workbook which we will publish to the SharePoint document library in the Business Intelligence Center.

note Note:

If you have previously followed the steps in Test Lab Guide: Configure Excel Services data refresh by using an embedded connection or Test Lab Guide: Configure Excel Services data refresh by using an external connection, this database may already exist. In this case, there is no need to recreate the database.

Use the following procedure to create the SQL Server database and table.

To create a database and table with sample data

  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. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

  4. On the toolbar, click New Query.

  5. Copy the following query to the query window:

    USE [master]
    GO
    CREATE DATABASE CorporateAccounts;
    GO
    USE [CorporateAccounts]
    GO
    CREATE TABLE [dbo].[Accounts](
    [Region] [varchar] (50) NULL,
    [Sales] [money] NULL,
    [OpEx] [money] NULL,
    [AssetValue] [money] NULL
    ) ON [PRIMARY]
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('North', 23486.56, 16874.53, 64789.34);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('South', 19864.35, 11265.84, 55349.37);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('East', 31824.65, 22849.67, 87318.27);
    GO
    INSERT INTO Accounts ([Region], [Sales], [OpEx], [AssetValue]) 
    VALUES ('West', 27691.26, 17628.43, 84287.67);
    GO
    
    
  6. On the toolbar, click Execute.

With the sample database and table created, the next step is to create an Active Directory account for use as the unattended service account.

Configure a data access account

The Excel Services 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 DC1 as CORP\Administrator.

  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 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 2013 workbook. Use the following procedure to create a SQL Server logon and grant that logon db_datareader access to the CorporateAccounts database.

To create a SQL Server logon

  1. Log on to SQL1 as CORP\User1.

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

  3. On the Connect to Server dialog box, select Database Engine as the Server type, type SQL1 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 CORP\ExcelUnattended.

  7. Under Select a page, click User Mapping.

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

  9. Click OK.

The account that we will use as the unattended service account now has access to the CorporateAccounts database, but we must also grant access to the CORP\ExcelWorkbookAuthors group in order for users to have database access from Excel. Use the following procedure to grant database access to the CORP\ExcelWorkbookAuthors group.

note Note:

If you created the database when you did a different test lab guide, the CORP\ExcelWorkbookAuthors group may already have database access.

To configure database access for Excel workbook authors

  1. In Management Studio, under Logins, double-click the CORP\ExcelWorkbookAuthors login.

  2. Under Select a page, click User Mapping.

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

  4. Click OK.

With the account created and granted the appropriate data access, we can now configure Excel Services.

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 the Excel Services unattended service account

  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, select the Create a new Unattended Service Account option.

  5. In the User Name: (Domain\UserName) text box, type CORP\ExcelUnattended.

  6. Type and confirm the password for the CORP\ExcelUnattended account.

  7. 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 using the unattended service account.

Publish 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 CLIENT1 by using the CORP\Susan.Burk account.

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

  3. If you are prompted to choose a template, choose the Blank workbook template.

  4. 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.

  5. In the Server name box, type SQL1.

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

  7. In the Select the database that contains the data you want section, select CorporateAccounts. In the table list section, select Accounts, and then click Next.

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

    note Note:

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

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

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

    1. Select the AssetValue check box.

    2. Select the OpEx check box.

    3. Select the Region check box.

    4. Select the Sales 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 2013 settings before we publish the workbook to the document library. By default, the Excel 2013 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 2013 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 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, and then click Browse.

  2. On the Save As dialog box, in the URL box, type http://WFE1/sites/BICenter, and then press Enter.

    note Note:

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

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

  4. In the File name box, type CorporateAccountsUnattended.xlsx.

  5. Click Save.

  6. When the save has completed, navigate to the document libaray in the Business Intelligence Center (http://wfe1/sites/BICenter/Documents) and click on the CorporateAccountsUnattended.xlsx file to render it in the browser using Excel Services.

Once the workbook has been loaded in a browser, the next step is to confirm that data refresh is working correctly.

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

Test 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 CLIENT1

  • Change a value in the CorporateAccounts database

  • Refresh the data displayed in the browser on CLIENT1 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 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 CorporateAccounts database and confirm that the changes appear in the workbook.

To update data in the CorporateAccounts database

  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. 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 [CorporateAccounts]
    GO
    UPDATE dbo.Accounts set dbo.Accounts.AssetValue = 65000.00 where dbo.Accounts.Region = 'North'
    GO
    
    
  6. In the toolbar, click Execute.

Return to the CLIENT1 virtual machine, where CorporateAccountsUnattendeds.xlsx is displayed in the browser.

Note the currently displayed value for AssetValue for the North region in the spreadsheet. We updated this row to a value of $65,000.00 in the Accounts table, so the new value should appear when we refresh the workbook. Use the following procedure to refresh the workbook.

note 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 Note:

    If a warning message appears, click OK.

The new value of 65000.00 should now appear for AssetValue for the North region, confirming that data refresh through the unattended service account is working.

note Note:

The Excel Services cache settings affect how quickly the new results appear when you refresh the workbook. In the Test Lab Guide: Configure Excel Services 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.

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