Benefits of an ITAR Compliant Environment
SharePoint Online to Azure SQL Database: Part I
Table of Contents
1.Introduction
2.Native vs External Lists
3.Azure SQL Database Setup
4.SharePoint Online Setup
5.Secure Store
6.External Content Type
7.Summary
1.Introduction
When we first set out to setup SharePoint Online to integrate with an Azure SQL Database with Business Connectivity Services (BCS), I thought it was going to be very quick and easy. It probably will be from now on, but we had to work our way through some challenges. We did our research and felt pretty confident that it was going to be very easy. To be honest, the steps to setup BCS in SharePoint Online aren’t exactly rocket science. But there are a few gotchas and if you’re not careful, they will get you. In this article we will walk through the steps to setup SharePoint Online to integrate with Azure SQL Database via BCS.
2.Native vs External Lists
There are two types of lists in SharePoint: native and external. Both come with advantages and disadvantages. As with most things that are Out-of-the-box (OOTB) in SharePoint, native lists are setup for rapid development and convenience. There are many content types to create lists such as Announcements, Calendars, and Tasks, which are very convenient and feature rich. This comes with the cost of not being the most scalable or portable. Sites with large lists can have a noticeable impact on performance. What most people don’t realize is that all SharePoint list data is kept in a single table in the SharePoint database. So, it doesn’t matter how many different lists you create, all the items in those list are kept in the same SQL Server database table. You can read more about it here:
This isn’t a big deal in SharePoint sites that don’t have a lot of list data. But in cases where there is, and particularly with a lot of users and queries with multiple joins, operations can get slow. Queries against lists that involve multiple joins on lookup columns to other lists can be especially slow.
External lists are very scalable and can accommodate large amounts of data. Data in an external list is stored in another data source such as SQL Server and the data can be normalized in a manner that strikes a balance between efficient storage and performant querying. They have many of the same features as a native list, but they are more complex to setup and require support from developers and administrators who have more technical knowledge than the average user. You can view the lengthy differences between native lists and external lists here:
I think biggest most notable difference with external lists is the inability to associate workflows with them. Let’s face it, even the simplest sites and apps in SharePoint often call for workflows so that is a big difference. But, don’t be turned off by external lists, workflows and Microsoft flow can work with external lists, they just can’t be directly associated with them. Instead, you will need to create the workflow on another list or create a Site workflow. Same applies for Microsoft Flow. We’ll cover that in another part of this blog series.
3.Azure SQL Database Setup
We need to start with setting up an Azure SQL Database. Microsoft Azure SQL Database is a cloud relational database service. For something that is so quick and relatively easy to setup, it is very feature rich and cost effective. A deep dive on Azure SQL Database is outside the scope of this article, but if you are new to this service, I highly recommend learning more about it here:
First, go to the Azure Portal and login at If you don’t already have a subscription, then you will need to set one up. If you’re not a Global Admin, then you will need to coordinate with someone who can to setup your subscription. Once you’ve logged with your subscription, click on SQL databases and then click “Add” on the right side to create a new SQL database as seen below:
Figure 1. Add new SQL database
Enter “CloudDemoDB” for the Database name field. Keep in mind, this is not the server name. Select the subscription you want the new database to fall under and if you haven’t already created a resource group, now is the time to do it. If you have a resource group you want to use, then select it:
Figure 2. Subscription and Resource group options
Next, click to configure the server as seen below:
Figure 3. Configure Server
Click “Create a new server”:
Figure 4. Create new server
Name the server whatever you want. In this example, I am going to call it “cloudrnd”. Here is a gotcha that caught me. Later, you will need this server name to setup the Secure Store and to create the External Content Type. First, I mistakenly tried to use the database name and then the Fully Qualified Domain Name (FQDN) of the server when entering the credentials for the database connection in later steps. That is not what you need, you will need exactly what you type in the “Server name” field shown below:
Figure 5. New Server
Chances are that “cloudrnd” will not be available. You will need create a name that is unique for your server in the Server name field and remember it later. When you enter credentials for this server, it will be in the [Server admin login]@[server name] format. We are going to use the Server admin login, which is not what we would call a best practice. When you are configuring this for a customer’s production environment, you will want to use a service account in the secure store. I’ll touch on this again later. For this example, use the Server admin login, which will be sqladmin@cloudrnd. Be sure to use the server name you created and remember the password for the server admin login as well.
For the selected source, I am going to select the Sample (AdventureWorksLT) database.
Figure 6. Select database template
This is a sample database that includes various database objects and records for us to work with in this demonstration. If you want to create your own database, feel free. Be sure to select “Not Now” for elastic pool and Basic for the Pricing tier and then click “Apply”. Selecting these options will keep the new database from burning up a lot of Azure credits and keep it very low cost.
Figure 7. Select pricing tier
You should be all set now, click “Create” as seen below:
Figure 8. Create Database
It might take a minute, but once Azure is done creating your database it will appear on the left side under SQL databases:
Figure 9. New CloudDemoDB
Next, we need to configure the server firewall settings to allow connections. There are two types of firewall rules that can be applied to Azure SQL Databases:
- Server-level:Rules that enable clients to access all databases within the same logical server
- Database-level: Rules that enable clients to access specific databases in the same logical server
When a client from the internet tries to connect to a database in Azure, the firewall checks the database-level firewall rules. If the IP address trying to connect doesn’t fall within of any of the ranges of the rules specified, then the server-level firewall rules are checked. If the IP address is in one of the ranges specified, then the connection is allowed. If the IP address doesn’t fall within the ranges specified in either the database-level or server-level rules, the connection is denied. To learn more about firewall rules for Azure SQL Databases I recommend learning more here:
For this example, we are going to setup server-level firewall rules. Go to the overview of the database:
Figure 10. Set server firewall
At the top, you will see your own IP address, which you will want to add:
Figure 11. Add new client IP
Once you’ve entered a name and your IP address in the start and end fields click “Add client IP” and then click “Save”. SharePoint Online will need to be added as well. Realistically, we would not want to do this manually for every server and IP addresses in a real world production scenario. There is a really good blog I found, which shows how to do this with Powershell. I recommend reading more about how to add firewall rules for SharePoint Online BCS here:
For now, add another rule for the IP range 0.0.0.0 to 255.255.255.255. This is another setting we would not call a best practice. This last rule is to allow SharePoint Online BCS to connect and to just get us up and running for demonstration purposes.
Now that our database has been created and the firewall rules are in place, open it up with SQL Server Management Studio (SSMS) and login with the Server admin credentials to test them:
Figure 12. Login with SSMS
Once you’re in, take a quick look around and familiarize yourself with the database. As I mentioned before this database template already has several objects and records in the tables. Now we ready to move onto setting up SharePoint.
4.SharePoint Online Setup
To create an external list in SharePoint we need two pieces: an external data source and a Secure Store Application, which will hold the credentials to access the external data source. We’ve already created Azure SQL Database. Now, we’ll start configuring SharePoint so we can create external content types. Note that you need to be a SharePoint administrator on your tenant to setup the Secure Store.
5.Secure Store
Let’s start by logging into Once you’ve logged in click on “Admin” under apps:
Figure 13. Office 365 Admin Center button
Next, expand “Admin centers” in the left side navigation menu, and click on “SharePoint”:
Figure 14. SharePoint Administration Link
In the side navigation for the SharePoint admin center, click on “secure store”:
Figure 15. Secure Store Link
In the ribbon, click “New” to create a new secure store target application:
Figure 16. Create new secure store target application
Next, you will be on a page where you create the secure store application:
Figure 17. New Secure Store Application Interface
Enter the following:
Target Application Settings
- Target Application ID: CloudDemoSqlDb
- Display Name: Cloud Demo Sql Db
- Contact E-mail: [your email address]
- Target Application Type: Group Restricted
Credential Fields
Don’t change anything for the credential fields.
Target Application Administrators
Add yourself and anyone else you want to administrate the target application.
Member
Click the address book and search for and add everyone
Once you’re done, click “OK”.
Your new secure store target application will appear in the list of Target Applications IDs, select the new target application ID and click “Set” to set the credentials for it:
Figure 18. Set Target Application ID credentials
This is where you will need the server admin login for the Azure SQL Database in the [Server admin login]@[server name] format, which will be sqladmin@cloudrnd. If you used a different name for the server admin login, then you will need to enter that instead, just make sure it’s in the proper format. Enter the password in the password fields and the click “OK”:
Figure 19. Enter Target Application ID credentials
6.External Content Type
External Content Types (ECT) are the reusable metadata descriptors of connectivity information and the core building blocks of Business Connectivity Services (BCS). ECT provides data definitions and behaviors for external data. Once created, ECT provides metadata that can then be managed and reused to interact with external data. Some of the benefits of ECT includes:
- Reusability
- Encapsulation of external systems
- Provide built-in Office and SharePoint behaviors
- Provide secure access
- Simplify maintenance
- Enables search of external data
- Enables working offline
Taken from:
Basically, ECT allows us to create SharePoint lists out of data from external systems. This provides a way to continue to interact with data in SharePoint, but get around some of the scalability and performance issues with large data.
Create External Content Type
It’s time to open your site with SharePoint Designer (SPD) 2013. Open SPD 2013, then click “Open Site”:
Figure 20. SPD 2013 Open Site
Enter your site’s URL in the Site name field and click “Open”:
Figure 21. Enter Site URL in the Site name field
Here is another gotcha that might get you. If you attempt to open your site and get a message telling you that there is a problem with your account, then you probably need an App Password. If your admin has set up multi-factor authentication (MFA) for your organization, then you will need an App Password. How do you know if your admin has set up MFA? If you log in with a username and password and then get a text message sent to your phone to verify your login, MFA has been established.
To create an App Password, follow these steps:
- Sign into Office 365
- Choose Settings
- Choose Security & Privacy > Additional security verification
- Choose Update my phone numbers used for account security
- At the top of the page, choose App Passwords
- Choose create to get an app password, and then click “Next”
- Choose copy password to clipboard
- Return to SPD 2013
Taken from:
Now, in SPD 2013 click on Account in the left side navigation
Figure 22. SPD 2013 Account Settings
If there is already an account signed in, click “Sign out”:
Figure 23. SPD 2013 Account Sign out
Next, click sign in:
Figure 24. SPD 2013 Account Sign In
Enter your email address for your Office 365 account and click “Next”:
Figure 25. Office 365 Sign in
Now enter your App Password for the password:
Figure 26. Enter App Password
I have seen where SPD 2013 will tell you that your password or User ID is incorrect if you enter your App Password here. If that happens, enter your Office 365 account password, then it will make you enter your credentials again, on the second try, enter the App Password.
Most other problems can be cleared up by clearing your cache and turning off Cache site data across SharePoint Designer sessions in your Application Options in SPD 2013. Once you’ve cleared your cache and updated your settings, close and reopen SPD 2013. You can read in detail how to do that here:
Once you’ve opened your site in SPD 2013, click on External Content Types in the left side navigation:
Figure 27. External Content Types menu
In the ribbon at the top of the page click “External Content Type”:
Figure 28. New External Content Type
Next, click “Click here to discover external data sources and define operations”:
Figure 29. Discover external data sources
Now, click “Add Connection”:
Figure 30. Add connection
A dialog will appear to select the data source type. Select “SQL Server” and click “OK”:
Figure 31. Data Source Type
Another dialog will appear to setup the SQL Server Connection. Enter the following:
Figure 32. SQL Server Connection Settings
For the Database Server field, you will need to enter the name for your server. Click “OK”. You will have a windows authentication dialog appear. Make sure you select SQL Authentication and enter the credentials you created for the Azure SQL Server server admin login in the [Server admin login]@[server name] format. Give a moment to authenticate and your new ECT will appear in the Data Source Explorer:
Figure 33. Data Source Explorer
Expand the Tables node, and right click on the Customer table, and click “Create All Operations”:
Figure 34. Create All Operations
Figure 35. External Content Type All Operations
Click “Finish” and then the External Content Operations will appear to the right:
Figure 36. SPD 2013 External Content Type Operations
Now, save the CloudDemoDB external content type. After we save the CloudDemoDB external content type, we will need to configure the BCS Metadata Store Permissions.
BCS Metadata Store Permissions
In the SharePoint admin center, choose “bcs” in the side navigation menu:
Figure 37. SharePoint admin center bcs
Next, click “Manage BDC Models and External Content Types”:
Figure 38. Manage BDC Models and External Content Types
Here you will select the new external content type you created in and click “Set Object Permissions”: