create and configure Business Connectivity Services on SharePoint 2010

Requirements

AD user and group

First step is creation of a Service Account on AD which it will be used as Impersonated User (i.e. the broker that will connect to LOB System on behalf of the group of people that should have access to data but doesn’t access to the database).

Second step is the creation of Security Group. The Members will be all people that should have access to LOB System.

SQL user login

Grant access to BCSUser account on database that will be used to get data.

Setup Environment

Creation of Target Application ID

Connect to Sharepoint Central Administration and go to Application Management than Manage Service Application.


Click Secure Store Service

Click on New to create a target Application ID and write target Application ID, Display Name and select Target Application Type to Group.

Press next.

By default the fields represent how will collect the credential of the Impersonated User, in my case Windows Account. press next to go to last step where Sharepoint wants the list of users that can manage Target Application settings and the list of users (or group) that will be mapped to the credential defined foe the Target Application. In may case I use the Sharepoint Service Account as Target Application Administrator and the Secure Group previously created sps.securestorebcsuser

Now I have create a new Secure Store Service. The process is not complete. It is need to set credential to set the mapping for Impersonated Users.

In BCS Windows User Name insert the account previously created on AD.

External Content Type

Now we need to create external content type.
Open Sharepoint 2010 Designer (note that is strongly reccomended that you use designer on a machine in the same domain of Sharepoint 2010 Server) and click on External Content Type, than populate the fields Name and Display Name. Afterwords click on external system to define our connectivity.

Add a connection and select SQL Server as data Source Type

Define SQl Server Connection properties

When connection is validated, you are able to connect to LOB System, expand it and select table o view that you want map, than right click and select type of operation (in my case All Operation)

After the wizard, to define operation properties, the validation is complete, click save button to save information about External Content Type to the BDC Metadata Store.

Now is necessary to set store permission to have permission to use External Content Type created.
Connect to Sharepoint Central Administration and go to Application Management than Manage Service Application and click to Business Data Connectivity Service.
Select External Content Type Map_ViewTot01Monitor, click the custom action and select set permission.

After this operation you can consume BCS using External List, Chart Web Part or…