External list is a type of list which can be created within SharePoint, using data from an external line of business. In order to implement the integration, we have to ensure that the two pieces given below are in place.
- Azure SQL DB – SQL DB has to be set up in Azure, which will serve as the external line of business database. The creation of Azure SQL DB has been detailed here.
- Secure Store target Application- Secure store in SharePoint will hold the credentials which will be used by SharePoint to connect to Azure SQL DB. The detailed steps to set up the target application in secure store can be found here.
Create Azure SQL DB External Content Type
Now, we will see how to create the external content type and publish it to SharePoint Online. In order to do this, spin up SharePoint Designer.
Click on Open Site to connect to our SharePoint Online site. Specify SharePoint Online URL and hit enter.
It will ask for authentication to connect to SharePoint Online. Specify the username, which is ideally the Email Id associated with the user account and click Next.
Enter the password and sign in.
Thus, we have connected to SharePoint Online, using SharePoint Designer.
Select external content types from the left pane.
Specify the name and display the name of the external content type in the Window.
Select click here to discover external data sources and define operations link. Select Add connection from the resulting Window. Select the DataSource as SQL Server and click OK.
In the database Server name, specify the Server name of Azure SQL DB and the database name. The Server name can be obtained from Azure SQL DB Server dashboard.
We will make use of Secure Store target Application to use the saved credentials to connect to Azure SQL DB. The Secure store target Application, which we have created here is SPO_BCS.
Thus, we have established the connection and we can see the tables within the “BI” database.
Now, we have to define the operations on the table. Right click on the table and select “Create All Operations” to define entire CRUD functionality.
Proceed by clicking Next.
We will have to map to a primary key of the SQL table. In the identifier dropdown, select the primary key of Azure SQL table.
If we want to get only a subset of the data, we can specify filter parameters, else complete the creation of the content type by clicking Finish.
Thus, we have created the connection to Azure SQL DB and have also defined the operations , which will be possible due to SharePoint Online.
Now, we will go ahead and save the content type to SharePoint Online.
If we go to SharePoint Online Admin Center, we can see the external content type, which we had recently created in BCS section.
Create External List from SharePoint Online
We will now create an external list, which is based on the content type that connects to Azure SQL DB.
Specify the external list name and the external content type, which we have created from SharePoint Designer.
Clicking on Create will create the external list, which connects to Azure SQL DB. However, we may get the error given below, if Firewall rule for the IP in the error message is not present for Azure SQL DB Server.
We will have to add IP range in the Azure SQL DB Server dashboard.
From Configure, add the range of IP that will have Firewall exemption.
Now, if we head over to the external list, we can see the data that are pulled from Azure SQL DB in SharePoint Online external list.
Let’s try adding a new value to SharePoint list.
If we check Azure SQL DB, the update has been made in SQL DB as well. Thus, it supports all the CRUD operations between SharePoint Online list and Azure SQL DB.
Summary
Thus, we saw how to create an external content type that connects SharePoint Online and Azure SQL DB, using business connectivity Services of SharePoint. We have also defined the entire two way CRUD operations between SharePoint and Azure SQL DB.
Join the conversation! Your thoughts help the community grow.