Push & Pull data to SQL via SharePoint Online and BCS

PURPOSE
Ever wondered what sort of SQL data connections are possible in SharePoint Online?

Ever wondered of the limitations when trying this type of setup?

If so, read on...  This tutorial will go over all the steps to setup everything from scratch.  Every step is important, you miss one, it won't work.

WHAT YOU NEED

• SharePoint Online Plan 2 (you cannot achieve this with the basic version of SharePoint online as you require access to the Admin panel to setup Secure Store access permissions and BCS permissions.
• A windows azure account (which you can get a free 30 day trial as long as you're willing to provide your credit card.
• SharePoint Designer 2013 installed locally (required to create the final connection to the azure DB.

Please note, you'll make this a hell of a lot easier if all these accounts are setup using the same office logon.  Otherwise you'll be logging in and out like a madman.

IMPLEMENTATION

Step 1 - Create SQL Database: 

Log into Azures Management portal (https://manage.windowsazure.com/)

From here, Quick Create an SQL Database.  Azure will ask if you want to create a new SQL server, say yes.  In a minute or two your server and DB should be created.  Note down the server name for use later (The Fully Qualified Domain Name:  servername.database.windows.net) as well as the DB name.

Once the Database has been created click 'Design your SQL Database', log in, and create a table and all the columns you want

Now your DB and table have been created, it's time to connect them up to SharePoint.

Step 2 - Add BCS Permissions:

log into your SharePoint Online admin panel (https://[companyname]-admin.sharepoint.com/).  Click on the BCS Tab.

Click 'Manage BDC Models and External Content Types'

In here, 'click Set Metadata Store Permissions', add the office365 account you want to have access to manage the data connection to SQL.  I just used the account I was logged in as, and ticked all the boxes for access.

Step 3 - Create Secure Store:  

While in the SharePoint Admin Portal, click on the 'Secure Store' tab.  Here's where we create the access token to connect to the database.  Click 'New'. 

Add a unique name for the Target Application ID, this can be anything, but note it down as it will be required later.  Click OK.  You have just created the secure store token, but you still need to add the username and password to it.

Check the box and click Set Permissions.  In these text boxes, enter the username and password you created earlier to connect to the SQL Database.  Click Save.

Step 4 - Create External Content Type: 

Ppen up SharePoint Designer and connect to your SP Online site (https://companyid.sharepoint.com).  Don't forget the S in https..

Click 'External Content Types'.  Click 'New External Content Type' in the ribbon.

Click connect to external data sources.  Choose SQL server.  Type in the server name and database you recorded in step 1.  Choose the last radio button and enter your secure store application id from earlier.  and then enter your SP Online username and password.  You should now see the table and columns from the database.  Select the table and click Save.

Step 5 - Create External List: 

Open up your SharePoint Online site and click settings > add an app > external list.  Choose the external content type you just created.

Step 6 - Done!: 

You should now be able to add and remove items in the external list, which should automatically push those changes to your SQL Database.  Cool as ice right!!??

REFERENCES AND THANKS:

• http://office.microsoft.com/en-au/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx#_Toc350352764 
• http://michaelwasham.com/2013/03/16/office-365-access-denied-by-business-data-connectivity-connecting-to-sql-server-azure/