Chapter 38. Excel Services Configuration
Excel Services is a highly utilized feature when operating SharePoint Enterprise Edition. Deployment of this feature is straightforward, well documented on TechNet and rather uncomplicated. Instead of providing a series of additional screenshots that explain the deployment of Excel Services, we will focus on the following in this chapter:
Understanding how Excel Services interacts with PowerPivot
Excel Services and the Secure Store
What is the Secure Store Service?
Configuring the unattended service account for Excel Services
Allowing cross-domain access
One of the keys to properly configuring Excel Services is to understand the data flow and where each component should live in a farm environment (Figure 38-1).
Excel web access, the Excel Web Services, and the PowerPivot Web Service will be installed and run on the Web Front End. The PowerPivot Web Service is a WCF service that handles the routing of requests from external data connections. There is no management or configuration available to be done to the PowerPivot Web Service, hence our leaving it out of the PowerPivot chapters above.
Excel Services and the Secure Store
There are three primary methods of using Excel Services with the Secure Store Service:
- Unattended Service Account
The unattended service account is an account that is used by Excel Services to provide access to all users in the farm. Use the unattended service account for accessing data that is not sensitive or where restricted access is not required.
- Embedded Connections
A Secure Store target application can be specified directly in the workbook. When published to a SharePoint document library, the embedded connection will be used to connect to Excel Services and the specified target application credential is used to refresh the data.
- External Data Connections
A Secure Store target application can be specified in an Office Data Connection (ODC) file and then a connection to that ODC file is made in Excel. When the workbook is published to SharePoint, it maintains its connection to the ODC file. The connection information in the ODC file is used when Excel Services refreshes the data in the workbook.
What Is the Secure Store Service?
In SharePoint 2010, Microsoft replaced the little used Single Sign-On (SSO) component from MOSS 2007 with the Secure Store Service. The Secure Store Service is a claims-aware authorization service that has a database for storing encrypted username and password information that can be used to access external data sources.
A Secure Store entry is created by assigning a set of credentials to a unique identifier known as a target application ID. The credentials are stored in the Secure Store Database and encrypted using a passphrase. In the Secure Store Service, an ITPro specifies a set of credentials that has access to that data source and a group of users to be granted access to a data source. Users are then granted access to leverage the target application ID’s credentials upon request.
The Secure Store Service can accept claims security tokens and decrypt them to get the application ID, and then do a lookup. When the Security Token Service (STS) issues a security token after an authentication request, the Secure Store Service decrypts the token and then reads the target application ID. Once the Secure Store Service retrieves the credentials from the Secure Store database, the credentials are then used to authorize access to the specified data source.
You can then specify the target application ID in a workbook, an Office Data Connection (ODC) file, or in Excel Services Global Settings, and Excel Services will use the stored credentials on behalf of the specified users to refresh data in a data-connected workbook.
Configuring the Unattended Service Account for Excel Services
Our best explanation of the proper use of the unattended service account for Excel Services comes from directly quoting Central Administration:
The unattended service account is a single account that all workbooks can use to refresh data. It is required when workbook connections specify “None” for authentication, or when any non-Windows credentials are used to refresh data. To use this account, specify the application ID that is used to reference the unattended service account credentials.
The first step in configuring the unattended service account for Excel Services is to validate that it is not already set up. To do this, you will go to Central Administration → Application Management → Manage Service Applications → Excel Service Application and select Global Settings.
You will notice that the application ID in Figure 38-2 is blank. You will need to go to the Secure Store Service and create a target application ID (SSS ID) for this purpose. It is recommended that you set up a separate domain service account for this purpose.
To create the unattended service account for Excel Services SSS ID in the Secure Store do the following: using Central Administration, go to Application Management, and then in Manage Services Applications select your Secure Store Service. You should already see several Secure Store entries listed, as shown in Figure 38-3.
To create the SSS ID, click New in the ribbon, enter the SSS ID, display name and, contact email address, set the target application type to Individual, and click Next (Figure 38-4).
The next screen (Figure 38-5) will give you the option to set a field name and field type; however, the default settings of “Windows User Name” and “Windows Password” are required for the field type so it is recommended that this setting be left alone. You can change the field name to anything you like as it is merely a data label in the UI. The masked flag ticked will make it so that when entering the password in the “Set Credentials” screen, the password will not be viewable on the screen.
You will then be prompted to specify the administrators for this SSS ID, as shown in Figure 38-6. You can assign a single user, group, or multiples of either or both.
After you click OK, the process completes and you will find the new ExcelServicesUnattendedAccount SSS ID in the list and will now be able to set the credentials that will be used when calling this SSS ID.
There are two ways to set the credentials for the SSS ID: via the ribbon or the drop-down context menu shown in Figure 38-7.
You will need to add a credential owner; this is similar in nature to specifying a Site Collection owner in that there can be many, but one must be specified initially. Next, specify the domain account username and password. It is important to know that this screen does not validate the account information you entered. If the account username and/or password are incorrect, it will still pass the information along. See Figure 38-8.
As a result of selecting this specific entry to be a target application type of “Individual,” there is no group permission to assign to this SSS ID.
Now that our SSS ID has been created, we can return to the Excel Services Application’s Global Settings page and enter our SSS ID, as shown in Figure 38-9.
Allow Cross-Domain Access
This setting is frequently troubling for security-minded individuals. The name alone sounds like something that you just don’t want to do. The reality is far less scary than the initial impression. This setting specifically allows the Excel Services service application to talk to other HTTP domains such as Alternate Access Mapping named SharePoint zones or other approved data sources within your own domain that may exist on a different DNS zone. Another important factor to keep in mind is that this functionality is a pull capability, not a push. This does not allow external data sources to cross domains and make modifications; instead, it allows Excel Services to reach out to other domains and request data. This is core functionality of the SharePoint BI story, as referenced in Chapter 30.
To make this change to the Excel Services service application, navigate to the Excel Services Application’s Global Settings page and check the Allow Cross Domain Access box, as shown in Figure 38-10.
Summary
In this chapter, we provided a series of additional screenshots that explain the deployment of Excel Services and focused on understanding how Excel Services interacts with PowerPivot, Excel Services, and the Secure Store. We also reviewed how to configure the unattended service account for Excel Services.