Using Data Connection Libraries - Share Point 2010

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.
    • In the Location section, type the Address 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, this URL isn’t configured within SharePoint and therefore can’t be used as a SharePoint trusted file location.
    • In the External Data section, select the Trusted Data Connection Libraries Only option for Allow External Data.
    • 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.
  4. In the User-Defined Functions section. check the User-Defined Functions Allowed checkbox.
  5. Click OK to apply the settings.

Restricting Data Connection Types

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.
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. To force the configuration changes to take immediate effect, you can recycle the SharePoint Web Services application pool using the methods described earlier.
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 and then click OK to save the changes.
  5. TIP 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.
  6. 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.
  7. From the Data menu, select the Connection button.
  8. In the Workbook Connection dialog, make sure the AdventureWorksList30DaysSales connection is selected and then click the Properties button.
  9. 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.
  10. 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>/chapter12/ Sample Data Connections.
  11. 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.
  12. 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.
  13. 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.

Connecting to Data Using Alternative Credentials
One of the reasons for creating a central library of trusted data connections is to allow administrators to identify specific user credentials for each connection rather than using the Windows credentials of the calling user. So far, our connection is still set up to use Windows authentication, so let’s take a quick look at how we can change this to use specific credentials.

  1. Before we can change our connection to use specific credentials, we need to set up the credentials in question. Create a new local user account named testuser, and give this account read-only access to our sample database.
  2. Within SQL Server Management Studio, add a new Login for our testuser account. This can be achieved by expanding the Security node, right-clicking the Logins node, and then selecting New Login from the context menu.
  3. In the Login - New dialog, enter details of our testuser account in the Login Name textbox. Type the default database as AdventureWorks.
  4. Switch to the User Mapping page and map our
    Connecting to Data Using Alternative Credentials

    • Click OK to complete the process.

Now that we have a created specific user account with the appropriate permissions to our database, we can look at how these permissions can be used within Excel Services. Three possibilities exist for using specific credentials within Excel Services:

Embedded in Connection String We could embed credentials in the connection string that we used when creating the data connection. This has an obvious drawback in that the username and password are freely visible to anybody with access to view the data connection. Furthermore, this won’t allow us to use a specific Windows user account.

No Credentials This option isn’t as crazy as it sounds. When a data connection is created and the authentication type is set to None, Excel Services uses default credentials to connect. This account is known as an “unattended service account" and is configured using the Secure Store Service.

Secure Store Service Account The Secure Store Service provides a secure mapping of user credentials between systems. We can make use of this service within Excel Services to retrieve securely stored connection credentials. The difference between explicitly using the Secure Store Service and using it indirectly via the Unattended Service account is that the Unattended Service account is configured globally for the entire Excel Services application. However, when we’re explicitly using the Secure Store Service, we can specify which application ID should be used for each connection.

Configuring the Secure Store Service
Since two of our three options make use of the Secure Store Service, let’s look at how to set it up:

  1. Within SharePoint Central Administration, select Manage Service Applications from the Application Management section.
  2. From the List of Service Applications, select the Secure Store Service Application and then, from the Service Applications menu, click Manage.
  3. If this is the first time the Secure Store Service has been used, we need to initialize it by clicking the Generate New Key button in the Key Management section of the Edit menu. Once the service has been initialized with a key, we can add a new application for use with our Excel Services external data store. Before we move on to take this step, however, I’ll clarify what an application is and how it works within the Secure Store Service. An application is similar to an Excel worksheet: along the top of the worksheet are columns that relate to the properties that are defined by the application. These might include things like Username and Password but can include practically any content. Each row represents a mapping for a particular user account or group of users. For each mapping, values are stored in the respective columns. When a request is made to the Secure Store Service, the request will contain details of the application, and using this together with the SharePoint user credentials, the appropriate row will be selected and returned. Now let’s add a new application for Excel Services:
    • In the Manage Target Applications section, click the New button.
    • In the Target Application Settings page, enter the Target Application ID as Excel Services Unattended Account.
    • Set the Display Name to the same name as the Target Application ID, and add an appropriate e-mail address in the Contact E-Mail text box.
  4. Various Target Applications Types are available when we’re creating applications, but these can be split into two broad categories: Individual and Group. Individual types create a one-to-one mapping between a SharePoint user and a set of properties, or to refer back to our earlier analogy, each row in our workbook represents only one SharePoint user. Group types, on the other hand, create a many-to-one mapping. Effectively, our workbook has only one row, which can be mapped to any number of SharePoint user accounts or groups.
  5. For our Excel Services application, we’ll create a Group application, and this will allow us to map all users to a single set of credentials. Click Next to proceed to the next step of the process.
  6. Specify which field will be used by our application. To refer to our earlier analogy, each field is a column in our worksheet. For our purposes, the default fields—Windows User Name and Windows Password—are sufficient. Click Next to move on.
  7. Now specify which accounts have administrative permissions for this application. Enter an appropriate username in the Target Application Administrators picker. As well as specifying administrative users, we also need to specify which users and groups will use the credentials that are mapped to our application. In this case, we want everybody to use the same credentials, so we’ll specify All Users (windows). Click OK to finish creating our application.
  8. To set credentials for our application, we must select the application by clicking the checkbox next to it and then click the Set button in the Credentials section of the ribbon, as shown:
  9. Configuring the Secure Store Service

  10. Enter details of the testuser account that we created earlier, remembering to prefix the username with the local computer name (that is, yourcomputer estuser). Click OK to store the credentials.

NOTEAlthough we’ve created a group application for use with our Unattended Service account, we could also have created an Individual application and mapped the Windows username of the Excel Services service account to our testuser account. The drawback in doing this, however, is that if the service account changes, somebody must remember to add the new service account manually to the application. However, this approach does benefit from being more secure since only an appropriately configured account can use our testuser credentials. Our Group application allows our testuser account to be used by any Windows account.
Now that we’ve set up our Secure Store Service application, we can take the final step necessary to use it as our Unattended Service Account within Excel Services.

  1. Navigate to the Manage Excel Services page within Central Administration. Click the Global Settings link.
  2. Scroll down to the External Data section, and in the Application ID text box, enter the ID for our Secure Store Service application—in our case, type Excel Services Unattended Account. Click OK to commit the configuration changes. Before we can see our unattended service account in action, we need to reconfigure our data connection to use no authentication:
  1. Browse to the Sample Data Connections library of our sample site and then, from the context menu, edit the AdventureWorksList30DaysSales connection file using Excel.
  2. The Excel client application is opened automatically. In the Security Notice dialog, click Enable to allow our data connection to execute. A table of data from our external data source will be displayed. This data is just for reference purposes; it effectively allows us to see what our data connection will return when executed.
  3. To modify the data connection properties, from the Data menu click Properties, and then in the External Data Properties dialog, select the icon to the right of the Name text box, as shown:
  4. Configuring the Secure Store Service
  5. We’ll be presented with the familiar Connection Properties dialog that we used earlier when creating our workbook. Switch to the Definition tab, and then click the Authentication Settings button.
  6. Set the authentication type to None, and then click OK to close the dialog.
  7. As we did when we modified the connection, click Export Connection File to save the changes back to our data connection library.
  8. After the connection has been exported, click OK to close the dialog, and then close Excel. Discard the workbook that was automatically created. We’re now ready to return to our sample site home page to confirm that our data is still being refreshed properly. We can use SQL Server Profiler to confirm that connections to the database are now being made using our testuser account. You’ve learned how to set up an Unattended Service account using the Secure Store Service. To use a specific application ID, the process is practically identical. The only difference is that in the Connection Properties dialog, rather than specifying the authentication type as None, the type is set to SSS and the application ID is entered.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Share Point 2010 Topics