- Open the site using SharePoint Designer when you want to create BDC. It can be a subsite as well.
- Navigate to External Content Types and click on New External Content Type.
- In the section “External Content Type Information” on the new page enter the Name and Display Name.
- In the Section “External Content Type Operations” click on “Click here to discover external data sources and define operations”.
- Now you will be navigated to the new page where if you are doing it for the first time will have to setup the connection, by clicking on “Add connection”.
- Select the data source – say SQL Server.
- Provide the database sever name, database name
- You can start with authentication type as current user identity.
- Once the connection is setup navigate to the database , and the table.
- Right click on it and select Create All operations.
- Now click on Save.
- Now navigate to Edit Connection properties if you have to change the authentication mode to “Impersonate Windows identity” ans provide the secure store AppID.
- Now open the browser and navigate to your site and create App of the type External List.
- Specify the BDC External type you have created, and create external list.
- Now setup users by opening Central Admin and navigating to the BDC Service application and selecting the BDC we have created.
- Click on Set permissions.
NOTE: Against SQL server table if you get errors for text field, convert it to varchar(max).