Identity delegation for Business Connectivity Services (SharePoint Server 2010)

  • Article

 

Applies to: SharePoint Server 2010

In this scenario you configure the Business Data Connectivity service application to use Kerberos constrained delegation to authenticate with SQL Server. Once it is configured, you create a new external content type and external list to test authentication and read operations within a SharePoint site.

In this scenario, the SharePoint Server Farm and BCS data source are both in the same domain. Therefore, we configure Kerberos constrained delegation to allow identity delegation to the back-end data source. If you are required to authenticate with data sources in other domains within the same forest, you have to configure basic (unconstrained) Kerberos delegation. Remember that BCS does not leverage the C2WTS; therefore you can use basic delegation.

Note

If you are installing on Windows Server 2008, you may have to install the following hotfix for Kerberos authentication:
A Kerberos authentication fails together with the error code 0X80090302 or 0x8009030f on a computer that is running Windows Server 2008 or Windows Vista when the AES algorithm is used (https://support.microsoft.com/kb/969083)

Scenario dependencies

To complete this scenario you have to have completed the following:

Configuration checklist

Area of configuration Description

Active Directory configuration

Create BCS Application Service Account

Validate Service Principal Names

Configure Delegation

SharePoint Server configuration

Start the BCS Service Instance

Create the BCS Service Application

Verification

Create a BCS External Content Type

Configure BCS Security

Create a BCS External List

Open the external list in the browser

Scenario Environment Details

Diagram of authentication process Diagram of delegation process

Step-by-step configuration instructions

Active Directory configuration

Create BCS Application Service Account

As a best practice Business Connectivity Services should run under its own domain identity. To configure the BCS Application an Active Directory account must be created. In this example the following accounts were created:

SharePoint Server service IIS App Pool Identity

Business Connectivity Service

vmlab\svcBDC

Validate Service Principal Names

BCS external content types run within the context of the IIS application pool using the ECT type when BCS data is used in SharePoint sites. For BCS to connect and authenticate with external data sources using Kerberos authentication the IIS application pool service account and the service account for the external data source must have service principal names configured. Refer to scenarios 1 & 2 in this document to configure and validate the necessary SPNS on the web applications and SQL Server service accounts.

Configure delegation

To allow BCS to delegate the client’s identity Kerberos delegation must be configured. Although constrained delegation is technically not required like Excel Services, unconstrained delegation can be used for BCS, it is a best practice to limit the scope of delegation the service is allowed to perform therefore constrained delegation will be configured in this example.

Each IIS application pool service account hosting the site running the ECT must be configured to allow delegation to the back-end services.

In our example the following delegation paths are needed:

Principal Type Principal Name Delegates To Service

User

svcPortal10App

MSSQLSVC/MySqlCluster.vmlab.local:1433

User

svcTeams10App

MSSQLSVC/MySqlCluster.vmlab.local:1433

To configure constrained delegation

  1. Open the Active Directory Object’s properties in Active Directory Users and Computers.

  2. Navigate to the Delegation tab.

    SP2010 Kerberos Guide228.gif

  3. Select Trust this user for delegation to specified services only.

    Note

    If you need BCS to authenticate with data sources within the same forest but outside of the domain that SharePoint Server resides in you will want to select Trust this computer for delegation to any service to configure basic delegation instead of constrained delegation. The BCS external content type will execute in the web application’s IIS worker process and does not leverage the C2WTS. Remember that cross forest Kerberos delegation is not possible.

  4. Click the Add button to select the service principal allowed to delegate to.

  5. Select User and Computers.

  6. Select the service account running the service you wish to delegate to. In this example it is the service account for the SQL Server service.

    Note

    The service account selected must have a SPN applied to it. In our example the SPN for this account was configured in a previous scenario.

  7. Click OK.

  8. Select the SPNs you would like to delegate, and then click OK.

  9. Select the services for the SQL Server cluster and click OK.

    You should now see the selected SPNS in the services to which this account can presented delegated credentials list.

  10. Repeat these steps for each delegation path identified earlier in this section.

Verify MSSQLSVC SPN for the Service Account running the service on the SQL Server (performed in Scenario 2)

Verify the SPN for Analysis Services service account (vmlab\svcSQL) exists with the following SetSPN command:

SetSPN -L vmlab\svcSQL

You should see the following:

MSSQLSVC/MySqlCluster

MSSQLSVC/MySqlCluster.vmlab.local:1433

SharePoint Server configuration

Start the BCS service instance

Before creating a BCS service application, start the BCS service on the designated farm servers.

  1. Open Central Administration.

  2. Under Services, select Manage services on server.

  3. In the Server Selection box in the upper-right corner, select the server(s) running Excel Services. In this example, it is VMSP10APP01.

  4. Start the Business Data Connectivity Service service.

Create the BCS service application

Next, configure a new BDC service application and application proxy to allow web applications to consume BDC services:

  1. Open Central Administration.

  2. Select Manage Service Applications under Application Management.

  3. Select New then Business Data Connectivity Service.

  4. Configure the new service application. Be sure to select the correct service account (create a new managed account if the BDC service account is not in the list).

Verification

Create a BCS external content type

To access external data through BDC a BDC eternal content type must be created. In this example we will use SharePoint Designer 2010 to create the external content type in the Portal web application (http://portal):

  1. Open SharePoint Designer 2010.

  2. Open the test site collection at http://portal.

  3. On the left hand navigation, click External Content Types.

  4. Select External Content Type in the New section of the ribbon in the upper left hand corner of the page.

  5. Give the External Content Type a display name.

  6. Then select Click here to discover external data sources and define operations.

  7. Click Add Connection.

  8. Select SQL Server from the Data Source Type dropdown list and add the information to connect to the test database. Be sure to select Connect with the User’s Identity to test delegation.

  9. Expand the new connection. Right-click the test table (Sales) and select Create All Operations.

  10. You should see an error explaining there isn’t a unique identifier defined. Select the identifier column and select the Map to Identifier check box. Click Finish to accept the default options and create the ECT operations.

  11. Click Save (CTRL+S). This will publish the ECT to the BDC service application metadata store.

Configure BCS security

Before clients can use the BCS external content type in the portal web application BCS permissions must be configured. BCS supports a granular permission model but for the purposes of this demo we will configure secure at the Metadata store level and propagate the security changes to all objects in the store.

  1. Open Central Administration.

  2. Select Manage Service Applications under Application Management.

  3. Click the link for the new Service Application, Business Data Services in this example.

  4. Select Set Metadata Store Permissions.

  5. In our example, we configured Enterprise Admins with all permissions and All Authenticated Users with all permissions except the Set Permissions permission.

  6. Ensure the Propagate permissions check box is selected and click OK to save your changes.

Create a BCS External List

To test the external content type we will configure an external list to display the external data in the portal application:

  1. Open SharePoint Designer 2010.

  2. Open the test site collection at http://portal.

  3. Select External Content Types on the left side.

  4. Click the content type that you created earlier.

  5. In the ribbon, click Create Lists & Form.

  6. If you are prompted to save the external content type, click Yes.

  7. On the Create List and Form dialog box, type a list name in the List Name text box, and then click OK.

Open the external list in the browser

  1. Open SharePoint Designer 2010.

  2. Open the test site collection at http://portal.

  3. Click "Lists and Libraries" in the left hand navigation.

  4. Select the external list at the bottom of the List and Libraries list.

  5. Click the Preview in Browser button.

    Internet Explorer will open and display the selected site and external list.

  6. Validate the external data is displayed correctly. To further validate the connection, change the source data in SQL Server Management Studio and refresh the browser page. You should see the data changes reflected in the browser.