API access to SharePoint external lists

Suppose you want to programmatically access data in a SharePoint external list (e.g.  data from SQL Server). You may think that you can just use SharePoint’s ListData.svc. Think again…

I did some experimenting with an external list connected to SQL Server, with the following results:

SharePoint API Read Create/Update/Delete
ListData.svc (REST) Not supported Not supported
Web services (SOAP) Supported (GetListItems) Not supported
Client side object model Supported Supported

In short: if you want CRUD access to an external list, your SharePoint API choice is reduced to the client side object model.

I hope this is useful to others working with external lists, as there is little information about this topic on the internet. Let me know if you have different findings.

Remote authentication in SharePoint Online

Suppose you want to programmatically access SharePoint Online from Node.js, Ruby, Java, [insert your favorite platform here],…. SharePoint offers data access APIs, but how do you authenticate? SharePoint Online uses claims based authentication. Documentation about programmatic authentication is fairly limited and restricted to .NET solutions.

Fortunately there is this great post from Wictor Wilén, providing a working .NET example that connects to SharePoint Online. It requires the SharePoint Client Object Model and Windows Identity Foundation SDK and Runtime (WIF) to run.

OK, but can we use a platform other than .NET? In the end it’s all just data and http under the hood…

Fiddler to the rescue! By running the sample application and using Fiddler to look at the http traffic under the hood, we can find out how the authentication sequence works.

It’s actually quite simple:

image

Step 1: Send SAML Request to STS

The application POSTs an SAML Request Security Token message to the Microsoft Online Security Token Service (STS), located at the following address:

https://login.microsoftonline.com/extSTS.srf

The Request Security Token message should contain username, password of a Microsoft Live account and the url of your SPO site. Here is a template that you can use to build the XML message.

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
      xmlns:a="http://www.w3.org/2005/08/addressing" 
      xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
  <s:Header>
    <a:Action s:mustUnderstand="1">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</a:Action>
    <a:ReplyTo>
      <a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>
    </a:ReplyTo>
    <a:To s:mustUnderstand="1">https://login.microsoftonline.com/extSTS.srf</a:To>
    <o:Security s:mustUnderstand="1" 
       xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
      <o:UsernameToken>
        <o:Username>[username]</o:Username>
        <o:Password>[password]</o:Password>
      </o:UsernameToken>
    </o:Security>
  </s:Header>
  <s:Body>
    <t:RequestSecurityToken xmlns:t="http://schemas.xmlsoap.org/ws/2005/02/trust">
      <wsp:AppliesTo xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy">
        <a:EndpointReference>
          <a:Address>[endpoint]</a:Address>
        </a:EndpointReference>
      </wsp:AppliesTo>
      <t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey</t:KeyType>
      <t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue</t:RequestType>
      <t:TokenType>urn:oasis:names:tc:SAML:1.0:assertion</t:TokenType>
    </t:RequestSecurityToken>
  </s:Body>
</s:Envelope>

 

Step 2: Receive SAML Response

If the credentials are valid, the STS will respond with a Request Security Token Response message. Look for the BinarySecurityToken tag and you’ll find the Security Token:

.....
<wst:RequestedSecurityToken>
   <wsse:BinarySecurityToken Id="Compact0">t=EwBgAk6hB...
   </wsse:BinarySecurityToken>
</wst:RequestedSecurityToken>
.....

Extract the token value (including ‘t=’) for the next step.

Step 3: Send the Security Token to SharePoint Online

After you received the Security Token from STS, you need to POST the Security token to SPO:

http(s)://yourdomain.sharepoint.com/_forms/default.aspx?wa=wsignin1.0

E-accounts need to include a user-agent header. Your POST should look something like this:

POST http://yourdomain.sharepoint.com/_forms/default.aspx?wa=wsignin1.0 HTTP/1.1
Host: yourdomain.sharepoint.com
User-Agent: Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Win64; x64; Trident/5.0)
Content-Length: [calculate]

t=EwBgAk6hB....abbreviated

Step 4: Receive the authentication cookies

After SPO has validated the Security Token, it will return 2 authentication cookies in the HTTP header: FedAuth and rtFa.

.....
Set-Cookie: rtFa=0U1zw+TnLmLfDtzmppbu....abbreviated
Set-Cookie: FedAuth=77u/PD94bW.....abbreviated
.....

Extract the values of both cookies.

Step 5: Send requests including authentication cookies

For each subsequent request from your application to SPO, you must include the FedAuth and rtFa cookie in your request headers. Like this:

....
Cookie: FedAuth=77u/PD....LzwvU1A+; rtFA=0U1zw+TnL......AAAA==
....

This will satisfy SPO to process your request, whether it is a request for a page, a document, a web service or ListData service.

And that’s all there is to it!

I hope this is useful for others implementing SharePoint Online access. If you want more details, please check out my SharePoint client for Node.js which includes authentication code in JavaScript.

Thanks for reading.