The following scenario shows you how to create a no-code business solution in Microsoft Business Connectivity Services (BCS) by using the SQL Server AdventureWorks sample database. You learn how to:

  • Configure the accounts and groups that you need to securely access the external data source.
  • Configure the permission on the external data source, the external content type, and the external lists.
  • Create and configure an external content type.
  • Create an external list that makes the external data available to users.
  • Connect the external list to Microsoft Outlook to make the external data available when the user is offline.
What these procedures help you deploy

Business Connectivity Services is a centralized infrastructure in SharePoint Server and Office that enables you to integrate data that is not in SharePoint Server and Office into SharePoint Server and Office. BCS implementations take many different forms, including the on-premises form. These procedures show you how to install and configure BCS to integrate data from an on-premises SQL data source into a SharePoint Server external list and into Outlook. For the purposes of building out this scenario, we use the AdventureWorks sample SQL database. The solution looks as shown in the following diagram.

1. A user goes to an external list on a SharePoint site. The external list creates a request for data by using the user’s Windows credentials.
2. The request is sent to the BDC runtime in the SharePoint farm.
3. The BDC runtime accesses the external content type for the list (in the BDC Metadata Store) to see how to access the external system and which operations can be performed. By using either the user’s credentials or the credentials from the Secure Store (as defined in the external content type), the BDC runtime passes the request to a connector that can handle the request, in this case the SQL connector.
4. The SQL connector accesses the external data source and retrieves the data, and applies any formatting and filtering as specified in the external content type. The data is passed back through the request chain to the list where the user can interact with it.
5. The user wants to take this data on a portable computer in Outlook so the user can use the Connect to Outlook feature on the external list to take the data offline.
6. The Click Once installation runs and installs the required BDC model on the client. This lets the BDC Client-Side Runtime access the external data directly.
7. Outlook then connects to the external data by using the configuration in the BDC model and synchronizes it into an Outlook SharePoint external list, formatted as a contacts list.
8. The user can then interact with the contact data, and any changes that the user makes can be written back to the external data source either by an on-demand synch or by waiting six hours for the automated synchronization.

Before you start your installation of the on-premises Microsoft Business Connectivity Services (BCS) scenario, you must have these software and infrastructure requirements in place.

  • A fully functional SharePoint Server server farm with a Web Application and site collection
  • A properly configured Business Data Connectivity Services service application
  • A fully functioning instance of SQL Server 2008 with Service Pack 1 (SP1) and Cumulative Update 2 or SQL
  • Server 2008 R2
  • SharePoint Designer 2013
  • Outlook 2016 client
  • You have an account that has permissions to administer the Business Data Connectivity Service Application
  • You have an account that has permissions to administer the Secure Store Service service application
    Download the AdventureWorks sample database from Codeplex downloads for SQL Server. This database must be installed and functioning on your SQL Server
  • Create an Active Directory directory Service (AD DS) security group and add the users who will be using this BCS solution, for example create a group that is named AdventureWorksBCSUsers
Create database logins

Microsoft Business Connectivity Services (BCS) requires an account that it can use to access external data sources. The account must have the necessary permissions on the external data source to perform all the operations that your BCS solution might require. For ease of configuration and ongoing management, you can map a group of SharePoint Server users to a single shared account on the external data source.
In this procedure, you create SQL Server login and then assign that login to a user account on the AdventureWorks sample database. You will use Secure Store Service services to map a group of SharePoint Server users to the single shared account in a later procedure.

Create a SQL Server login

9. Start SQL Server Management Studio.
10. In Object Explorer, expand the <database server name>, expand Security, and then expand Logins.
11. Right-click Logins, and then click New Login
12. In the Login Name box, enter SharePointGroupAccount.
13. Select SQL Server authentication, and then enter and confirm a password.
14. In the Default database box, select AdventureWorks2008R2, and then click OK.

Create a SQL Server user on the AdventureWorks database

15. In the Object Explorer, expand Databases, expand AdventureWorks2008R2, expand Security, and then expand Users.
16. Right-click Users, and then click New User.
17. Under the Login Name, with the User name box pre-selected, in the first box, enter AdventureWorksUser
18. In the second box, click Browse, in the Select Login dialog, click Browse, select the SQL Server account, SharePointGroupAccount, and then click OK twice.
19. Under Database Role membership, select db_owner.
20. Click OK.
21. Close SQL Server Management Studio.

Set permissions on the BCS Metadata Store

The BCS Metadata Store holds external content types, external systems and BDC model definitions for the BCS Service Application. In this procedure you configure administrative permissions on the Metadata Store and everything that it will contain.

Set permissions on the Business Connectivity Services Metadata Store

22. Open the SharePoint Central Administration website with either a Farm administrator account or an account that has been delegated permissions to administer the Business Data Connectivity Service Applications.
23. On the Quick Launch, click Application Management.
24. On the Application Management page, under Service Applications, click Manage service applications.
25. In the list of services, select the row of the Business Data Connectivity Service Application that you created in Configure a Business Data Connectivity service application in SharePoint Server and then click Manage and then Set Metadata Store Permissions.
26. Enter the Farm Administrator account and any other delegate administrators if you have them and then click Add.
27. For each account or group that you added that is an administrator of the Business Data Connectivity Service Application, select the Edit, Execute, Selectable In Clients, and Set Permissions checkboxes.
28. Select the Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store. Doing so will overwrite existing permissions checkbox. For more information on setting permissions on the BDC Metadata Store, see Overview of Business Connectivity Services security tasks in SharePoint Server.
29. Click OK.

Configure permission on an external content type

To configure user access and permissions to the external content type:

Set up permissions to the external content type

30. Open the Central Administration page for your site.
31. On the Quick Launch, click Application Management.
32. On the Application Management page, under Service Applications, click Manage service applications.
33. In the list of services, click your Business Data Connectivity (BDC) Service.
34. Click AWCustomers.
35. On the ribbon, click Set Object Permissions.
36. Enter the user accounts to which you want to grant permissions, and then click Add. For this example, you would add the security group that was created in Prerequisites for deploying a Business Connectivity Services on-premises solution in SharePoint 2013AdventureWorksBCSUsers.
37. Select the user accounts that you just added, and then select Execute check boxe.
38. Select the Propagate permissions to all BDC Models, External Systems and External Content Types in the BDC Metadata Store check box to overwrite existing permissions.
39. Click OK.

The external content type is now available for use in SharePoint and Office products to the appropriate users.

Create an external list

An external list is a key building block for SharePoint solutions based on external data. You can read and write external data with the familiar experience of using a SharePoint list. An external list looks and behaves a lot like a native list, but there are some differences in behavior. For example, you can create views and use calculated values with formulas, but not attach files or track versions. For this exercise, you create the external list in the browser because that is a common approach. This article uses the procedures in Create an external list on Office.com. You must open that article and perform the steps there using the parameters given in the matching sections of this article.

Create an external list

40. Open Create an external list
41. Create an external list named AdventureWorksCustomers using the AWCustomers external content type.

Create a view of an external list

42. Create a view for the external list AdventureWorksCustomers. For this example, use ByRegion Data Source Filter.
43. Make it the default view, and select your own Sort, Filter, and Limit values.

Manage user permissions

Once you or an appropriate user has created the external list, it’s important to make sure that you set appropriate permissions for other users. If the subsite that contains the external list inherits permissions from its parent site, then you may inadvertently give permission to inappropriate users. In this example, permissions are given to the AdventureWorksBCSUsers group.

Manage user permissions to the external list

44. On the List tab, in the Settings group, click List Settings.
45. Under Permissions and Management, click Permissions for this list…
46. Apply permissions to the list as you have planned them.