Business Connectivity Services is the new SharePoint 2010 replacement for the Business Data Catalog (BDC) which was introduced with Microsoft Office SharePoint Server 2007 Enterprise. Business Connectivity Services provides many improvements over the BDC including the ability to write back to the data source, offline capabilities through the office suite such as SharePoint Workspace, improved tooling and additional features such as External Lists. Our site provides several articles introducing the BCS which can be found here:
Within this article we are going to explore how Business Connectivity Services Authentication works and how to configure it successfully, some of the issues that you will face and how to overcome or work around them.
There are two types of authentication to consider when it comes to authenticating with their backend data sources; User Identity or Impersonation. User Identity is where you use the identity of the logged on user, and want to pass those credentials through to the data source, and return data to SharePoint that the user should be allowed to see based upon the identity of the user. Impersonation allows the user to log on with their credentials, but connect to the data source as an alternate user, thus impersonating another user. Both of these options have pro’s and con’s.
It is a very common requirement for companies to wish for the User Identity to be passed through to the data source. This often works well in a development environment but fails when you move to production. The reason for that is something known as the Double Hop Issue. The Double Hop issue existed with the BDC and still exists with Business Connectivity Services. The reason being is that it is a Browser/IIS issue and not a SharePoint issue. When you connect to the data source using PassThrough authentication, the security token for the Windows Authenticated user can only be passed one hop. That one hop is from the browser to IIS. In a development environment, it is common for the database server to be installed on the same physical machine as IIS, and therefore the one hop is all that is required. However, in production it is rare to have the database server on the same machine and therefore two hops are required to pass the credentials from the browser to IIS to SQL. The user is therefore not authenticated.
One workaround for the Double Hop Issue while still maintaining the users identity is to configure your Environment to use Kerberos. Kerberos is difficult to configure and if it hasn’t been configured as part of the current architecture, it is difficult to get the go ahead from your administrators to all of a sudden change the authentication protocol that you use for SharePoint. Kerberos overcomes the double hop issue by allowing you to setup trusts between each server. E.g. a trust between IIS and SQL. The passing of credentials is then renewed at the IIS server.
If passing through the user’s identity is not important to you, then you can use Impersonation to overcome the double hop issue and still be authenticated at the data source. One of the options of Business Connectivity Services authentication is the ‘RevertToSelf’ option. RevertToSelf doesn’t use the Users Identity but instead uses the Process Account (Account that the BCS Runtime service is running with). This means that every SharePoint user regardless of their identity will connect to the data source as the same user. This sounds like a security issue, but actually works very well, since you can assign permissions to each user or group at the SharePoint level. You can assign permissions such as execute and write back. This is actually a good practice; since you have the option to revisit the permissions assigned to each Table, View, and Stored Procedure in SQL and assign it directly to the SharePoint user account.
Another method that Business Connectivity Services Authentication supports is the Secure Store Services (SSS). Secure Store Services is the replacement for Single Sign On (SSO) within SharePoint 2007. SSS provides you with the option to store credentials in a Secure Store Database which resides on the SharePoint Server. Credentials are then mapped to either from ‘user to user’ or ‘Domain Group to user’. This allows the credentials to be renewed again at the SharePoint server. SSS will be discussed in a blog post later this week.
We cannot really recommend one Business Connectivity Services authentication mechanism over the other as every organization has different configurations and political or environmental issues can sometimes dictate the choice.
Connecting to SQL Server using SharePoint Designer provides you with the following options for Authentication:
· Connect with User’s Identity
· Connect with Impersonated Windows Identity
· Connect with Impersonated Custom Identity
This post introduces the types of Business Connectivity Services authentication to consider. We will cover each type of authentication in detail over the next few days.