SharePoint 2013 – create an external content type for SQL Server

I am creating an external content type to display records from an SQL table in a SharePoint list, using a target application in the Secure Store for authentication.  I will do this using SharePoint Designer 2013.  At a minimum, I must configure a ‘Read Item’ and a ‘Read List’ operation before I can create the external content type.  Here is how it is done:

1. Start SharePoint Designer 2013, click Open Site and enter the URL for the site
2. In the navigation pane, under Site Objects, click External Content Types
3. On the ribbon click External Content Type
4. Next to Name click New external content type and enter a name for the new external content type
5. Next to Display Name enter a more descriptive name for the external content type
6. Set the Office Item Type to Generic List
7. Click the Click here to discover external data sources and define operations link
8. Click Add Connection and change the Data Source Type to SQL Server and click OK
   

9. Enter the Database Server, Database Name, choose Connect with Impersonated Custom Identity and enter the Secure Store Application ID.  Click OK
   

   Note: If you have not created a Target Application in the Secure Store you may wish to follow this guide: Configure the Secure Store and create a Target Application

10. In the Data Source Explorer pane expand the Tables node in the tree view.  Right-click on the required table and select Create All Operations
11. Click Next
12. By default, all columns are selected to deselect those not required.  For the column that is to be used as a unique identifier check the Map to Identifier box and check the Show in Picker box.  Click Next

    Note: always review the errors and warnings and correct any problems.

13. Click Finish
14. On the ribbon click Summary View
15. In the list of fields select a field that has a unique value and click Set as Title in the ribbon
16. In Central Administration, under Application Management, click Manage service applications
17. Click on the Business Data Connectivity Service and the new external content type should be visible in the list
18. Check the box next to the new external content type and click Set Object Permissions in the ribbon
19. Add the user(s) that require access to the external content type and grant the appropriate permissions  Click OK