I have just recently started working with external lists using BDC and was quite surprised to discover that I can’t really interact with the data in any way using SharePoint 2013 workflows (Even just read from it). Or so it seems at least, but I hope you might be able to help out.
I would expect to be able to use the REST endpoint for lists to be able to query the external list, but no matter what I do I get
Unauthorized as response.
What I have done so far:
- Made sure all users have access to the external content type
- Made sure all users have read access to the external list
- Made sure all user have access to the secure store service
- Created a user with read privileges to the SQL database (The source of the external list) and set it in the secure store as a group login
What I can do so far:
- See the entire external list in my browser
- Query the external list using U2U CAML Query Builder
- Call the
lists(guid’[guid of the external list]’)/Itemsendpoint using the browser and get the expected xml response
- Call the
lists(guid’[guid of the external list]’)endpoint using a workflow and get the expected json response
What I cannot do yet:
- Use the query build by the U2U tool. I get
- Call the
lists(guid’[guid of the external list]’)/Itemsendpoint using a workflow. I get
In essence I need a way – using SharePoint 2013 workflows – to pull the value from a field in an item in the external list where the ID match my query.
It would seem that my workflow is simply missing permissions to execute the query, but I cannot for the life of me figure out what I might have overlooked.
I realize SharePoint 2010 workflows can pull the data without using the REST calls, but that’s not an option in our current environment.
From the ULS logs I get this:
System.UnauthorizedAccessException: Apps cannot use External Content Types defined in the Business Data Connectivity Shared Service Application metadata catalog. Apps should package any External Content Type definitions in a BDC model file which is packaged and deployed as part of the SharePoint App.
Sadly that doesn't really help me much.