Microsoft Office Tutorials and References
In Depth Information
database. If the specified application is not defined in SSO, or the current user is not mapped to any
credentials, the connection will fail.
SSO credentials can be defined as integrated or database. When retrieving integrated credentials , the Excel
Calculation Server uses them to log in, and then it connects to the database while being impersonated
with those credentials. When retrieving database credentials , the Excel Calculation Server adds them to the
connection string, and uses the unattended account (if it is defined) to connect to the database.
The main advantage of SSO is that it allows different credentials to be used for each user or group of
users without requiring Kerberos-constrained delegation. In other words, it provides the SSO
administrator with flexibility in defining the credentials.
The main disadvantage is that it requires additional overhead to define and maintain the credential
The previous section covered the various aspects related to external data security. This section discusses
issues related to performance. In many cases, there is a tradeoff between security and performance, because
increased security often comes at the expense of performance.
External data queries can be extremely expensive. They may require connecting to another machine to
get the connection file from a DCL, get the credentials from SSO, and perform the actual query from the
database. Depending on the query and the database, this might take a long time, consume resources on
the database machine, transfer a lot of data over the network, and require memory and CPU resources
on the ECS machine to process the results.
Excel Services has a number of optimizations for caching data query results and connections, and for
sharing them between users. The net result is that, in many cases, the performance of Excel Services with
many users querying data from a few workbooks is much better than the performance of the same users
connecting to the database from Excel. Because of the caching of the results sharing, it puts much less
strain on the database itself.
The following sections describe how you can configure Excel Services for optimal performance.
Caching and Sharing
After performing a query, the ECS will cache each query result for up to the cache lifetime setting that is
defined for the trusted location. As long as the cache lifetime for that query has not expired, the ECS will
use the cached results instead of performing a new query.
The cache will be shared with other users who have the same credentials. When using
workbookembedded credentials or the unattended account, the cached results will be shared with all the other
users, because all users have the same credentials. When using SSO, the cached results will be shared
with all the other users who are mapped to the same SSO credentials. When using Kerberos-constrained
delegation, the cached results will not be shared, because each user has individual credentials.