Configurating an external data type in SharePoint 2013 Part 2

This is part two of the series on configuring an external data type in SharePoint 2013. The first part is here.

At this point you should have already finished configuring the external data type, and created a list based on that external data type.

When you are trying to access the list you get an “Access Denied by Business Data Connectivity” error. This is caused by your configuration of your Business Data Connectivity Service. To change that you have to:

1. Go to “Manage service applications” in central administration

2. Click on the “Business Data Connectivity Service”

3. Select your external content type and click “Set object permissions”

4. Give full permissions to All Users(windows)

5. Now you should be able to see your list

Configurating an external data type in SharePoint 2013 Part 1

This is a step by step configuration of an external data type in SharePoint Designer 2013. The first part explains step by step how to create an external data type. The second part shows how to configure the connection for fetching the data from an SQL Server database that is on another server.

So without further ado let’s get to the step by step description:

1. Open SharePoint Designer

2. Go to “External Content Types” and click the “External Content Type” button

3. You will get a window with the new external content type configuration

4. Change the name of the external content type by clicking on it

5. Go to sources configuration

6. Click “Add Connection”

7. For the source type choose SQL Server

8. Specify the database connection properties

9. Now you are ready to connect to the SQL database. Create a table in your database if you haven’t already.

10. Your source connection should now be visible in SharePoint Designer.

11. Now you need to generate the possible operations on the ProjectsTest table. Make a right click on the table an choose “Create All Operations”

12. On the “Operation Properties” screen click next

13. Check if the parameters are configured properly, and if you have no Errors in the “Errors and Warnings” screen.

14. On the “Filter Parameters” screen click next

15. You can now see the defined “External Content Type Operations”

16. Now we can create a new list based on the external content type that we’ve just created. Go to “List and Libraries” and on the ribbon click on “External List”.

17. Pick our external content type and click “OK”
18. Choose a name for out external list and click “OK”

19. If the SQL Server database is on the same server you should see the list.

If the SQL database is on another server, than you will see an “Access Denied by Business Data Connectivity” error. Handling this problem is covered in the next part of this tutorial.