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:
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.
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.
Switch to the User Mapping page and map our
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:
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.
Share Point 2010 Related Interview Questions
|Web Services Interview Questions||XML Interview Questions|
|Share Point 2010 Interview Questions||ASP.NET Interview Questions|
|Share Point Administration Interview Questions||BizTalk Admin Interview Questions|
|Microsoft Office SharePoint Server (MOSS) Interview Questions||Biztalk Server Interview Questions|
|Asp Dot Net Mvc 4 Interview Questions||Biztalk Esb Toolkit Interview Questions|
|InfoPath Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.