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
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
Create a new target application using the Group type.
- 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
Create new External Content Type
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
Create a Read List and Read Item operation at the very least
Save the External Content Type
Open up the options menu of the newly created ECT to create an External List
III. SharePoint Central Admin
- Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
- 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.
- Select a user or group with access to the ECT and grant at least Execute permissions to it.
- 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”