Microsoft Office Tutorials and References
In Depth Information
Single sign-on (SSO)
Each of these has its advantages and disadvantages. None of them is a silver bullet, and you should use
the methods that work best for your specific configuration and needs. The following sections detail how
to set up the workbooks and the server for each of these options, and when to use them.
Authoring the Workbook
Excel Services can support multiple credentialing options at the same time. When you’re authoring a
connection in Excel, define the credentials you want to use to connect to data on the server.
In the Workbook Connections dialog box in Excel, select a connection and click Properties. In the
Connection Properties dialog box, switch to the Definition tab and click the Authentication Settings button. Select
from one of the following settings:
Windows Authentication — Select this to use the end user’s credentials. This setting works in a
one-machine topology or when Kerberos-constrained delegation is enabled.
SSO — Select this to use the end user’s SSO credentials. This requires you to define the SSO
None — When you select this setting, the end user’s credentials are not delegated. You can
embed a user and password in the connection string. If there is no such user and password,
predefined server credentials are used to connect to the database.
In this topology, the WFE, the ECS, and the database server are on the same machine, as shown in
Figure 5-8. Because there is only one hop, the credentials are delegated.
The major advantage of this topology is that it is extremely simple to set up and secure. The
disadvantages are as follows:
It will work only against databases that you own and can deploy to the same machine. You
cannot connect to any other databases in your organization.
In terms of performance, you are limited by how much you can scale up your machine. You
will not be able to add machines to the farm and scale out.
Some of the optimizations described later in this chapter in the “Performance” section will not
work. Each user has a separate connection with different credentials and therefore potentially
different results, so query results are not shared between users. When connecting to Analysis
Services, a separate optimization can allow different users to have the same roles for sharing
This topology is great if you want to build a full solution that has Excel Services and a database, and you
own and control both pieces.
To make this work, you must set the workbook connection to use Windows Authentication. In addition,
you need to set up the access model between the WFE and ECS to Delegation (see Chapter 8 for details).