Building a mixed cloud model for SharePoint – Part 4

I got a couple of requests to walk through the possibility to work with Business Connectivity Services (BCS), in SharePoint 2016 with the mixed cloud model in my series. The requests do inspire me to keep digging into the model and write the article.

Hence, this article is not going to show you the architectural view, decision considerations along with pros and cons of the model we have built, as I said in the previous article.

If you have never heard about BCS, I highly suggest you to start by this link.

There are two databases in this article we are going to create. One is SharePoint BCS database and one is the external database we wish SharePoint to connect to. Two databases will be hosted on Azure SQL Database.

Keep track the series

Creating external database on Azure SQL Database

Let’s assume that there is a business database which you want to display in SharePoint. This business database contains business information such as sales revenue, product, sales performance or so on. You can create a new blank database on Azure SQL Database then design a model by yourself. To quickly get things up, I use a very common database provided by Microsoft for sample Business Intelligence deployment. Its name is AdventureWork, giving you a real-world like scenario. The cool thing is that with Azure SQL Database, Microsoft provides AdventureWork sample database so you don’t have to restore it. Use the following PowerShell (make sure you follow the series and the parameters remain the same value) to deploy an AdventureWork sample database on Azure SQL Database. Collation value does not neccesarily have to be the supported one in SharePoint.

Do the quick check by using Data Explorer and your SQL administrator account to verify if the newly created database has dummy data.

Creating BDC Service Application in SharePoint

Similar to other service application creation, below is the PowerShell script I used to create a new business connectivity service application whose database is hosted on Azure SQL Database. Make sure you have the blank database before this script

Also do the quick check with Data explorer.

Deploying Secure Store Service

Secure Store Service is used to store connection string and credential which has privilege in the external database. The reason I need to set up this service application is because I need to use SQL account to request access to the database.

then go to set up for a new target application.

Next, change the default field type to User Name and Password

You then need to set SQL credential which we will pass to the credential when working with external content type.

Capture the target application ID.

Working with external database 

There are some approaches to connecting and working with external database. To quick check, I use SharePoint Designer 2013. Note that there is no SharePoint Designer for SharePoint 2016. The 2013 version is still useful for SharePoint 2016. Open SharePoint Designer and connect to your target site collection.

Create a new external content type then add a new connection, choose SQL Server as the main data source type. When asked, give the correct field as follows.

Wait a few minutes to let SharePoint Designer to retrieve and connect to the Adventure Work database hosted on Azure SQL Database.

Perform further steps such as permission configuration, external list setting or so on. Below is the customer info the business connectivity service retrieves from Customer Table in the Adventure Work database.


This article does not provide you every step to make the work done. My apologies if this results your inconvenience. The article can be well read for those who work with SharePoint business connectivity service, connecting to external SQL Server database. Instead of on-premises system, the article shows you possibility to work with Azure SQL Database.

Go to Building a mixed cloud model for SharePoint – Part 5


  1. February 1, 2018 — 8:59 pm

    perfect level of detail, a very clear roadmap to SharePoint in a VPC, i really liked the series, thanks!
    i can’t wait for the next step in the series, dynamically instance SharePoint VMs depending on the workload 😛

    • Thuan Soldier
      February 2, 2018 — 1:46 am

      Dynamically workload based SharePoint VM could be done either one of the following ways:

      1. Deploy SharePoint on VM Scale set: I don’t know if this is supported. Nobody in the Internet even mentioned about this.
      2.Monitor performance via metric then automatically provision a new one using either Azure Function or Azure Automation Runbook.

      I will grab my time researching the two. But #2 is higher priority.

  2. Bruce Flitt
    February 26, 2018 — 3:24 am

    Like it

Leave a Reply

© 2018 The Soldier of Fortune.