This article describes the steps to setup and configure the SharePoint Online Integration with Azure SQL Server. It also covers the Azure SQL database subscription used in this article and the test database. There already does exist a Microsoft Office Online article related to this topic, however, the article does not describe the SharePoint Online process of setting up permissions in BCS in detail. 

Requirements

Below is the configuration of my test environment
1. Microsoft Office 365 SharePoint Online Plan 2
2. Microsoft Azure SQL Database with Basic feature.
3. Microsoft Azure Active Directory
4. SQL 2012 SP1 SQL Server Management Studio

Azure SQL database subscription

If you are interested in learning and or testing Azure SQL then consider using the basic database subscription as shown below.
 

This subscription gives you the option to go to your Azure portal and see your SQL database information as shown below.


When you view the SQL basic database created, you can see the server name. Now you can go to SQL 2012 SP1 Management studio (SSMS) on your local machine and put in the server name, change the authentication to 'SQL Server Authentication' and add in the Login name and password. 


Below is what the database looks like on SSMS. Notice the blue color database icon.

Importing the test database

Download the Adventure Works for Azure SQL Database from codeplex and extracted the files to C:\tools where you should see AdventureWorks and AdventureWorks_Federated folders and ExecuteSQL and ReadMe files. Below is a screenshot.


Copy the ExecuteSQL file in the AdventureWorks folder. 


Now run the following command.
CreateAdventureWorksForSQLAzure.cmd <serverGUID>.database.windows.net <sqllogin>@<serverGUID> <password>

For example, if the assigned SQL Azure server is named nssk8budo, the administrator user name is dchristian, and the fake password is Secret, the following would be typed:

 CreateAdventureWorksForSQLAzure.cmd nssk8budo.database.windows.net dchristian@nssk8budo Secret 

Depending on the performance of your computer and internet connection, creating this new database connection can take up to 15 minutes. Once the database is created you will get the below message. By default, this will create a Web Edition SQL Azure database. On September 10th 2014 Microsoft has announced the SQL Azure Web and Business database will be retired in September 2015.


Go back to your SSMS, refresh the database and expand it. Below is what you should see

Changing the database edition

Now when you go back to your Azure Portal and view the SQL databases, you will see the new AdventureWorks2012 database there, however, it is recognized as a Web (Retired) database as shown below. As mentioned above the Web edition is going to be retired hence I am going to take the extra steps below to change it Basic which is what my subscription is. The change process is simply one click!


Click on the AdventureWorks2012 database name and then click on Scale. Below is a screenshot


Under General, SERVICE TIERS select BASIC. I have also changed the MAX SIZE to 500 MB. Below is a screenshot


Now the database on Azure SQL is available and ready to make my Business Data Connectivity on SharePoint Online

Create a Business Connectivity Service

Before you go to your 365 SharePoint admin center, confirm the Azure SQL database name to get the URL for the Odata service. The URL is usually the database server name with https: hence in this example the URL is https://nssk8bdudo.database.windows.net 

Now go to your Office 365 SharePoint admin center and select bcs and then select Manage connections to online services.


Click on the Add button and add the below settings for your connection. The service address is the same URL we copied above. 

Create a Secure Store Service ID

In Office 365 SharePoint admin center go to secure store as shown below.


In the ribbon, click New which will take you to the page to specify the Target Application settings. 
In Target Application settings I have provided a unique ID. This ID cannot be changed once it has been created.


In the Credentials Fields, enter names that you want to use for the username and password. By default, Secure Store will use a Windows User Name and Windows Password. It is recommended that these values are accepted.  The Field Type shown below cannot be edited once created and saved.


In the Members section you can enter the names of the users or groups that will use this Target Application.


Click Ok. You will be returned to the Secure Store Service page. Now that you have created the Target Application you need to enter the credentials that the Secure Store uses to access the data from the Azure SQL database.
Select the ID that you just created and click on Credentials


If this is your first time using Azure then I recommend that you go back to Microsoft Azure Active Directory and confirm the username. Below is a screenshot of the credentials I am using. The account used in this example already has access to the Azure SQL database which is a requirement for the connectivity to work.

Create the External Content Type

From here onwards the steps are very similar to SharePoint 2013 on-premises farms.
Open SharePoint Designer 2013 from your local machine and access the SharePoint online site of your choice. 


Click on the External Content Types as highlighted above.  Click on the External Content Type option as shown below.


Below is what you will see 


Give the content type a name and then click on the External system link.


Click on the Add Connection button and the configuration settings shown below.



You will be prompted to added the Azure SQL database server, database name and the Secure Store Application ID we just created.
The server name is needs to be the Fully Qualified Server Name


In this case we have received a prompt requesting the credentials to access the external data source i.e. the Azure SQL database. 


It looks like the credentials typed in for the AzureSQL didn't work because we have received the below message.


Consider taking this connectivity issue to your advantage and revisit some of the steps mentioned above to confirm the settings and find a solution. Following are three items you can check to confirm your credentials.

1. Go back to your SharePoint admin center, secure store and add the correct credentials again to the AzureSQL. Confirm that you have typed in the username match the username in the Windows Azure Active Directory. In this example, the username is dchristian@christianfamily.biz

2. In SharePoint admin center, bcs, Manage connections to online servers, select your connection name and view the properties.

 Here's a potential problem! We previously created this connection using User's Identity. We need to change it to Use credentials stored in SharePoint.
The credential we created was also AzureSQL. 

3. Finally, in SharePoint admin center, bcs, click on Manage BDC Models and External Content Types.

Click on Set Metadata Store Permissions


Set metadata store permissions. One of the accounts must be given all permission levels.


Now, let's go back to SharePoint Designer and try making the data connection again. 
It is recommended that you close SharePoint Designer and then reopen it again to create the external content type otherwise you will receive a message stating that an External content with this name already exists.

Yay!! The connection is established, i.e. we can have successfully connected the Azure SQL database with Office 365 SharePoint.


Expand the Tables and selected any one of them that you need. In this example I have selected Employee. Right-click on Employee and selected one of the options.


While the connection is being made you may momentarily view the below message.


Once the process is completed you will receive the below operations properties message. Click Next


When you arrive at the Parameters Configuration options you may also see Errors and Warnings. In this case we have received two. To resolve them uncheck the Required box and check the Show in Picker box.


Below is a screenshot.


Click Next. You will be presented with the Filter Parameters Configuration operation. It is strongly recommended to add a filter of type 'Limit' for this operation type hence we are going to use the existing BusinessEntityID element to create a limit of 2000.
To set this limit, click on the 'Click to Add' link as shown below


And set the Filter Type to Limit and click OK.


Now type in 2000 or less as the Default Value and click Finish.


Click on the Save button to save the new external content type that you just created. Now, when you go back to Office 365 SharePoint admin center, bcs and click on 'Manage BDC Models and External Content Types' you will see your connection there

Click on the drop-down option for SQLAzureAdventureWorks and click on Set Permissions as shown below.


Search for All Users, select both All users (Windows) and All users (membership), add them and click OK.


Give both the groups Execute and Selectable in Clients permission levels and click OK.

Adding external list

Now that the data connection is made we are going to add the external list on our Office 365 SharePoint site. As shown below, click on Site Contents> add an app and then select External List


In Adding External List, add a name and then click on the icon highlighted below to find your ex


If all the prior settings and configurations have been done successfully then you should see 'AdventureWorks2012' listed as shown below. Select it and click OK.


Now click Create. 


You should now see your new external list. 


When you click on the app you might see the below image telling you that Office 365 SharePoint is attempting to connect to the Azure SQL database


 
Yay!! The data connection has been made!!!

Conclusion

Microsoft has made it super easy to setup the external content types between Office 365 SharePoint Online and Azure SQL databases. Once all the permissions are setup correctly and the configurations made, databases from Azure SQL can be added as external lists on SharePoint Online. 

Other reference articles

Getting Started with Windows Azure SQL Database Using the Windows Azure Management Portal
Azure SQL Database TechNet Wiki Articles Index
Make an External List from a SQL Azure table with Business Connectivity Services and Secure Store