Sharepoint BCS (Business Connectivity Services) configuration for SQL Server

Recently we have a request for SharePoint data display, but the data source is SQL Server.So the we leveraged BCS (Business Connectivity Services) to implement this task.

There are several authentication options when configuring an External Content Type to point to a SQL server list (tables, views etc).

Connect with User’s Identity

Connect with Impersonated Windows Identity

Connect with Impersonated Custom Identity

Normally we should choose “Connect with Impersonated Windows Identity”, because the final permission can be controlled at Sharepoint list level. The alias account credentials are kept in the Secure Store Service Application.

I.  SharePoint Central Admin

  1. Central Admin > Manage Service Application. Create a new Secure Store Service Application and edit it. The msdn details step can be referenced from https://technet.microsoft.com/en-CA/library/ee806866.aspx
  2. Create a new target application using the Group type.
  3. Once the Target Application has been created open the associated drop down list and select Set Credentials and enter the Username/Password of the AD alias account.

II. SharePoint Designer

  1. Create new External Content Type
  2. Create new connection and use the Connect with Impersonated Windows Identity option and enter the Secure Store Application ID you just created in Central Admin above
  3. Create a Read List and Read Item operation at the very least
  4. Save the External Content Type
  5. Open up the options menu of the newly created ECT to create an External List

III. SharePoint Central Admin

  1. Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
  2. From the Business Data Connectivity Service Application find the new ECT, select the box next to it, and go to Set Object Permissions located on the command ribbon.
  3. Select a user or group with access to the ECT and grant at least Execute permissions to it.
  4. Select Set Metadata Store Permissions located on the command ribbon

The rest steps can be referenced by Connecting to SQL Server Using the External Content Type Feature. Basically it just use Sharepoint designer to connect to the External content type, and create a list to reference the ECT. Please note that all tables/views from SQL Server side need to map the primary key (identity key), and a Sharepoint site needs to be created first for designer to connect with.

There is another article can be referenced as “SharePoint 2010 using BCS with SQL Server database”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s