Applications Server

SharePoint 2010 : Using Data Connection Libraries (part 1) - Restricting Data Connection Types & Adding Connections to Data Connection Libraries

11/17/2011 9:19:03 AM
We’ve managed to meet the requirements of our demonstration scenario and conveniently in the process have touched upon all the major features of Excel Services. Although our demonstration site works as required, in a real-world environment, we’d need to consider a few other aspects, particularly those with regard to how data connection information is stored and used.

Our sample workbook includes an embedded data connection that has been configured to use the credentials of the currently logged-in user. This approach has a few drawbacks, however. First, all users of the web site must also be granted permissions to access the data source referred to by the workbook. Second, anybody with permissions to edit the workbook can make changes to the data connection, possibly creating a connection to a server that’s not generally accessible. In such a case, the generated workbook would display just fine for users with appropriate permissions on the data source but would display an error message for other users. As well as creating connections to restricted servers, a user might also be able to create a connection that returned an unnecessarily large volume of data. For example, a user could read every single sales transaction record from an ERP system into a pivot table and then summarize the total sales data by quarter. While this would deliver the required end result, the performance implications of using such a Workbook in Excel Services are considerable.

To resolve these issues, you can restrict data connection availability to specific data connection libraries. Permissions can be set on the libraries so that only authorized users can create connections. This provides a much higher degree of control over what data sources can be used, how authentication is handled, and how queries are written. Furthermore, it allows users who are not familiar with the nuances of connecting to database servers and retrieving data to create useful Excel workbooks simply by selecting the appropriate data source from a list.

Restricting Data Connection Types

Let’s start by denying our embedded connection the rights to run under Excel Services:

  1. Using SharePoint 2010 Central Administration, select Manage Service Applications from the Application Management section.

  2. Select the appropriate Excel Web Service Application instance from the list of available services.

  3. In the Trusted File Locations section, add a new location specifically for our sample site. This will allow us to override the default settings for our sample site without affecting the settings for other sites that use our Excel Services instance. Click the Add Trusted File Location link.

  4. In the Location section, type the Address as http://<your server name>/12 and then check the Children Trusted checkbox. Notice that we’re using the physical server name rather than localhost because Excel Services configuration uses the URL that was assigned to an application when it was first created. Although we’ve accessed our sample site using the URL http://localhost/12, this URL isn’t configured within SharePoint and therefore can’t be used as a SharePoint trusted file location.

  5. In the External Data section, select the Trusted Data Connection Libraries Only option for Allow External Data.

  6. Currently our workbook is set up to refresh external data content manually. As it happens, the first time we select a currency from the drop-down list, the workbook is refreshed, causing the underlying external data to be reloaded. The caching settings within the External Data section determine how often external data is reloaded, and the default values mean that external data is cached for a period of 5 minutes. When we reconfigure our data connection to use a Data Connection library, we’ll set it up to refresh automatically when the workbook is loaded. To prevent the user from having to confirm this refresh every time the workbook is opened, uncheck the Refresh Warning Enabled checkbox.

  7. In the User-Defined Functions section. check the User-Defined Functions Allowed checkbox.

  8. Click OK to apply the settings.

We can now revisit our sample site home page to see the damage that we’ve done to our application. Bearing in mind that external data is refreshed only whenever the currency code is changed, select an alternative currency from the drop-down list to trigger a refresh. If our configuration changes have been properly applied, we should see an error, as shown:

Excel services makes extensive use of caching, both in terms of the workbooks and the external data that’s used within them. If the expected error is not shown, it’s most likely because the workbook has been cached on the server.

Adding Connections to Data Connection Libraries

Now that we’ve broken our sample application, we need to fix it again. We can do this by adding a new data connection library and then creating an Office Data Connection (.odc) file containing our connection settings. We’ll then tweak our workbook to use our new connection file instead of an embedded connection.

  1. Create a new Data Connection library. From our sample site home page, click the Documents link to view the list of document libraries. Click the Create link to show the Create dialog.

  2. From the Content & Data category, select Data Connection Library. Type the name of the new library as Sample Data Connections, and then click the Create button.

  3. With our new library available, we need to let Excel Services know that all data connections stored there can be trusted. Switch back to the Manage Excel Services page in Central Administration and click the Trusted Data Connection Libraries link.

  4. Click the Add Trusted Data Connection Library to add a new library. Type the address http://<your server name>/12/Sample%20Data%20Connections, and then click OK to save the changes.


    In the real world, where trusted connection libraries are used, it makes sense to have a single central connection library at a well-known location. Given that the purpose of the connection library is to allow all users to access trusted business data freely, making connections to the data as easy to find as possible is a worthwhile aim.

  5. Now we’ll create a data connection in the library and reconfigure our Excel workbook to use that instead. Navigate to the Excel Workbooks document library in our sample site and then edit our Last30DaysSales workbook using Excel client.

  6. From the Data menu, select the Connection button.

  7. In the Workbook Connection dialog, make sure the AdventureWorksList30DaysSales connection is selected and then click the Properties button.

  8. While we’re changing stuff, we’ll configure our data connection to reload external data when the file is opened. In the Usage tab, check the Refresh Data When Opening The File checkbox.

  9. Switch to the Definition tab. Click the Export Connection File button and then in the File Save dialog, save the connection to http://<your server name>/12/Sample Data Connections.

  10. Since we’re uploading the file to SharePoint, we’ll be prompted for some additional metadata. Make sure that the Content Type is set to Office Data Connection File, and then click OK to complete the upload.

  11. We can see in the Connection Properties dialog that the Connection file path has changed to our data connection library. Even though we’ve saved the connection details to our SharePoint server, Excel still uses an embedded copy of the connection details. To force a reload every time the connection is used, check the Always Use Connection File checkbox.

  12. Click OK to close the Connection Properties dialog, and then click Close to return to Excel. We can now save our revised workbook back to SharePoint by clicking the Save icon in the upper-left corner.

When we return to the home page of our sample site, we’ll find that our chart now functions properly. If an error is still being displayed, try recycling the application pool to clear out any cached copies of the workbook.

Other -----------------
- SharePoint 2010 : Excel Services - Using the JavaScript Object Model
- Optimizing Exchange 2007 Servers & Monitoring Exchange Server 2007
- Optimizing an Exchange Server 2007 Environment : Analyzing Capacity and Performance
- Exchange Server 2010 : Planning Certificates for Autodiscover (part 2) - Deploying Exchange Certificates
- Exchange Server 2010 : Planning Certificates for Autodiscover (part 1) - The X.509 Certificate Standard
- Exchange Server 2010 Autodiscover : Autodiscover Concepts
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 5) - Protecting DCs as Virtual Machines
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 4) - Performing Proactive Restores
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 3) - Relying on Windows Server Backup to Protect the Directory
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 2) - Relying on Built-in Directory Protection Measures
- Active Directory 2008 : Proactive Directory Maintenance and Data Store Protection (part 1) - Twelve Categories of AD DS Administration
- BizTalk 2009 : The BizTalk Management Database
- BizTalk 2009 : Handling Failed Messages and Errors
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 3) - Additional steps
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 2) - Loading sample company data & Creating a new Dynamics GP company
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 1) - Completing the Dynamics GP installation
- Microsoft Dynamics GP 2010 : Creating an ODBC data source
- Microsoft Dynamics AX 2009 : Working with Forms - Storing last form values
- Microsoft Dynamics AX 2009 : Creating modal forms & Changing common form appearance
- Exchange Server 2010 : Performing Tracking and Logging Activities in an Organization (part 2) - Using Protocol Logging & Using Connectivity Logging
Most View
- iPad SDK : New Graphics Functionality - We Are All Tool Users (part 3) - The Line Tool
- Developing Applications for Windows Phone 7 : Visual Containers
- Windows Server 2008: Understanding Read-Only Domain Controllers (part 2) - Understanding When to Leverage RODCs
- jQuery 1.3 : Headline rotator (part1) - Setting up the page
- Windows Phone 7 : Silencing Your Phone
- Sharepoint 2007 : Create Permission Levels for a Site
- SQL Server 2008 : ACID
- Windows 7 : Sending and Receiving Secure Email (part 1) - Setting Up an Email Account with a Digital ID
- Windows Phone 7 : Browsing the Web - Understanding the Mobile Web
- Microsoft Exchange Server 2003: Configuring Recipient Objects (part 9) - Managing Mail-Enabled Groups
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS