How to Create External Lists from SQL Server using BCS in SharePoint 2016?

Business Data Connectivity Services enables SharePoint 2013/2016 and Office 2013/2016 clients to interact with data that doesn’t live in SharePoint. BCS allows you to integrate external data into SharePoint. The external list provides a familiar list-like user experience. Let’s see how to create an External List in SharePoint that grabs the data stored in the SQL Server Database and displays it in SharePoint 2016 without writing a single line of code.

Prerequisites: Before creating an external content type-External List, You need to have Business Data Connectivity Service (BCS) application and/or secure store service application configured in your SharePoint farm! Here is how to Create and configure BDC service application in SharePoint 2016

Step 1: Create External Content Type in SharePoint Designer 2013:

Follow these steps to create an External Content Type using SharePoint Designer 2013.

• Open your SharePoint 2016 site in SharePoint Designer 2013
• Select External Content Types in the left Navigation. Click on External Content Type under New Group.

  

• In the External Content Type creation page, Enter the Name and Display Name for the external content type. Click on “Click here to discover external data sources and define operations” link next to “External System”.

  

• This takes you to the Operation Designer page. Now, Click on “Add a Connection” button to connect to the database. Select Data source type as SQL Server.

  

• Setup SQL Server connection properties. Enter the Database server name, Database name and choose the “Connect with impersonated windows identity” and enter the Application ID you created to connect with SQL server in Secure store service.I’ve created a target application ID in SharePoint 2016 secure store service. Refer this article on How to Create Target Application ID in Secure Store Service in SharePoint 2016

  
•  Once the connection is made with the provided database, Data Source Explorer will be filled with the database objects we specified. Now select the relevant table to which we are going to connect from SharePoint. Right click the table and select the option “Read List Items”. Repeat this step for “Read List” operation as well. 

  

• You’ll get Operation Properties wizard. Run through this wizard, specify appropriate settings such as filters, necessary fields and click on Finish button once done. 

  

• You’ll be presented with a list of operations that your External Content Type can do, as shown below.

  

• Hit Save button in SharePoint designer to complete creation of external content type.

Step 2: Create External List in SharePoint 2016:
The Next step is to expose that SQL table through an External List.

• Open your SharePoint site in Browser, Add an App >> Choose External List
• You will be presented with an External Content Type Picker and you will be able to view the Content Type you created a while ago. Choose that content type, Provide a name to your External list and hit OK

  

• Now you can see your SharePoint list populated from the database table!
  

You may have to explicitly provide permission to External Content type through the BCS service application if you have not already set metadata permission to all users! Last but not least: We can’t compare External Lists with standard SharePoint lists. See my another post on some of the significant limitations when using external lists: 25 Limitations of External Lists in SharePoint