Configure Excel Services data access by using external data connection

 

1. Configure data access account
   - Log on to AD server, and create an account to access data source.
   
   Example: ExcelODCAccess
2. Grant Read access to data source
   
   Set the Db_datareader to data source
   
   
3. Configure Secure Store Services
   In the Secure Store target application, we will map a specific AD Group to the ExcelODCAccess account, Excel Services will use this account on their behalf to refresh data-connected workbooks.
   
   - Create an AD Group on AD Server, example: ExcelODCAccessGroup
   - Add accounts to this AD Group: User1, User2
   
4. Create a target Application on Secure Store Services
   - Open SharePoint 2010 Central Administration –> Application Management –> Manage Service Applications
   
   
   
   - Open Secure Store Service
   
   
   - New
   
   - Configure New Secure Store Service Target Application
   
   
   
   - Next
   
   
5. Associate the account (ExcelODCAccess) use to access data source to Target Application
   
   - Set Credentials
   
   
   
   
   
6. Create and publish an ODC file
   - Open Excel 2010 from a client PC and create a connection to SQL Server datasource
   
   
   
   - Enter the SQL Server name
   
   
   - Select the Database and SQL Table
   
   
   
   - Select Pivot Table Report view data
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   Save to SharePoint BI Center Site – Data Connection Library
   
   
   
7. Create and publish a Pivot Table
   
   
   
   
   
   
   
   
   -> Select the ODC Connection from Data Connection
   
   
8. Create the PivotTable Report
   
9. Save as into Documents of BI Center
   
   
10. Browser the document on Web Browser
    
    
    
11. Checking the connection by changing the value on data source and try to re-fresh the data, if it updates, so the configuration is correct.