Tag Archives: SharePoint Online

SharePoint Online remote authentication (and Doc upload)

The SharePoint REST API is touted as being the tool to provide inter-platform integration with SharePoint Online. However, outside of .NET the authentication piece is not so straightforward. App authentication solves this issue for registered apps but I want to show how remote user authentication can be achieved, regardless of platform.

The goal of this post is to provide examples of the HTTP requests which need to be made in order to authenticate SharePoint Online. It then provides an example of using the same technique to upload a document and update metadata just to prove it all works ūüôā

The type of applications where this kind of approach may be necessary include: a Java application, a PHP application, or JavaScript application where there is otherwise no SharePoint Online authentication context and the decision has been made (for whatever reason) that user authentication is most appropriate (as opposed to app authentication).

Edit: This approach will not work in a JavaScript environment due to cross-domain restrictions enforced by browsers (unless of course you are on the same domain, in which case you don’t need to worry about any of this anyway). The ADAL.js library is available for the cross-domain JS scenario. I have posted an example here: http://paulryan.com.au/2015/unified-api-adal/

I wrote about using the SharePoint REST API here (and background here, and here).

I will be providing examples of the requests using the ‘Advanced REST Client’ Google Chrome extension.

Authenticate

The authentication piece comes in a few steps:

  • Get the security token
  • Get the access token
  • Get the request digest

Get the security token

First we must provide a username and password of a user with Contribute access to the Roster Data library and the URL at which we want access to the SharePoint Online Security Token Service.

This is done by POSTing the following XML as the request body to:
https://login.microsoftonline.com/extSTS.srf

<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>

Requesting the security token
Requesting the security token

The response from the request includes the security token needed to get the access token. It is the value which has been stricken out in orange in the image below.

Response including the security token
Response including the security token

Get the access token

Once the security token has been retrieved it must be used to fetch the access token. The can be done by POSTing to the following URL with the security token as the request body:
https://yourdomain.sharepoint.com/_forms/default.aspx?wa=wsignin1.0

Request to fetch the access token, passing the security token
Request to fetch the access token, passing the security token

The response from this request includes couple of cookies which must be passed as headers with all future requests. They are marked with the ‚ÄėSet-Cookie‚Äô header. We need the ones beginning with rtFa= and FedAuth=. They can be seen the below image of the response headers.

Response includes the access token cookies
Response includes the access token cookies

Get the request digest

The request digest is a .NET security feature that ensures any update requests are coming from a single session. It must also be included with any POST requests.

The request digest is fetched by POSTing to: https://yourdomain.sharepoint.com/_api/contextinfo
The access token cookies must be included as Cookie headers with the request as shown in the image below.

Request to fetch the request digest, passing access tokens
Request to fetch the request digest, passing access tokens

The response from the request will include the request digest in the XML response as in the image below. The entire contents of the FormDigestValue tag will required, including the date time portion and timezone offset (-0000).

Response containing the request digest value
Response containing the request digest value

Upload a document with metadata

Upload the document

Now that we have all the authentication headers we can make update calls into SharePoint Online as the user whose credentials we originally supplied when fetching the security token.

In order to upload a document perform the following POST request:
https://yourdomain.sharepoint.com/subweb/_api/web/lists/getbytitle(‚Äėlist name‚Äô)
/rootfolder/files/add(url='filename.csv',overwrite=true)

A number of headers must be send with the request including the access token cookies, the request digest (X-RequestDigest) and the accept header as shown in the image below. The body of the request must contain the content of the document being uploaded.

Request to upload a document to SharePoint Online
Request to upload a document to SharePoint Online

The response of this request contains some minimal metadata about the file and can be safely ignored. However, for completeness here it is.

Response JSON from the document upload request
Response JSON from the document upload request

The unique ID property could be used to fetch the document in order to perform metadata updates rather than URL as done in the following example.

Update document metadata

The final step which needs to take place is update the document in SharePoint with the relevant metadata.

This can be done with yet another POST request. This time to the following URI:
https://yourdomain.sharepoint.com/subweb/_api/web/lists/getbytitle(‚ÄėlistTitle')
/rootfolder/files/getbyurl(url='serverRelFileUrl')/listitemallfields/validateupdatelistitem

All the headers sent with the previous request must be sent with this request as well. The request body is a JSON object which defines the metadata fields to be updated. The fieldname and fieldValue properties must be updated as required. Note that the fieldname property must be equal to the field internal name not the field display name. An example of this is in the image below.

Request to set metadata on a document in SharePoint
Request to set metadata on a document in SharePoint

The response from this request provides success notification for each individual field update as shown below.

Response from the document metadata update request
Response from the document metadata update request

So, this should now be enough to write an application in any server-side language which supports web requests and work against SharePoint Online. I’d love to see some implementations of this, please comment if you’ve done it.

I’d like to acknowledge the following posts as they were invaluable references:

 

Setting Default Page Layout, Available Page Layouts, and Available Web Templates via CSOM (JSOM)

The publishing infrastructure provides a site with many features including page layouts. A default page layout, a restricted set of available page layouts, and a restricted set of web templates can be set. The page in the UI looks like this:

config_pageLayouts

In order to perform these configurations programmatically, methods are provided as part of the the server side object model via the PublishingWeb object. However setting these properties via CSOM is less obvious. The equivalent methods are not available (at least not at the time of writing. It is worth noting that the CSOM model gets extended as part of CUs as well as in SPO).
The property information is stored solely as web properties. I have verified this by reflecting the Microsoft.SharePoint.Publishing assembly, and ensured that the server side object model methods perform no other action than this.

The following JSOM code demonstrates how to set the web properties with valid XML values.

The methods can be called like this:

// The page layout is defined by its filename
var filename = "NewsArticlePage.aspx";
setDefaultPageLayout(filename, onSuccess, onFailure);


// The page layouts are defined by an array of filenames
var filenames = ["NewsArticlePage.aspx"];
setAvailablePageLayouts(filenames, onSuccess, onFailure);

// The web templates are defined by their long name,
//the guid is that of the feature which deploys the web template
var webTemplates = ["{BDACFFF5-05DF-4446-9907-B4C39F15F1D7}#WT_VanitySite"];
setAvailableWebTemplates(webTemplates, onSuccess, onFailure);

 Note Рthese samples reference both the jQuery and underscore libraries.

Deploying/provisioning difficult web parts (like XsltListViewWebPart)

Deploying the XsltListViewWebPart to a page as part of a packaged solution can be a challenge (as well as some other web parts). When these web parts are exported they refer to the underlying list or library via its GUID rather than by URL or name. This means that that the web part cannot be imported using this same XML to another site as it will fail to find a list with that GUID.

As Microsoft’s guidance is now to avoid using the server-side object model, the previously most common way to resolve this issue¬†is no longer available. Web parts could be provisioned by JSOM code¬†on a page however that really¬†feels messy and there some declarative alternatives.

SharePoint-2013-export-webpart

Replacing the AllUsersWebPart element with the View element

Specifically for provisioning an XsltListViewWebPart, rather than using the AllUsersWebPart element to provision a web part to page you can use a View element instead. The view element allows you to specific a list via URL, which when omitting a list reference in the child web part element, will be used to identify the list or library. This is my recommended approach. See here for the View element schema definition.

 

Using the listId token for document libraries

There is a token that can be used to replace the library GUIDs in the web part XML. I say library specifically as I can’t seem to get this approach to work with lists. The properties that need to be replaced with token are as follows (in this example we are referencing the ‘Documents’ library):

<property name="ListName" type="string">{$ListId:Documents;}</property>

<property name="ListId" type="System.Guid, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">$ListId:Documents;</property>

An example of the full XML using this approach can be found here.

This method can be used for other web parts as well such as specifying a specific list when deploying a ContentQueryWebPart.

Using BinarySerializedWebPart for lists or libaries

The other option is a very static and rather awkward approach to solving this issue – but at least it works. The BinarySerializedWebPart allows any web part to be imported in a binary format and provides a mechanism for mapping GUID to URL. The major downside is that if you want to make a minor change it will require you to re-create the binary representation of the web part.

In order to find this XML you will need to configure a web part as desired on a site that has never had publishing features enabled and then import the site as a site template (the publishing features disable the export site template functionality and although you can get around this you can get very strange side-effects, so I recommend avoiding doing so. e.g. I once found that all my Script Editor web parts were deleted from my site after performing the operation on site with publishing features enabled). By downloading and renaming the site template package as a .cab you will be able to find an Elements.xml that contains all the xml for deploying web parts, including yours as BinarySerializedWebPart if it can’t be otherwise imported.

For more information on this see here.

Good luck!

Key difficulties deploying a SharePoint Online solution using CSOM

I have been developing a console app that utilises the SharePoint C# CSOM to deploy a solution to SharePoint Online (a.k.a Office 365 SharePoint). The solution involves more than just a wsp (although it has one of those too). I have encountered a few difficulties during this process and this blog will discuss those:

  • (Re)creating a site collection
  • Importing a large-ish taxonomy
  • Uploading and installing a sandboxed solution (that contain only declarative elements)
  • Hooking up¬†of taxonomy and (root site) lookup columns
  • Pre-creating a number of sites with specific features enabled (including the root site)

Before I go any further, for those of you reading this before doing something similar yourselves, please be aware of two constraints which caught me by surprise:

  • You can’t leverage the same import taxonomy function that is available in Term store management. If you¬†already have files in that format you will need some custom code (I have an example later on) or you may want to import from a more robust XML formatted document
  • The CSOM does not support uploading or activating sandboxed solutions! However, there is a CodePlex project that assists with this. I also include the dll later in the post that I have rebuilt with references to the lastest v16 Microsoft.SharePoint.Client dlls.
  • The CSOM does not support activating web scoped features! You can active site scoped but not web scoped. You need to use web templates to achieve this. Again, I will cover this in some more depth later on.

SharePointOnline2L-1

Deleting and recreating a site collection

The initial step of the deployment process involves creating a new site collection (having deleted it first as required). In order to perform actions at this scope (tenant) you cannot create your client context in the same manner as usual (with a reference to the site collection; as it is yet to exist and the site collection delete and empty recycle bin require it too). Instead you must create the client context passing in tenant/admin site URL.
This is the one that looks like this: https://<tenant>-admin.sharepoint.com

You can then create a Microsoft.Online.SharePoint.Tenant object by passing the ‘admin’ client content to its constructor. This object requires a reference to the Microsoft.Online.SharePoint.Client.Tenant assembly which is available¬†by downloading and¬†installing¬†the SharePoint Server 2013 Client Components SDK. The assembly can then be found here: C:\Program Files\SharePoint Client Components\16.0\Assemblies

The tenant object provides the methods required to perform the create and delete site collection actions. This process involves a lot of waiting about for deletion to complete, and then provisioning to complete. Unfortunately you can’t continue with other actions until this has occurred. I found this to take upwards of three minutes.

A link to the relevant code that I used to achieve this can be found here: https://gist.github.com/paulryan/cbfaa966571d6a9cdb8b

Importing taxonomy

As mentioned above you can’t pass those CSV files directly to the CSOM and have it import it all for you. In my scenario we had already developed a lot (dozens) of term sets in the form of these CSV files so that¬†were able to import them during a discovery phase so it was important that I could support the import of¬†taxonomy in this form. I wrote code to support in the import of these files, but only to the point that it meets my immediate requirements. Please use the following as a rough guide only¬†as it is not fully featured (or tested beyond the happy path).

The code I wrote to support his can be found here: https://gist.github.com/paulryan/e461f8bac28336b05109#file-importtaxonomycsom-cs

Uploading and activating a sandboxed solution

There is a¬†CodePlex project that provides this functionality (as well as some authentication utilities) that I mentioned above. It¬†performs web requests to UI¬†and I am very¬†glad someone else has already done¬†this¬†before me! It was originally created when SharePoint 2010 was present in the cloud and references the v14 assemblies of the Microsoft.SharePoint.Client assemblies accordingly. If you don’t mind maintain references to both v14 and v16 assemblies then this might be fine. I have instead rebuilt the source having replaced the references with the v16 equivalents.

You can download it here: SharePointOnline.Helper.dll

FYI: v14 is SharePoint 2010, v15 is SharePoint 2013, v16 is SharePoint 2013 Online specific

Activating web features

Actually there isn’t a lot more to say here other than you must use web templates if you need to create sites with features enabled as part of the deployment process as it can’t (currently) be done using the CSOM. I would recommend using the web template for nothing other than activating features and put all other declarative elements in a feature. This will provide the best upgrade experience in the future.

Hooking up taxonomy columns

The best place to start is almost certainly a reference to Chris O’Brien’s blog on this here. As I have the luxury of being able to run further deployment code after uploading/activating the sandboxed solution I opted to avoid having to rebuild the solution for various environments and instead hook-up the columns using the CSOM and a mapping. There is a catch with this though.

If your list instance is built from a list template which defines the managed metadata columns then updating the site column via the CSOM fails to push down the new SspID. To get around this, DO NOT include managed metadata column definitions as part of the list definition (in the fields element). When you run the CSOM to update the site columns it will update the content type and add the column to the list instance with the correct SspID.

Good luck building your SharePoint Online CSOM deployment framework!

Script Editor only runs JavaScript in edit mode

If you need to embed script into a content editable page in SharePoint 2013/Online, you may decide to use the new Script Editor web part. There are often many preferable ways to add script to a page (e.g. via the master page, a custom action, custom control, the ScriptLink property, etc.) however this is an easy option for demo purposes or when deployment activities are out of scope.

There is a gotcha for those who like to skip attributes that may have seems verbose in the past. Any JavaScript which you include via the Script Editor web part must be wrapped in the <script> tag otherwise it will be rendered as text. However, if you fail to provide the required type='text/javascript' or language='javascript' attribute to the script tag then the code will continue to run when the page is in edit mode but will fail to execute when the page is saved and then viewed.

Note the attribute, you need it!
Note the language attribute, you need it!

CAVEAT: As I stated in the first paragraph, this is often not the best way to add script to a page.

Excel data connection with Access 2013 App

I have had a hard time creating data connections with an Access 2013 App database. After a good few hours spent scouring the internet for a solution, and a good few more hours uncovering a “solution” that is underwhelming at best, I am happy to share with you my findings. I really hope that someone will leave a comment with a better solution at some point in the future.

This blog post will provide step-by-step guide on how to achieve a data connection from an Excel workbook (which can be hosted in SharePoint) to the SQL database behind an Access 2013 App. Once this is achieved, a good BI developer should have no trouble visualising the data captured via the Access App with the help of pivot tables, slicing and graphing.

The first step is to identify the server address and database to connect to along with the credentials required to authenticate.

  1. This can be done by navigating to the Access App, clicking the ‘settings’ icon, then clicking ‘Customize in Access’

    Launching the app database in Access
    Launching the app database in Access
  2. Download the .accdw file and open it to launch access
  3. Click ‘FILE’ in the ribbon
  4. Click ‘Manage’
  5. In the drop-down menu ensure that ‘From Any Location’ and ‘Enable Read-Only Connection’ are highlighted with pink squares. If not, click them

    Determining the Access database location and credentials
    Determining the Access database location and credentials
  6. Click ‘View Read-Only Connection Information’
  7. Take note of Server, Database, UserName, and Password from this dialog as you will need them all later

    Access connection information dialog
    Access connection information dialog

Next we use this information to create the data connection.

  1. Launch Excel
  2. Create a new external data connection ‘From Data Connection Wizard’

    Launching the Excel data connection wizard
    Launching the Excel data connection wizard
  3. Click ‘Other/Advanced’, then ‘Next’
    5otherAdvancedConnection
  4. Click ‘SQL Server Native Client 11.0’, then ‘Next’
    6SQLClient11
  5. On the ‘Data Link Properties’ dialog, uncheck the ‘Blank Password’ box and check the ‘Allow saving password’ box, then input the server name, user name, password, and database

    Configuring the data connection. Ensure you provide the database
    Configuring the data connection. Ensure you provide the database
  6. Test the connection, you should see a dialog box with ‘Test Connection Succeeded’
  7. Note that it is when you attempt to make a data connection without providing the database that you get the following error which I bet lead to to this post:

    Failure to connect to the Access 2013 App's SQL database
    Cannot open server ‘xxxxxxxxxx’ requested by the login. Client with IP address ‘00.000.000.000’ is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
  8. You can now click ‘Ok’
  9. Uncheck the ‘Use Trusted Connection’ checkbox and replace the existing password with the correct one. Click ‘Ok’
  10. Select a table and click ‘Next’. You can get fancy here later, let’s just get it working first.
  11. Click ‘Finish’
  12. Click ‘Ok’
  13. The data connection will fail with the following error:

    Connection error
    Initialization of the data source failed. Check the database server or contact your administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new data source to connect to the database.

The final frustration!

On the next dialog, uncheck the ‘Use Trusted Connection’ checkbox and¬†replace the existing password with the correct one. Click ‘Ok’.
The second time it works. This process of providing the connection credentials twice is required not only upon the creation of the connection but also every time the data needs to be refreshed. It makes for a rather poor UX and it is a pretty awful scenario to have to explain to a client.

I really want to believe that there is a setting (most probably under the ‘All’ tab on the ‘Data Link Properties’ dialog) that will workaround this issue however I am yet to find it. Please leave a comment if you find a solution to this issue.