The latest update to SharePoint online brought about a few new capabilities including Business Connectivity Services (BCS). With BCS it is now possible to connect SharePoint online to external data sources and line of business applications via web services or Windows Communication Foundation services. BCS enables full read and write capabilities of external data sources when granted proper permissions. As of January 1, 2012 it is not possible to connect SharePoint Online directly to SQL Server or SQL Azure.
Once you have established a BCS connection to external data you can display the data using built in BCS web parts or by accessing it with the client object model. It is not possible to use sandboxed solutions to interact with the BCS. See the MSDN article titled What’s New for BCS Development in SharePoint Online for additional details.
The first step in using BCS with SharePoint Online is creating a web service that will communicate to the data source or line of business application. This web service should implement at a minimum two methods; one for retrieving all of the data and a second method for returning a specific record based on a unique ID. These web methods should return a collection of entity classes or a single entity class containing the data. An entity class is a .NET class that has fields for holding data.
Below is the entity class definition I used in a web service to pull blog post information from my WordPress database.
As you can see, I am defining a few fields that will hold data that is returned from my custom web service. It is important that you do have a unique ID as part of your entity class. The unique ID is how BCS will be able to retrieve details about a single record in your data set.
Once the entity class is defined it is possible to create the two required web methods for retrieving data. Below is the method I created to retrieve all of the blog posts from my WordPress database. I am using an entity data model for connecting to the database. This allows me to use LINQ for querying the WordPress data tables. This method is returning an array of BlogPostsRecord objects which is my entity class.
The second method is used to return a single, specific record from the WordPress database.
Since SharePoint’s BCS does allow writing data back I could have created additional methods for submitting information back to the database. In this sample I decided to only read data from my WordPress database.
After the web service has been built and tested it can be deployed on a web server that is accessible from the Internet. Since this web service is hosted on the Internet it is important to implement some level of security. In my case I decided to simply use file level permissions and configure IIS to require NTLM authentication only. This means that anyone connecting to the web service will need to provide the proper Windows credentials to use the web service. You may also choose to use SSL to encrypt the connection to and from the web service.
To allow SharePoint Online to communicate with the web service it will need to know the credentials to use with the BCS connection. This is where the SharePoint Online Secure Store Service comes into play. To configure the Secure Store Service, login to the SharePoint Online administration portal and choose the Secure Store Service option. Choose new and complete the form by supplying a target application ID, display name, contact email address, application administrators and members.
The target application ID can be any string and will be used when defining the BCS data source. In the members section add every user that you wish to have rights to the credentials defined for this target application. In my case I want all of my tenant users to have access to my blog post list, therefore I need all of the users to have access to the secure store credentials. It is important to understand that adding a person to the members list does not allow them to actually view the username and password of the defined credentials. This only allows BCS or other applications to use the defined credentials on behalf of the users listed in the members section.
After the Secure Store Service application definition for the web service has been defined the credentials used to access the web service can be stored. In SharePoint Online’s Secure Store Service definition list click on the drop down next to our application definition and choose Set Credentials. This will allow you to enter in the Windows credentials that are required in order to access the web service located on the remote IIS server. Enter the credentials in the DomainUsername format.
With the web service created and the credentials defined in the SharePoint Online Secure Store Service it is now possible to create the BCS definition. I used SharePoint Designer to create an External Content Type which in turn creates the BCS definition and a default list view for my external data.
Open up SharePoint Designer and connect to the SharePoint Online site. Select External Content Types in the left navigation menu in SharePoint Designer. External content types are reusable metadata descriptions of connectivity information and data definitions plus the behaviors you want to apply to a certain category of external data. See the MSDN article titled What Are External Content Types for more details.
Initially your External Content Types list will be empty. In the top ribbon bar of SharePoint Designer choose External Content Type in the New grouping.
In the External Content Type Information section click on the links next to Name and Display name in order to set those values.
Click on the link next to External System to define the BCS connection to the remote web service. Click on the Add Connection button on the Operation Designer page. Select WCF Service from the Data Source Type dialog box and then click OK. The other types listed are only support on SharePoint on-premises and not for SharePoint Online.
Complete the WCF Connection form with the URL to your web service. Also set the WCF Service Authentication settings to connect with impersonated Windows identity and specify the Secure Store Service application ID. If you created a .NET web service your service metadata URL will be the service URL followed by ?WSDL. An example would be http://mydomain/demo.aspx?wsdl.
You may be prompted to provide credentials after hitting OK. If you are, enter in the Windows credentials for the web service. In a few moments the web service should be listed in the Data Source Explorer. Expand the Web Methods folder and you should see the two web methods offered by the web service. In my case I have GetPost, for retrieving a single record, and GetPosts for retrieving all of the records.
Right click on the web method that retrieves a single record and choose New Read Item Operation.
This will open up a Read Item operation wizard for defining the read operation for the SharePoint BCS. Review the information on the first page and then click on the Next button.
On the Input Parameters Configuration screen we need to configure the input parameter that we are passing to our web service in order to retrieve a single record. In this case I have a single parameter in my web service defined called postid that is my identifier. When this page of the wizard first comes up there is an error indicating that an identifier needs to be specified. Selecting the postid element and then checking the Map to Identifier option (under properties) resolves the issue. Click next to continue.
The final page of the wizard is displayed that defines the returned data from the web service. Once again we must map one of the fields as an identifier. My web service returns an ID field, so I will select that and click the Map to Identifier check box. Also ensure that the Identifier property for the ID field is set to postid. Click finish to complete the read item operation definition.
Now the operation to retrieve all of the records needs to be created. On the Operation Designer screen where the web methods are displayed, right click on the method that returns all of the records, in my case GetPosts, and choose New Read List Operation. Review the first page of the wizard and then click next. Since this web method doesn’t have any input parameters we can skip the second page of the wizard; click next.
On the Return Parameter Configure page of the wizard we need to once again define the field that holds the unique identifier for the returned records. My web service returns the unique identifier in the ID field, so I will choose that under the Data Source Elements section and then click on Map to Identifier property.
If I decide to use this external data source as the source for a picker control I will need to specify what field will provide the data for the picker control. In my case I would want to use the blog post title. I selected Post_Title from the Data Source Elements section and then I checked the Show In Picker property.
Once all of the property settings have been configured for your web service, click on the finish button. Click on the SharePoint Designer’s save button to make sure all of our settings are saved out to SharePoint Online.
At this time, the BCS connection has been created to our remote web service. To use this connection I decided to allow SharePoint designer to create default list and forms for my data. Click Create Lists & Form on the ribbon bar and then provide a name for the new list in the dialog box that pops up.
After the list and form is created you can connect to your SharePoint Online site and view the new external list. You can sort and filter the new list just like you can with any other SharePoint list. You can also create new views of the external list.
With SharePoint Online it is possible to access and use remote data or information stored in line of business applications. A combination of SharePoint Online’s Secure Store Service and Business Connectivity Services makes this possible. With a little bit of up front work you can provide your users with a wealth of information at their fingertips in one single location.