Office 365 (O365) Business Connectivity Services (BCS) Hands-on-Lab: Wiring up O365 BCS to a Windows Azure Service for Office 2010 and SharePoint Online Solutions

Business Connectivity Services (BCS) in Office 365 SharePoint Online (SPO) provides a significant opportunity to build out relevant productivity solutions by both power users and developers alike. Although not available at the initial release of Office 365 SPO, since November of 2011, BCS was rolled out to O365 tenancies world-wide. Now the same capability that on-premises SharePoint provides to bring line-of-business (LoB) data into the SharePoint context, can be tapped by those that are in the SPO environment. If you haven’t thought about using this capability, then this post will help you understand how you can get things wired up and hopefully inspire you to start tapping your LoB resources for SPO and build integrated Office 2010 solutions. Once the data is available in a SharePoint External List, you have options for its use in both no-code and code-based solutions.

The bottom line is that SPO BCS can connect up to any Windows Communication Foundation (WCF) or SOAP-based web service. Whereas the on-premises BCS can connect directly to a SQL database, .NET Assembly and WCF service, the SPO BCS version can only connect to the WCF/SOAP-based web services. The only other caveat for SPO BCS is that the user’s authenticated identity cannot pass directly through to the backend web service. The service itself must be username/password protected and the username/password will be set in the SPO Secure Store (SSS). Think of this as a “Trusted Subsystem Model” where there is a single service account that is used to authenticate to the backend system, not a specific user’s credentials/identity. Therefore, if the user must be known to the backend LoB system, you’ll need to pass some sort of a the user’s unique identifier (e.g. employee-id, GUID, etc.) as a parameter to the backend so it can do logging, filtering, permissions setting, etc. for the specific user. Other than this, the SPO BCS is wired up in the identical way as you would the on-premises version.

The hands-on-lab (HOL) steps you through the process of creating a WCF service that simply front-ends the Northwind database that is publically available via an OData feed at, https://services.odata.org/Northwind/Northwind.svc/. (https://www.odata.org has a number of data sources you could tap into – just follow the “ecosystem” and “producers” links.) The HOL includes the following:

Exercise 1:
In this exercise you’ll build your WCF service that fronts the Northwind OData service that contains a couple methods to retrieve data from the Customers table. You’ll then deploy your service to your Windows Azure account and test to make sure the endpoint is working. (Note: you do not need to host your service on Windows Azure. The steps in this exercise show how to do that, but you can host your SOAP-based web service on any server as long as it has a publicly accessible endpoint that SPO BCS can hit.) For the HOL you will not secure the web service with a username/password because to use security in the SPO SSS you would need to have the web service secured with a certificate that is issued by a Certificate Authority (CA) like VeriSign, Go Daddy, or others. BCS will not connect to a web service that is protected with a self-signed certificate – therefore, to keep this simple and free, the HOL web service is not secured. But for your production LoB web service you should purchase a certificate issued by your favorite CA.  

Exercise 2:
Here you will use your SPO administrator portal to set permission on the Metadata Store and then use SharePoint Designer 2010 to create the External Content Type from the web services and then create an External List from that.

Exercise 3:
In this final exercise everything comes together to show how to build out both no-code and code-based solutions now that the valued LoB data is available in SPO. First, you’ll see how to surface the data in web parts that filter the data directly in the URL. Secondly, you’ll add External Columns to a document library so that these columns can be surfaced in a Word document. This document can then become the default document for the document library so this exercise shows how to wire up this type of solution. Lastly, this exercise shows how to consume the External List data programmatically via a Word add-in. The main requirement here is that the add-in must prompt the user for their O365 credentials for the add-in to authenticate to O365 and access the External List. The HOL provides the guidance around this and sample code for both the starter solution and a completed solution.

Please download the HOL from here:

And don’t forget, once your External Lists are available in SPO, they are accessible via the web browser on your devices. For instance, these can come in really handy for your sales personnel in the field that may need to get quick directions to their customer’s site. They simply navigate to the SPO site and to the External List, drill into the customer they want, click on the Searchable Address link and then they can get their turn-by-turn directions – and a solution like this is done without any code once the External List is available!

Enjoy!

Update 7/12/2012: for a deeper presentation on an end-to-end solution using O365 SharePoint Online BCS, an Excel client add-in, a Windows Phone 7 app, a Windows SQL Azure database and a Windows Azure web service, see this post.