In this SharePoint BCS tutorial, we will discuss everything about the business data connectivity service SharePoint 2013. We will check here sharepoint bcs step by step and the below topics:
- How to start a secure store service in SharePoint 2013
- How to create a new secure store service application in SharePoint 2013
- How to create an external content type in SharePoint 2013
- How to create an external list in SharePoint 2013
Let us check in detail about the business data connectivity service in SharePoint 2013.
Start secure store service in SharePoint 2013
Let us first start the secure store service in SharePoint 2013.
1. Open SharePoint 2013 Central Administration > System Settings > Manage Services on server.
2. In the Services List, Click Start next to the Business Data connectivity Service and Secure Store Service.
Generate New Key
1. In the Central Administration > Application Management > Manage service applications.
2. Click the Secure Store service application.
3. In the Key Management section, click Generate New Key.
4. On the Generate New Key page, type the Pass Phrase, and type the same string in the Confirm Pass Phrase and click OK. This passphrase is used to encrypt the Secure Store database.
Read How to make SharePoint site read only?
Create secure store service application in SharePoint 2013
Next, we will see how to create a secure store service application in SharePoint 2013.
1. In the SharePoint 2013 Central Administration > Application Management > Manage service applications > Secure Store Service.
2. In the Manage Target Application section, click on New
3. On creating a new page Enter your Target Application ID, Display Name, Contact E-mail, select your Target Application Type, Target Application Page URL and click Next.
4. Select Field Type: Windows User Name for Field Name: Windows User Name & Field Type: Windows Password for Field Name: Windows Password and click Next.
5. Now give the appropriate users for Target Application Administrators and Members and click OK.
6. The newly created Secure Store Application ID will be displayed on the page select the Secure Store Service Application ID and click “Set” in the Credentials Section.
7. To Set the Credentials Give the Windows Username, Windows Password, Confirm Your Entered Password and click OK
Note: In the Windows Username and Windows Password use the credentials of the user who has access to SQL Server Database you want to use.
This is how we can create and configure Secure Store Service application in SharePoint 2013.
Read How to check server health status check using PowerShell in SharePoint
Create External Content Type SharePoint 2013
Now, we will see how to create an external content type in SharePoint 2013.
1. Start the SharePoint Designer 2013, click on Open Site in SharePoint Designer, then give the URL of the site in the browse window.
2. Your site is opened in SP Designer in the Left Navigation click on External content Type
3. In the New External Content Type window give the desired Name, Display Name, Namespace, and External System for external Content Type. To select the External System click on the link “click here to discover external data sources.”
4. Initially no Data Source will be present in the Data Source Explorer, So click on Add Connection, In the External data Source Type Selection window select SQL Server.
5. For SQL Server Connection give the database server, database name, Name of the connection, select the connection mode as Windows Identity and give the correct secure store application id.
6. Give the credentials for BCS Secure Store Target Application ID.
7. Now the Data Source you created connection with will be displayed in the Data Source Explorer. We explore the database to get the Table or View or Routine on which we need to create operations for the External Content Type.
8. Here we perform only read operation, for that Right Click on the Table or View or Routine you want to create operation and from the list select New Read Item Operation.
9. For Read Item Set the Operation Properties: The Operation Name, DisplayName, Type will be populated automatically. The initial two values can be edited to their desired values. Click Next to proceed.
10. For Read Item Set the Input Parameters Configuration: Here you select a particular Data Source Element, and Check the Map to Identifier. Click Next to proceed.
11. For Read Item Set the Return Parameters Configuration: Here you again select a Data Source Element and make sure you Check all the elements you want to display, and in the Properties Check the Map to Identifier. Click Finish to proceed.
12. Once you have created the New Read Item Operation, It is advisable to go for Read List Operation as well. Right Click on the same Table or View you want to create operation and from the list select New Read List Operation.
13. For Read List Set the Operation Properties: The Operation Name, Display Name, Type will be populated automatically. The initial two values can be edited to their desired values. Click Next to proceed.
14. For Read List Set the Filter Parameters Configuration: Here we can Add Filter to the list if required any or else click Next to proceed.
15. For Read List Set the Return Parameters Configuration: Here you again select a Data Source Element and make sure you Check all the elements the way we did in Read Item Return Parameters Configuration Click Finish to proceed.
16. The above steps will create the External Content Type and then to give Permissions to the same Go to Central Administration > Application Management > Manage service applications > Click Business Data Connectivity Service,
17. Select the created External Content Type and click Set Object Permissions.
18. Click Add and give the Object Permission to All Users (windows), and select the Level of Permissions to be given to them below and click OK.
19. Select the created External Content Type and click Set Metadata Store Permissions.
20. Click Add and give the Metadata Store Permissions to All Users (windows), and select the Level of Permissions to be given to them below and click OK.
Read Cannot connect to the configuration database error in SharePoint
Create External List using SharePoint 2013 designer
Let us see now, how to create an external list using SharePoint designer 2013.
1. In SP Designer on the Left Navigation click on External Content Type, Select the Recently created External Content Type, and in the Ribbon click External List.
2. Give a Name and Description to your External List and click OK,
3. Now on the Left Navigation click on Lists and Libraries to see your External List in the External List section.
4. Open the External List in the browser. PEACE.
You may like the following SharePoint tutorials:
- How to configure incoming and outgoing emails in SharePoint Server
- How to sort folders in SharePoint document library
- How to create a custom master page using SharePoint designer 2013
- SharePoint Rest API Select, Filter, Sort and Paging Example
- Publishing and Synchronizing Excel tables to SharePoint
- SharePoint 2013 Web Services Tutorial and Examples
- The server is busy now. Try again later in SharePoint 2013/2016
In this tutorial, we learned how to work with sharepoint BCS step by step. How to set up business data connectivity service in sharepoint 2013.
After working for more than 15 years in Microsoft technologies like SharePoint, Office 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (9 times). I have also worked in companies like HP, TCS, KPIT, etc.