Consuming an External OData service from an SharePoint 2013 App
Ever wondered how you can consume an external OData Service from a SharePoint 2013 App? Well, I did and it came at the cost of a fair bit of headache because I kept feeling I was missing one essential piece of the puzzle: An ODataConnectionSettingId.
Ok, let’s start at the beginning. I created an account with visualstudio.com and wanted to retrieve data from my online TFS project in SharePoint. A quick google search made it obvious that, since visualstudio.com is offering an OData endpoint, I could simply create a SharePoint 2013 App with an External ContentType that would be the basis for a so called External List. Pretty simple! But then I got stuck at trying to configure my App to use a SharePoint Secure Store Application for storing my (Basic Authentication) Credentials …
Let’s go through it step by step. Prequisites for following my steps in your environment are a fully functional local SharePoint 2013 development enviroment with a working Business Connectivity Service Application and a Secure Store Service Application. And of course you’ll need an account for visualstudio.com.
Step 1 – Create a Develoment Site
I like to create Host Named Site Collections using the PowerShell command below. Specifiying the template to be “DEV#0″ gives you an App Development optimized template.
New-SPSite “http://apptastic.is-sp2013.local” -HostHeaderWebApplication “http://dev” -Name “Developer Site” -Description “SharePoint App Developer Site” -OwnerAlias “is-sp2013\administrator” -language 1033 -Template “DEV#0″
Step 2 – Create a SharePoint App Project in VS2012
- Click Add > New Project > App for SharePoint 2013
- Enter Name and choose location > OK
Step 3 – Specify the App Settings
- Choose (and validate) our development site for debugging
- Select “SharePoint-hosted” as way to host your App
Step 4 – Add ContentType for External Datasource
- Right click on the project node in the solution explorer
- Click Add
- Select Content Types for External Data Sources
Step 5 – Specify the OData Source Settings
- Enter the OData Endpoint (in my case: https://tfsodata.visualstudio.com/DefaultCollection)
- Enter a name for your OData Source
Step 6 – Select the Data Entities
- Select OData Entities (I selected WorkItems)
- Ensure that the option to create an External List is checked
Step 7 – Create a Secure Store Application
Now we’ll leave our Visual Studio Project for a short moment to create a Secure Store Application where we’ll store the credentials for connecting to our External OData Source.
- Navigate to SharePoint 2013′s Central Administration > Application Management > Service Application > Secure Store Service
- In the Ribbon, click New
- Enter a Target Application ID which you can choose freely – Later it will be the unique identifier for BCS to locate your Application (with the credentials) in the Secure Store Service
- Enter your email address (as administrator)
- Select Group as Application Type
Step 8 – Specify Field Types (User Name and Password) for Secure Store Application
What now follows may differ, depending on the type of authentication your OData Service requires. In my case, visualstudio.com’s OData Endpoint can be configured for Basic Authentication. Hence I needed to change the Field Types from Windows User Name and Windows Password to User Name and Password. Also, you’re only defining the Field Types (and hence the type of supported authentication). You’re NOT setting the credentials at this stage yet!
Step 9 – Add Members that may access the Secure Store Application
On the next page you can enter Users and / or Groups that you want to grant access to the credentials stored in the Secure Store Application. In this case I entered “Everyone”. This means that everyone who uses the App that in turn will use this Secure Store Application will be permitted to make calls to the OData Service specified by the External ContentType.
Step 10 – Set Credentials for Secure Store Application
We’re almost done. The Secure Store Application is created. Now select it and click Set Permissions in the Ribbon of the Secure Store Service and enter the credentials to access the OData Service Endpoint.
Step 11 – Create a BCS Connection using PowerShell
This step is, as I mentioned before, one that I found is hardly documented. But it’s essential! Using PowerShell you need to create a so called ODataConnectionSettings that is then used by our External ContentType to establish a connection to our Secure Store Application. You can choose any name you’ll like. In the next step, however, we’ll need it to configure our External ContentType as it is the ODataConnectionSettingsId. The rest of the arguments for its parameter should be self-explanatory.
New-SPODataConnectionSetting -Name “TfsODataOnlineConnection” -ServiceContext “http://apptast
ic.is-sp2013.local” -ServiceAddressURL “https://tfsodata.visualstudio.com/DefaultCollection” -AuthenticationMode “Creden
tials” -SecureStoreTargetApplicationId “TfsODataOnline”
Step 12 – Update External ContentType properties in your VS2012 Project
Now we’re ready to get back to our Visual Studio project. Update the following fields:
ODataConnectionSettingsId Use the name you specified in the previous step when you create the ODataConnectionsSettings using Powershell
AuthenticationMode For Basic Authentication you should select “Credentials”
SsoProviderImpementation Here we need to enter the Fully Qualified Assembly Name of SharePoint’s Secure Store Service:
Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=220.127.116.11, Culture=neutral, PublicKeyToken=71e9bce111e9429c
Step 13 – Grant BCS Permissions for your App in the AppManifest
As a last step we need to update the AppManifest and make our App request permission to Read from SharePoint’s Business Connectivity Service (BCS).
Step 14 – Update the Start Page of the App so your External List is loaded immediately
Step 15 – Debug your App and Trust it
Hit F5, wait a short moment … Now check the box for the External Connection that the App is requesting permission to read from.
Step 16 – Sit back and enjoy …