SharePoint Blog

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″

Slide12

Step 2 – Create a SharePoint App Project in VS2012

  1. Click Add > New Project > App for SharePoint 2013
  2. Enter Name and choose location > OK

Slide13

Step 3 – Specify the App Settings

  1. Choose (and validate) our development site for debugging
  2. Select “SharePoint-hosted” as way to host your App

Slide14

Step 4 – Add ContentType for External Datasource

  1. Right click on the project node in the solution explorer
  2. Click Add
  3. Select Content Types for External Data Sources

Slide15

Step 5 – Specify the OData Source Settings

  1. Enter the OData Endpoint (in my case: https://tfsodata.visualstudio.com/DefaultCollection)
  2. Enter a name for your OData Source

Slide16

Step 6 – Select the Data Entities

  1. Select OData Entities (I selected WorkItems)
  2. Ensure that the option to create an External List is checked

Slide17

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.

  1. Navigate to SharePoint 2013′s Central Administration > Application Management > Service Application > Secure Store Service
  2. In the Ribbon, click New
  3. 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
  4. Enter your email address (as administrator)
  5. Select Group as Application Type

Slide18

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!

Slide19

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.

Slide20

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.

Slide21

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”

Slide22

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=15.0.0.0, 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).

Slide23

Step 14 – Update the Start Page of the App so your External List is loaded immediately

Slide24

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.

Slide25

Step 16 – Sit back and enjoy …

Slide26

  • Mukund lahane

    Hi Can we use this approach in SharePoint foundation 2013

  • Héctor Sánchez

    Thank you man I owe you one! I could successfully connect to the service thank to you.

    I would like to add 2 clarifications for your article for the other people, if you are in an Office 365 Server (like I am),you cannot use the PowerShell steps, but there is an available option to create the connection for the step 11 at Central Admin > BCS -> manage connections to online services in there you just set a Title, the Service Address and the Secure Store Target Application ID for the one that you created in the Step 7.

    The other clarification:

    I was working on visual studio 2013, and I had a hard time finding the Properties of the Step 12(it’s not well clarified where are this options), I had to edit first the .ECT files created by visual with an XML editor and add the options manually and I figured out that you had a typo on the SsoProviderImpementation (You are missing an “L”) should be SsoProviderImplementation, but when I ended it all I found the option in VS

    You have to actually open the ECT file and then open the properties (View, Properties Window) , because if you don’t open the ECT file you will get the File Properties.. instead of the DataSource Properties and now you can edit those options directly in Visual

    Thank you again man

    • sudhakar upadrasta

      Hi Hector..can you provide details on how to do step 12 in detail. Screenshots will help. Where did you add these properties . I am using VS 2013.

  • Paul

    Thank you for this blog post. I’m not using SharePoint Apps, but I was using Windows Authentication with an Odata service and BCS SharePoint Farm service. It was the New-SPODataConnectionSetting that finally got it all working together.

Copyright ©2012. All Rights Reserved.