Excel Services in Microsoft SharePoint Server 2010 provides three methods of using Secure Store to refresh the external data source in a workbook
=> You can use an unattended service account
=> You can specify a Secure Store target application in a workbook. (This is known as an embedded connection.)
=> You can use an Office Data Connection (ODC) file that specifies a Secure Store target application
Using the unattended service account involves configuring an Active Directory account and granting it access to your data, storing the credentials for this account in Secure Store, and configuring Excel Services to use the stored credentials when it needs to refresh the data in a workbook.
The following steps are required to configure the unattended service account in Excel Services.
=> Configure a data access account
=> Configure a Secure Store target application for the unattended service account
=> Configure Excel Services Global Settings
=> Configure a workbook to use the unattended service account
=> Configure a data access account
The unattended service account requires an Active Directory account for data access. Have your domain administrator create an Active Directory account that you can use for data access.
Once the account has been created, you must grant this account read access to the data source that you will be using in your data-connected Microsoft Excel workbook. Use the following procedure to create a logon for the data access account in Microsoft SQL Server.
To create a SQL Server logon for the data access account
=> In SQL Server Management Studio, connect to the database engine.
=> In Object Explorer, expand Security.
=> Right-click Logins, and then click New Login.
=> In the Login name box, type the name of the Active Directory account that you created for data
access.
=> In the Select a page section, click User Mapping.
=> Select the Map check box for the database that you want to provide access to, and then, under
Database role membership for: <database>, select the db_datareader check box.
=> Click OK.
Now that you have created a logon for the data access account and granted the account access to your data source, you must create a target application in Secure Store to contain the credentials for the data access account.
Configure a Secure Store target application for the unattended service account
The 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. 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. If you do not know which account is being used to run the Excel Services, follow these steps to determine which account that is.
To find the Windows identity assigned to the Excel Services application pool
=> On the SharePoint Central Administration Web site home page, click Security.
=> On the Security page, under General Security, click Configure service accounts.
=> On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.
When this option is selected, the name of the Excel Services Application appears in the box underneath the drop-down list. This is the Windows identity that you need for the Members list when you configure the unattended service account in Secure Store.
=> 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
=> On the Central Administration home page, in the Application Management section, click Manage service applications.
=> Click the Secure Store service application.
=> On the ribbon, click New.
=> In the Target Application ID box, type a unique identifier for this target application (for example,
ExcelServicesUnattended).
=> In the Display Name box, type a friendly name or short description.
=> In the Contact E-mail box, type the e-mail address for a contact for this target application.
=> In the Target Application Type drop-down list, select Group.
=> Click Next.
=> Leave the default credential fields, and then click Next.
=> On the Specify the membership settings page:
In the Target Application Administrators box, type the account of the user who will administer this target application.
Note:
You can specify multiple users or an Active Directory group.
=> In the Members box, type the Excel Services application pool account that you located in the previous procedure.
=> Click OK.
=> Once the target application is created, you must specify which credentials should be used by it. These are the credentials of the Active Directory account that you created previously, and which have been granted read access to your data source.
Use the following procedure to set the credentials for the target application.
To set the credentials for the target application
=> On the Secure Store Service Application page, in the Target Application ID column, point to the target application that you just created, click the arrow that appears, and then click Set Credentials.
=> Type the user name and password of the data access account.
=> Click OK.
=> Once you have set the credentials 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 Global Settings
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
=> On the Central Administration home page, in the Application Management section, click Manage
service applications.
=> On the Manage Service Applications page, click the Excel Services service application.
=> On the Manage the Excel Services page, click Global Settings.
=> On the Excel Services Settings page, in the External Data section, in the Application ID box, type the Target Application ID of the target application that you created.
=> Click OK.
With the Excel Services Global Settings configured, setup of the unattended service account is complete. The next section describes how to configure the Excel Services authentication settings in a data-connected Excel workbook to refresh the data with the unattended service account after the workbook has been published to a SharePoint document library.
Configure a workbook to use the unattended service account
=> You must configure the Excel Services Authentication Settings in the workbook before you publish it to SharePoint Server 2010. Doing so enables the workbook to use the unattended service account to refresh data when the workbook is rendered in Excel Services. Use the following procedure to configure the authentication settings.
To configure Excel Services authentication settings
=> In a data-connected Excel workbook, on the Data tab, click Connections.
=> On the Workbook Connections dialog box, select the data connection that you want to update, and then click Properties.
=> On the Connection Properties dialog box, on the Definition tab, click Authentication Settings.
=> On the Excel Services Authentication Settings dialog box, select the None option, and then click OK.
=> 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.
On the Workbook Connections dialog box, click Close.
With the Excel Services Authentication Settings set to None, Excel Services uses the unattended service account to refresh the data in the workbook after you have published it to SharePoint Server 2010
=> You can use an unattended service account
=> You can specify a Secure Store target application in a workbook. (This is known as an embedded connection.)
=> You can use an Office Data Connection (ODC) file that specifies a Secure Store target application
Using the unattended service account involves configuring an Active Directory account and granting it access to your data, storing the credentials for this account in Secure Store, and configuring Excel Services to use the stored credentials when it needs to refresh the data in a workbook.
The following steps are required to configure the unattended service account in Excel Services.
=> Configure a data access account
=> Configure a Secure Store target application for the unattended service account
=> Configure Excel Services Global Settings
=> Configure a workbook to use the unattended service account
=> Configure a data access account
The unattended service account requires an Active Directory account for data access. Have your domain administrator create an Active Directory account that you can use for data access.
Once the account has been created, you must grant this account read access to the data source that you will be using in your data-connected Microsoft Excel workbook. Use the following procedure to create a logon for the data access account in Microsoft SQL Server.
To create a SQL Server logon for the data access account
=> In SQL Server Management Studio, connect to the database engine.
=> In Object Explorer, expand Security.
=> Right-click Logins, and then click New Login.
=> In the Login name box, type the name of the Active Directory account that you created for data
access.
=> In the Select a page section, click User Mapping.
=> Select the Map check box for the database that you want to provide access to, and then, under
Database role membership for: <database>, select the db_datareader check box.
=> Click OK.
Now that you have created a logon for the data access account and granted the account access to your data source, you must create a target application in Secure Store to contain the credentials for the data access account.
Configure a Secure Store target application for the unattended service account
The 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. 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. If you do not know which account is being used to run the Excel Services, follow these steps to determine which account that is.
To find the Windows identity assigned to the Excel Services application pool
=> On the SharePoint Central Administration Web site home page, click Security.
=> On the Security page, under General Security, click Configure service accounts.
=> On the Service Account page, in the Credential Management section, from the drop-down list, select the application pool that runs Excel Services Application.
When this option is selected, the name of the Excel Services Application appears in the box underneath the drop-down list. This is the Windows identity that you need for the Members list when you configure the unattended service account in Secure Store.
=> 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
=> On the Central Administration home page, in the Application Management section, click Manage service applications.
=> Click the Secure Store service application.
=> On the ribbon, click New.
=> In the Target Application ID box, type a unique identifier for this target application (for example,
ExcelServicesUnattended).
=> In the Display Name box, type a friendly name or short description.
=> In the Contact E-mail box, type the e-mail address for a contact for this target application.
=> In the Target Application Type drop-down list, select Group.
=> Click Next.
=> Leave the default credential fields, and then click Next.
=> On the Specify the membership settings page:
In the Target Application Administrators box, type the account of the user who will administer this target application.
Note:
You can specify multiple users or an Active Directory group.
=> In the Members box, type the Excel Services application pool account that you located in the previous procedure.
=> Click OK.
=> Once the target application is created, you must specify which credentials should be used by it. These are the credentials of the Active Directory account that you created previously, and which have been granted read access to your data source.
Use the following procedure to set the credentials for the target application.
To set the credentials for the target application
=> On the Secure Store Service Application page, in the Target Application ID column, point to the target application that you just created, click the arrow that appears, and then click Set Credentials.
=> Type the user name and password of the data access account.
=> Click OK.
=> Once you have set the credentials 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 Global Settings
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
=> On the Central Administration home page, in the Application Management section, click Manage
service applications.
=> On the Manage Service Applications page, click the Excel Services service application.
=> On the Manage the Excel Services page, click Global Settings.
=> On the Excel Services Settings page, in the External Data section, in the Application ID box, type the Target Application ID of the target application that you created.
=> Click OK.
With the Excel Services Global Settings configured, setup of the unattended service account is complete. The next section describes how to configure the Excel Services authentication settings in a data-connected Excel workbook to refresh the data with the unattended service account after the workbook has been published to a SharePoint document library.
Configure a workbook to use the unattended service account
=> You must configure the Excel Services Authentication Settings in the workbook before you publish it to SharePoint Server 2010. Doing so enables the workbook to use the unattended service account to refresh data when the workbook is rendered in Excel Services. Use the following procedure to configure the authentication settings.
To configure Excel Services authentication settings
=> In a data-connected Excel workbook, on the Data tab, click Connections.
=> On the Workbook Connections dialog box, select the data connection that you want to update, and then click Properties.
=> On the Connection Properties dialog box, on the Definition tab, click Authentication Settings.
=> On the Excel Services Authentication Settings dialog box, select the None option, and then click OK.
=> 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.
On the Workbook Connections dialog box, click Close.
With the Excel Services Authentication Settings set to None, Excel Services uses the unattended service account to refresh the data in the workbook after you have published it to SharePoint Server 2010
No comments:
Post a Comment