Why is this important?
Users do not care what you have specified on the SharePoint side. They already have this nice Excel sheet connected to a SQL database and they have heard that you can render it on a SharePoint site as well. But somehow it doesn’t seem to work. What will you tell your end users? How are you going to educate them?To get a better understanding of authentication settings we need to make a difference between getting data from within client applications and from within a browser on a SharePoint site or through a SharePoint Service.
Let us focus on the client side.
End user tools
The end user has a number of client tools available which are capable of fetching data from data sources:- Rich office clients
- Word
- Outlook
- SharePoint Workspace
- Excel
- InfoPath
- BI Authoring tools
- Excel with or without PowerPivot for Excel
- Report Builder 3
- PerformancePoint Dashboard Designer
- SharePoint Designer 2010
- Visio 2010
- Business Intelligence Developer Studio
Data connections
Secondly, there are various ways of defining external sources:- Embedded data connections
- Like in Excel you create them through the Ribbon
- External data connections
- .odc or .udc files you have saved to a SharePoint data connection library
- External content types
- Running on the client through the BDC runtime
Data sources
Finally, there are various data sources to fetch data from. To name a few:- Databases
- WCF services
- Web services
- Custom data sources
- .Net Connectivity Assemblies
External content types
Let us focus a bit on External Content types. If you need to get data from an external source you may want to use External content types in case you would like to reuse the data in various places or need basic CRUD actions on the data itself. A tool like SharePoint Designer 2010 may be used to define these External Content Types. I will not describe the various steps you need to perform but instead focus on the different authentication settings.When configuring External content types the authentication mode is selected. Each authentication mode represents a different way of processing incoming user credentials and mapping them to a set of credentials for the external content type.
To simplify matters the following list shows you how the various settings map to what:
- Mapped to Impersonated Identity
- Digest credentials
- External Credentials
- RDBCredentials
- Windows credentials
- Mapped to Users Identity
- Pass through
- Mapped to BDC Identity
- Revert to self
Impersonated Identity
The first four settings all map to the impersonated identity. It basically means that the user credentials will be replaced by another credential set. And that is where the Secure Store plays its part. The Secure Store allows you to define so called target applications.A target application defines a mapping between a (group of) member(s) to a credential set. The credential set can be a Windows Identity or an External Identity. A member can be the user account or for instance an active directory group or in some cases the application pool account of a SharePoint service.
Pass through
Meaning that the users identity will be used to access the data source. In case the data source is more than one server hop away from the user, this will fail as the user identity cannot be delegated to the data source. Another option would be for instance to use Kerberos authentication.Revert to self
It means that the user identity will be replaced by the BDC identity which is the identity the BDC runtime is running on the client side. In browser based scenarios this would often result in the application pool id of the BCS service application.What we just described are the client side authentication settings for External content types. These content types may surface in for instance Outlook.
In most BI authoring scenarios however, you are not using External Content Types. Instead you are connecting your client tools to data sources like SQL Server Analysis cubes, SQL databases or Web Services.
It greatly depends which tool you are using, which service and if you are doing this client side only or are planning to use it on SharePoint sites as well.
Embedded vs. external connection files
In all those cases your data connection can be defined as embedded or as an external file. What does that mean? Embedded means that you are using the various client applications to define data sources which are saved as part of your file. The file could be Excel or perhaps Visio etc. External means that the file pulls the data connection info from another file, called the ODC or UDC file. Often there are various ways of “externalizing” existing embedded connections to external ones. Obviously, the benefit of external ones are reusability amongst others.Classified Intel: Excel Services
Take for instance Excel. If you connect your Excel sheet to an external data source you can do so through the Ribbon -> Data -> Get External Data
If you connect to a SQL database for instance, a wizard will guide you through the configuration steps. Generally it means specifying a server name, a database name, a table and that’s it. The most common mistake: although this works for you in the client tool, it most likely won’t work in the browser.
If you render the same sheet with Excel services you would need to specific the settings through the button you can see in the Screenshot: Authentication settings. I will describe how that works later on, but keep in mind that this is the easiest example!
It already becomes different when using for instance Excel with PowerPivot. And if you would be using InfoPath than specifying these settings will keep you looking as there is no such button and you need to export the connection file, modify the XML and so forth.
For now we are talking client side only and it is important to remember that this kind of setting is called embedded.
Now, it may be that you are consolidating connection settings into a SharePoint connection library. In that case you can export the connection we just made in Excel by again using the Ribbon -> Data -> Connections -> select your connection -> Properties -> Select Definition Tab -> Choose button Export Connection File.
You can store the connection file in a SharePoint library for use. Later on you will see that you need to trust connection libraries to be used by SharePoint. But for now, we are ok with that.
Prepare for lift off: PowerPivot for Excel
If you have installed PowerPivot for Excel than you will notice that the PowerPivot window has its own Get External Data part in the Ribbon:
If you would like to pull info from Analysis Services for instance you will be prompted for authentication settings again. Keep in mind that this is authentication against the cube not the database.
Now this is information that will be used when rendering it on the client. If you would like to publish it to SharePoint and would like to use PowerPivot for SharePoint to render the pivottable, then this information may or may not be used. Instead you would need to define authentication settings through the browser:
Data refresh for PowerPivot
Credentials
Data sources
Refresh on behalf of the user
As you can see in the screenshots it basically tells SharePoint that whenever the user clicks on the refresh button in the browser, which authentication setting should be used: the account which has been specified as part of the PowerPivot for SharePoint Service Application. Or a predefined Windows user credential or the credentials specified by a target application ID. As I have explained earlier the target application ID (SSS ID) is a mapping between members and a set of credentials. If you are using an SSS ID here, PowerPivot for SharePoint will look up that application in the Secure Store and next will look for a member which matches the current user. If it finds that current user as a member it will map it to a different set of credentials.Refresh on behalf of SharePoint
The other section is quite similar to the previous one and will be used by SharePoint to refresh data according to a schedule. The difference is that the first setting is referring to the credentials contained in the workbook. Meaning that in this case the settings you have defined as a user in the Excel workbook – PowerPivot window, will be used.On the server side it is different
If you have made it this far you are probably interested in how things are configured on the SharePoint site. The answer is: it depends on the Service Application you are using. Still, most authentication settings and service applications share a similar model:Connection settings
- NONE: If a server side connection setting specifies none then this means that SharePoint will use the identity of the SA. In most cases this is the Application pool identity the Service App is running in. The important thing here is that you can specify which SA’s use which target application of the Secure Store in those cases where none is specified. The general idea is to map the application pool account to an unattended service account which will have access on the data source. You don’t want to add the application pool identities to the data sources. Instead you create a target application for each Service Application where the application pool identity is the member and the set credentials are the so called unattended service account. You will add the unattended service account (read account) access to your data source. This is a very useful scenario and often used for BI solutions. In most BI solutions you don’t want to query complex cubes on a per user identity. It would definitely require large amounts of memory on the SharePoint server as each query result will be cached for each user. In those cases you are using the unattended service account which will greatly reduce the cache and improve performance.
- Windows credentials: again, if you have Kerberos enabled this may be useful for you but otherwise you might find yourself facing a double hop scenario and windows cannot pass your credentials a second time.
- SSS ID: a scenario where you actually know who has access to the data source, for instance if you have a special BI group of readers defined. In that case you create a target application at the Secure Store and add the AD group as a member and a set of credentials as the account which actually has access to the data source. Very useful for all kinds of locked down situations.
Complex?
Do you get it? There are many places to define authentication and you have to carefully plan those when rolling out advanced features. Below you will find an example. You will be surprised!Summarized example for Excel and PowerPivot:
The example below will show you where you can specific authentication settings. Together with the explanation I have given you in this blog article you should be able to understand which authentication setting is applicable when. The example also shows you Excel and PowerPivot only. For other client tools and service applications it is different but at the same time similar patterns are applicable.
- Excel:
- Data tab in the Ribbon
- Client side settings
- Windows credentials
- Database account
- Excel services settings
- None: no authentication (meaning that the Excel Services unattended target application ID will be used)
- Windows authentication (useful when using Kerberos)
- SSS ID (use the mapping as defined by the target application within the Secure Store)
- PowerPivot for Excel
- Data tab in the Ribbon
- Client side settings
- Windows credentials
- Database account
- SharePoint
- Excel services Service App
- Unattended target application ID
- PowerPivot for SharePoint Service App
- Unattended target application ID (so called, as specified by administrator)
- Excel sheet
- PowerPivot refresh schedule
- On user behalf
- As specified by admin
- Specific windows credentials
- SSS ID
- Automatic according to schedule
- As specified by workbook
- Specific user account
- SSS ID
Mini debriefing
What about the other Service Applications?The pattern for most other Service Applications is the same:- Either you are directly connecting to the data source: for instance from the client tools like Excel
- Or you are connecting through an External Content Type
- Or a SharePoint service is connecting for you either by passing the credentials, using a specific target application or by falling back to a so called unattended target application. In those cases the Secure Store determines the target credentials which will be used to access the data source.
It is almost impossible to completely cover all differences at once but patterns are similar and MSDN will help as well.
