When playing with Business Connectivity Services (BCS) in SharePoint 2013, I found the following issues that other folks who are further ahead on this road than I am have documented:

In SharePoint 2013, you can use BCS to perform basic CRUD operations.

You might have a hard time starting workflows on external content types (ECTs). SharePoint 2013 workflow does not support CRUD on a BCS external list. Workarounds include:

• Create a SharePoint 2010 workflow in SharePoint 2013.
• Use a REST endpoint to the external list.

You can create an external content type (ECT) a couple of different ways:

• SharePoint Designer 2013, which allows you to re-use connectors and surface the ECT in an Office client.
• Visual Studio, which allows you to use an oData connection, create notifications on external events, and custom transformations.

You can add a custom action such as Search to the SharePoint Edit Control Block (ECB).

The limitations of BCS include:

• 100 connections
• 2000 items returned
• 60 second timeout to the database per query
• 3 MB size for data in a service call
• 60 second timeout for a service call

To use search on BCS content, do the following:

• Make sure the model is configured to allow crawls; configure the ECT using a declarative model for search.
• Use the SpecificFinder and Finder methods.
• Configure the profile page returned by search.
• Set permissions for the crawl in Central Admin.

You can create a sandboxed solution using JavaScript or the client-side object model (CSOM).

InfoPath list forms work well with BCS in SharePoint Enterprise.

Use the following connectors with BCS: SQL, Windows Communication Foundation (WCF), or oData (REST).

For the secure store service, map the credentials of the current user to different credentials required to access the external source.

For offline synchronization, use Office Outlook or Word.

ECT’s can be configured at the farm or Web app level.

To start a BCS solution in Visual Studio,install Visual Studio Tools for Office.

For authentication for an ECT, configure the connection properties of the ECT:

• For BDC Identity, enter Revert to Self for the farm account
• For Impersonate Windows ID, enter Secure Store Application ID. You can do this in Central Admin with the Secure Store Target Application. Group is the easiest to use. Group Restricted restricts special solutions. You can also use certificates.
• For Impersonate Custom Identity, enter a non-Windows account..

Stereotypes use Finder, SpecificFinder, Creator, Updater, and Deleter methods. You can use these methods in the SharePoint Designer Operations panel. Make sure you present data rows of reasonable size by implementing filter parameters.

Filters can include:

• LimitFilter – kind of a sledgehammer approach
• AcitivityID, Comparison – this approach is very useful for producing results
• Other filters include Timestamp, Username, PageNumber, and others.

You can also use stored procedures to drive external lists: AddName, GetName, GetNameWildCard, EditName, GetNames, RemoveName.

You can surface data source filters in the SharePoint external list settings.

ECT associations include the following:

• One-to-many – create in SharePoint Designer or Visual Studio
• Self
• Reverse – many-to-one. Reverse association is not inherent in the database schema. You must program the reverse association through a web service or stored procedure.
• Many-to-many – create only in Visual Studio; if you want to use SharePoint Designer, the workaround is to create two different one-to-many associations..

To create a one-to-many association, begin with the child ECT in SharePoint Designer and create a new association. Next, select the parent ECT and add filters.

You can use SharePoint Designer to create an ECT from a stored procedure in a SQL database. Click the following link for instructions:

https://blogs.msdn.microsoft.com/jysaikia/2010/12/15/a-step-by-step-guide-in-designing-bcs-entities-by-using-a-sql-stored-procedure/

Can import and export models after creating in SharePoint Designer

Actions are additional URLs that you can associate with an external content type that will show up on dropdown menu associated with items in external lists. Can configure in Central Admin. Can set up for anything that can be accessed with a URL, such as a query. Example: www.bing.com?query={0}. Shows up on ECB of item.

Profile pages are detailed information about items in external lists. Can configure in Central Admin. Can be landing pages for search results. Fully customizable.
Administrators and one account inside BDC service application to the service application must have all four permissions:
• Edit – allows you to edit
• Execute – allows you to use CRUD operations
• Selectable in Clients – allows you to choose from pick lists
• Set Permissions – change permissions in BDC service application

Configure in Central Admin on Manage Service Application page. Set up administrators here.

Throttling limits

default – 500 maximum connections allow to all external systems; global limit

2000- 25,000 – items returned from SQL query

60 seconds – 600 seconds –SQL or WCF connection timeout

3 Mb – 150 Mb – Size of returned WCF data

“access denied by business connectivity services” – model hasn’t been given appropriate permissions for people trying to use it.

Preferable to export models from SharePoint Designer. Saves with .bdcm extension. To work with the file in Visual Studio, rename the file with an .xml extension.

In XML file, entities section has entry for each external content type.

Methods section defines CRUD operations.

Command text enter has SQL statement.

Return shows all fields that will come back in query.

Actions section shows the actions you can define.

This model is portable to another environment. You can edit the RdbConnection Data Source

Property to enter a different server name for your SharePoint instance.(??) Can import this model file into Central Admin.

Recommended not to import permissions from model. Set permissions in Central Admin.

External content type operations include finder and specific finder, or in SharePoint Designer, Read List and Read Item.

You can use stereotypes with database views to create views on the external list. You’ll need to recreate the list to get the views to appear.

You can use filters with stored procedures and finder, specific finder, creator, updater, delete, assoc nav methods.

Connections from BCS to External System
2. Connect with user’s identity – must be added to SQL database
“failed anonymous” error signifies double hop problem.
The Default tab is for the thin client.
Client computer >> IIS >> impersonates user >> BDC run-time >> Database server
Second hop from BDC run-time to Database server isn’t allowed.
IIS won’t provide user ID; BCS forced to use anonymous.
2. Impersonate Windows Identity – Secure Store Service
From secure store, give me windows credentials associated with that user.
In Secure Store Service, create a new target application. Use Name, Email, and Type as Individual. These settings use the Windows credentials; use a form so users can enter credentials by selecting Use Default Page or other options for a pages.
Use Windows Name and Windows Password.
Will still see anonymous login problem.
Metadata cached in memory >> timer job clears >> takes one minute.
3. Impersonate Custom Identity
External database must use SQL authentication. Uses database credentials, SQL login and password. You don’t need a login page for this approach.
IN the Members box, enter users and groups.
Uses RDBCredentials.
Impersonate Custom Id is the most commonly-used scenario.

4.
If SQL database resides on the same server as SharePoint databases, this option works.
Kerberos constrained delegation solves the double-hop issue.

5. BDC Identity
This option is disabled by default. Uses RevertToSelf. Must run powershell to activate:
$bdc = Get-SPServiceApplication |
where {$_ -match “Business Data Connectivity Service”}
$bdc.RevertToSelfAllowed = $true
$bdc.Update();

Citations

Articles

https://msdn.microsoft.com/en-us/library/office/jj163782.aspx

https://technet.microsoft.com/en-us/library/jj683116.aspx

https://support.office.com/en-us/article/Introduction-to-external-data-676e60e7-d99f-463f-a173-65e9d63538c0
https://technet.microsoft.com/en-us/library/ee661743(v=office.14).aspx#Section3

PluralSight courses

https://www.pluralsight.com/courses/business-connectivity-services-fundamentals

Author: Scot Hillier

https://www.pluralsight.com/courses/cpt-sp2013-dev-ramp-part6

Author: Andrew Connell

Associations in BCS
https://msdn.microsoft.com/en-us/library/office/dn135194.aspx
https://msdn.microsoft.com/en-us/library/ff728816.aspx
Good example:
https://msdn.microsoft.com/en-us/library/gg607166.aspx
Create external content type based on SQL Table https://msdn.microsoft.com/en-us/library/office/ee557243(v=office.14).aspx

Videos
https://blogs.msdn.microsoft.com/bcs/2010/01/15/tooling-associations-in-sharepoint-designer-2010/