Create Back End Azure SQL DB For SharePoint Online Business Connectivity Services

Microsoft Azure SQL DB is a cloud managed database which can be used for app development as well as online storage. It is offered as a Cloud database as a Service. This makes maintaining apps online much easier. You can create SQL database from Azure portal section “SQL Databases”. Select create a SQL database to get started with the creation of the database.

This will open up the page, where we can specify the database settings like the name of the DB, Azure Subscription, DB Size etc.

Specify the login name and the password, which will be used to access the database.

Click the finish tick mark, which will start the provisioning of Azure SQL database.

After a few minutes, the database will be up and running.

If we try to open Azure SQL database from one of Azure machines, we are likely to get the error shown below, if the IP is not in the allowed IP list.

In order to resolve this, go to the Configure section of the database which was created and add the IP of the Server to the allowed IP address section.

Connect to Azure SQL DB

In order to connect to Azure SQL DB, let’s connect to one of Azure virtual machines and from there, open up SQL Server management Studio. In the Server name's text box, specify the fully qualified name of the database, which can be found in the Azure portal.

Use SQL Server authentication, specify the login name and password, which was entered during the provisioning of Azure SQL Server.

This will create a connection to the recently created Azure SQL database, as shown below. It has listed out the database, which we had created by the name BI.

Populate Data

To get started with the business connectivity Services integration with SharePoint Online, let's create a new table, which will have a two way CRUD update relation with SharePoint.

We have created a table, which shows the Car Sales Statistics and have named it Q1CarSales.

Populate some data to the table, so that it can be pulled from SharePoint Online, using business connectivity Services.

The back end is ready with Azure SQL DB. In the upcoming article, we will see how to implement the business connectivity Services connection from SharePoint Online and pull the data into SharePoint list, as shown below.


Thus, we have created the back end Azure SQL DB for implementing SharePoint Online business connectivity Services. Stay tuned for the next article in the series.