Second Life of a Hungarian SharePoint Geek

March 8, 2013

Accessing Office 365 REST services using LINQPad

LINQPad is a great tool, even for a SharePoint developer when working with the RESTful web services. However, it does not provide an authentication mechanism against Office 365, a major issue when there is no on-premise SharePoint or Project Server at your hand to develop and test your queries (as suggested by Andrew Lavinsky in this post), as illustrated by the figures below.

We add a new connection of type WCF Data Service 5.1 (OData 3) to LINQPad:

image

Specify the URI of the ListData.svc at our  O365 tenant, and Default (XML) as Formatter.

image

Then we receive the following error (you can try to specify username and password in the previous step, but it makes no difference):

Error: The remote server returned an error: (403) Forbidden.

image

I’ve found a workaround for this issue on the web, but for me it was so complex at the first sight (even though I later understood how it should work), so I decided to find another way, using my other favorite tool Fiddler.

Our “solution”: we will “cache” the authentication cookies from an Internet Explorer session, then inject the same cookies to the LINQPad sessions.

Start Fiddler, choose Roles / Customize Rules…, and edit the CustomRules.js file (don’t forget to create a backup!).

Before the OnBeforeRequest function add this code:

static var authCookies = "";
static var o365Site = "yourO365Site.sharepoint.com"; // modify this value!

At the beginning of the OnBeforeRequest function add this code:

if (oSession.HostnameIs(o365Site)) {
  var cookie = oSession.oRequest["Cookie"];
  if ((cookie == "") && (authCookies != "")) {
    //oSession.oRequest["Accept"] = "text/html, application/xhtml+xml, */*";
    oSession.oRequest["Cookie"] = authCookies;
  }
}

At the beginning of the OnBeforeResponse function add this code:

if (oSession.HostnameIs(o365Site)) {
  var cookie = oSession.oRequest["Cookie"];
  if (cookie != "") {
    authCookies = cookie;
  }
}

Done! Save the changes of CustomRules.js. Then (having Fiddler running and capturing network traffic!) start IE, navigate to your O365 site, and authenticate yourself when requested. Cookies are cached in Fiddler at this point.

Note: In my development environment I always enable Fiddler to decrypt HTTPS traffic. I have not tested this solution with decryption disabled, and have doubts, if it should work. If you test it, please, leave us a comment with the results.

image

In the next step (the same Fiddler instance is still running and capturing network traffic!), try to reconnect LINQPad to the same O365 site. Cookies are replayed by Fiddler, authentication in LINQPad should work this time.

image

To test the functionality, I submitted a simple query:

image

So far the good news. After “solving” the authentication issue, let’s see a further problem, and that is bound to the (missing) $metadata support of Microsoft’s OData implementation in SP 2013.

As you might have noticed, in the example above I used the “old-style”, SP 2010 compatible version of the REST API (_vti_bin/ListData.svc), and not the “new-school” format, including _api (like _api/web/), and that is no just accidentally.

Since LINQPad needs the $metadata to build up the object structure, it simply does not work without that:

In LINQPad:

Error: The remote server returned an error: (404) Not Found.

image

In Fiddler (HTTP status 404):

Cannot find resource for the request $metadata.

One of the workarounds may be (again with Fiddler) to use an existing beta installation of SP 2013, capture the response for the $metadata request to a file, then in the development environment send it as a response for the $metadata request from LINQPad automatically, but it is rather hacky, even for me. In my opinion it simply does not worth monkeying so much with that, we should rather learn and use the syntax of the OData requests.

Developers (including myself) who need to work against Project Online are luckier. Although the OData service of PS seems to be available only through the new _api interface, the $metadata support is still there in this case.

image

And a sample query:

image

Have fun using LINQPad against your O365 site and Project Online!

UPDATE: The same trick can be applied, when we would like to add a service reference to our Visual Studio project, referring to an O365 / Project Online site (just like described here for an on-premise PS). Although VS 2012 displays an authentication dialog for my – in this case German – O365 site (it is not the case with VS 2010),

image

it seems to have no effect (at least, not always?):

image

Note, that based on the error details it is likely not just a simple authentication issue, as VS would like to append /_vti_bin/ListData.svc to the service URL.

In this case you can use Fiddler again to replay the cookies and authenticate on behalf of VS:

image

UPDATE 2 (3/22/2013): I was wrong when I wrote that developers working against Project Online were much luckier. I’ve just realized, that although $metadata is really available for the ProjectData service, it is not supported for the ProjectServer service, that you should use to access entities like enterprise resources, calendars or custom fields (as illustrated by the next screenshot, requesting https://yourProjects.sharepoint.com/sites/pwa/_api/ProjectServer).

image

In LINQPad:

Error: The remote server returned an error: (404) Not Found.

image

In Fiddler (HTTP status 404):

Cannot find resource for the request $metadata.

Advertisements

5 Comments »

  1. Nice – let me try that out.

    Comment by Andrew Lavinsky — March 24, 2013 @ 16:09

  2. Works like a charm! Thanks!

    Comment by Nick Agren — April 16, 2013 @ 08:33

  3. I am not able to make this work and i am not able to add the service reference

    Comment by parthrawal — October 25, 2013 @ 07:33

  4. FYI – you do need the HTTPS decryption on to authenticate against Office 365.

    Comment by Andrew Lavinsky — December 31, 2013 @ 18:28

  5. I tried several times but it is not working, it is mandatory that i use VS 2010!?

    Comment by parthrawal — February 19, 2014 @ 13:47


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: