Prepare an Azure Database for SharePoint Online BCS Connection

While working on the demos for a resent presentation I wanted to research some steps on how to prepare an Azure database for Sharepoint Online BCS connection with SharePoint Online.  I found that there was no complete answer in one place so decided to make one… hence this post.  This post assumes you already have an Azure account and necessary rights to configure it.  It also assumes you do not have any DB’s in your Azure environment.  Also note, adding Azure DBs to your environment is not a free service and you will have costs added to your monthly invoice.

Create SQL Server in Azure

The first step is to create the logical server that will hold the DB you wish to create.  Login to https://portal.azure.com and complete the following steps:

1. Click on New
2. In the search window start typing SQL Server
3. Select SQL server (logical server)

You will be brought to a filter screen filled in with the information you entered.  Select SQL server (logical server).

You will be prompted with a confirmation screen that contains more information on the selection you have made.  Click Create to confirm

At the settings screen fill in the following information:

1. Server Name: Name you wish your server to be known as (don’t make too long as Azure appends “.database.windows.net” to it.
2. Server admin login: The SA account you wish to use
3. Password and Confirm Password: Password for the SA account
4. Subscription: This is where you select the subscription that the server should be applied to.  If you have multiple subscriptions connected to your login then you will have to select the one you wish the server is attached to.
5. Resource Group: Resource group you wish it to be grouped with.  Can create a new one or use an existing:
6. Location: The data center you wish the server to exist in.

Once your settings are in place, click Create

Create New SQL Database

1. Click on New
2. In the search window start typing SQL Database
3. Select SQL Database
4. Navigate through the confirmation screens as you had in creating the SQL server above.

At the settings screen fill in the following information:

1. Database Name: Name of your database
2. Subscription: This is where you select the subscription that the server should be applied to.  If you have multiple subscriptions connected to your login then you will have to select the one you wish the server is attached to.
3. Resource Group: Resource group you wish it to be grouped with. Select the resource group you placed the server in.
4. Select Source: Leave at Blank Database
5. Server: The server you wish the DB to run in.
6. Pricing Tier: This is the size and performance of your database.  This setting will greatly affect your monthly invoice.  Because this is just a minor dev database I selected the smallest (Basic).
7. Collation: Leave at default (SQL_Latin1_General_CP1_Cl_AS)

Once your settings are in place, click Create

Allow Firewall Access to SQL Server

Now that the DB is created you have to allow the systems using the DB access to the server.  This is accomplished from within the Azure Portal as well.

1. From the Quick Launch (left hand side of screen) click on browse and type in SQL Servers in the filter box.  Click on the SQL Servers entry.
2. Select the SQL Server that was just created.
3. The properties of the server will become available.

4.  Click on Firewall
5. The IP address of the PC you are on is conveniently provided to you which you can enter into the list.
6. Each time you enter an IP or an IP Range, click on “+ Add client IP“
7. To add the range for SharePoint online (needed for external lists) go to this link.  There is a number of ranges listed here so you may need to wait until you attempt an external list creation before you find the IP of your tenancy
8. Once all IPs and IP Ranges have been added.  Click Save.

Update Security of DB

Once you have added the IPs to the Azure DB firewall, you can now connect to the DB using SQL Management Studio.  You will need this in order to update the security of the DB.  When you launch SSMS, ensure you enter the FQDN of the server and login using the admin account you created earlier.

Create the Login for the BCS Account

Determine the account you wish to connect to the DB with using BCS from SharePoint.  This will be a SQL account you will create.  Right click on the Master database and select New Query.

Execute the following command:

Create LOGIN BCSUser WITH Password='*****';

Next right click on the new database you created and select New Query.  Run the following command (all together or one at a time it doesn’t matter).

Create USER BCSUser FROM LOGIN BCSUser;

exec sp_addrolemember N'db_datareader', N'BCSUser'
GO

exec sp_addrolemember N'db_datawriter', N'BCSUser'
GO

And that’s it.  You are now setup to connect your SharePoint Online BCS service to an Azure DB.  Stay tuned as I provide the steps to complete that in a future post.

 

Thanks for reading!!