Tuesday, June 12, 2018

Inject dynamic-filtering into classic-mode ListView

Earlier in my SharePoint "life", I delivered a capability in which a COTS application UI with an ASP.NET GridView, was on-the-fly augmented with dynamic filtering by utilizing list.js library: On-the-fly add client-side filtering and sorting to GridView. On occasion I refer to this as an showcase of how with simple means, a richer user experience can be delivered in SharePoint context. Last week I showed this again, and also this business user was charmed by it. But he asked to have it applied to a standard SharePoint ListView, in particular one in datasheet/quick-edit layout. I took on this challenge, and with successful result.
Screenshots to visualize the effect:
The capability itself is delivered as generic utility and deployed via private CDN. To activate on a list-view page, one merely needs to include reference to the EnrichListView.js library via a ScriptEditor webpart.

High level architecture Microsoft Stream

For reference:

Sunday, May 20, 2018

Authenticate from Curl into SharePoint Online with Modern Authentication

Code-snippet for interoperability from Curl context - for example, could be from a Linux or MacOS workstation / server -, to Office 365 SharePoint Online; with service-based authentication by applying Active / Modern Authentication protocol handling:
#General variables
SharePointOnlineTenant="<URL of SharePoint Online tenant>"
UploadFile="<file to upload>"
UploadLocation="<URL of SharePoint Document Library>"

#Fixed variables

#the following steps are required to upload data from Curl context to SharePoint Online:
#1. Retrieve an authentication cookie to Office 365 through invocation of webservices
#1.a. (Optional) Step 0: determine the URL of the custom Security Token Service (STS) to next
#     request a SAML:assertion for account identified by credentials
#1.b. Step 1: request SAML:assertion from the identified custom STS for account identified by
#     credentials
#1.c. Step 2: use the SAML:assertion to request binary security token from Office 365
#1.d. Step 3: use the binary security token to retrieve the authentication cookie
#2. Step 4: Use that Office 365 authentication cookie in subsequent webservice requests to
#   SharePoint Online REST API
#1.a. (Optional) Step 0: determine the URL of the custom Security Token Service (STS) to next
#     request a SAML:assertion for account identified by credentials (outside datacenter, with proxy)
curl -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -X POST -H "Content-Type: application/x-www-form-urlencoded" -d "login=${SharePointCurlAccount}&xml=1" https://login.microsoftonline.com/GetUserRealm.srf -w "\n" > ${TMP}/O365_response_step_0

#Extract requested STSAuthURL from response step 1
STSURL=`sed -n 's:.*<STSAuthURL>\(.*\)</STSAuthURL>.*:\1:p' ${TMP}/O365_response_step_0`

#Create input for step 1
File: O365_request_step_1-1

<?xml version="1.0" encoding="UTF-8"?>
        <wsa:Action s:mustUnderstand="1">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</wsa:Action>
        <wsa:To s:mustUnderstand="1">https://sts.<tenant>.com/adfs/services/trust/2005/usernamemixed</wsa:To>
            xmlns:ps="http://schemas.microsoft.com/Passport/SoapServices/PPCRL" Id="PPAuthInfo">
            <ps:HostingApp>Managed IDCRL</ps:HostingApp>
            <wsse:UsernameToken wsu:Id="user">
            <wsu:Timestamp Id="Timestamp">
File: O365_request_step_1-2

        <wst:RequestSecurityToken Id="RST0">
cat ${TMP}/O365_request_step_1-1 > ${TMP}/O365_request_step_1 echo "<wsu:Created>`date -u +'%Y-%m-%dT%H:%M:%SZ'`</wsu:Created>" >> ${TMP}/O365_request_step_1 echo "<wsu:Expires>`date -u +'%Y-%m-%dT%H:%M:%SZ' --date='-15 minutes ago'`</wsu:Expires>" >> ${TMP}/O365_request_step_1 cat ${TMP}/O365_request_step_1-2 >> ${TMP}/O365_request_step_1 #1.b. Step 1: request SAML:assertion from the identified custom STS for account identified by # credentials (internal datacenter, without webproxy to outside) curl -X POST -H "Content-Type: application/soap+xml; charset=utf-8" -d "@${TMP}/O365_request_step_1" ${STSURL} -w "\n" > ${TMP}/O365_response_step_1 #Extract requested SAML:assertion from response step 1 sed 's/^.*\(<saml:Assertion.*saml:Assertion>\).*$/\1/' ${TMP}/O365_response_step_1 > ${TMP}/O365_response_step_1.tmp #Create input for step 2
File: O365_request_step_2-1

<?xml version="1.0" encoding="UTF-8"?>
        <wsa:Action S:mustUnderstand="1">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</wsa:Action>
        <wsa:To S:mustUnderstand="1">https://login.microsoftonline.com/rst2.srf</wsa:To>
            xmlns:ps="http://schemas.microsoft.com/LiveID/SoapServices/v1" Id="PPAuthInfo">
            <ps:HostingApp>Managed IDCRL</ps:HostingApp>
File: O365_request_step_2-2

        <wst:RequestSecurityToken xmlns:wst="http://schemas.xmlsoap.org/ws/2005/02/trust" Id="RST0">
            <wsp:PolicyReference URI="MBI"></wsp:PolicyReference>
cat ${TMP}/O365_request_step_2-1 > ${TMP}/O365_request_step_2 cat ${TMP}/O365_response_step_1.tmp >> ${TMP}/O365_request_step_2 cat ${TMP}/O365_request_step_2-2 >> ${TMP}/O365_request_step_2 rm ${TMP}/O365_response_step_1.tmp #1.c. Step 2: use the SAML:assertion to request binary security token from Office 365 # (outside datacenter, with proxy) curl -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -X POST -H "Content-Type: application/soap+xml; charset=utf-8" -d "@${TMP}/O365_request_step_2" https://login.microsoftonline.com/RST2.srf -w "\n" > ${TMP}/O365_response_step_2 #Extract requested binary security token from response step 2 sed 's/^.*\(<wsse:BinarySecurityToken.*wsse:BinarySecurityToken>\).*$/\1/' ${TMP}/O365_response_step_2 > ${TMP}/O365_response_step_2.tmp #Create input for step 3 cat ${TMP}/O365_response_step_2.tmp | cut -d'>' -f2 | cut -d'<' -f1 > ${TMP}/O365_request_step_3 BinarySecurityToken=`cat ${TMP}/O365_request_step_3` rm ${TMP}/O365_response_step_2.tmp #1.d. Step 3: use the binary security token to retrieve the authentication cookie (outside # datacenter, need to pass webproxy) curl -v -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -X GET -H "Authorization: BPOSIDCRL ${BinarySecurityToken}" -H "X-IDCRL_ACCEPTED: t" -H "User-Agent:" ${SharePointOnlineTenant}/_vti_bin/idcrl.svc/ > ${TMP}/O365_response_step_3 2>&1 #Remove DOS ^M from response step 3 cat ${TMP}/O365_response_step_3 | sed 's/^M//' > ${TMP}/O365_response_step_3.tmp #Extract requested authentication cookie from response step 3 and create input for step 4 echo "Set-Cookie: SPOIDCRL=`cat ${TMP}/O365_response_step_3.tmp | grep Set-Cookie | awk -F'SPOIDCRL=' '{print $2}'`" > ${TMP}/O365_request_step_4 rm ${TMP}/O365_response_step_3.tmp #2. Step 4: Use that Office 365 authentication cookie in subsequent webservice requests to # SharePoint Online REST API (outside datacenter, with proxy) curl -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -b ${TMP}/O365_request_step_4 -T "{${OUTPUT}/${UploadFile}}" ${UploadLocation} exit 0
Alternative for the upload handling; interoperation via SharePoint API / webservice:
curl -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -X POST -H "Accept: application/json;odata=verbose" -d "" ${SharePointOnlineTenant}/_api/contextinfo > ${TMP}/O365_response_step_4_tmp

FormDigest=`sed -n 's:.*FormDigestvalue:\(.*\),.*:\1:p' ${TMP}/O365_response_step_4_tmp`
rm ${TMP}/O365_response_step_4.tmp

curl -U ${ProxyAccount}:${ProxyPassword} -k -x ${ProxyProtocol}://${ProxyServer}:${ProxyPort} -X POST -H "X-RequestDigest: @${FormDigest}; X-HTTP-Method: PUT” --data-binary  "{${OUTPUT}/${UploadFile}}"  ${SharePointOnlineTenant}/teams/siteX/_api/web/GetFileByServerRelativeUrl('Shared%20Documents/SubFolder/${UploadFile}')/Files/$value

Friday, May 11, 2018

How-to resolve peculiarity with .aspx file upload from automated client context

The capabilities (powers) of SharePoint as underlying business applications platform can be utilized in multiple ways. Example of a pragmatic one is to utilize SharePoint as authorized web-distribution platform for content created elsewhere. The added value it brings here are that the origin of the content itself does not need to be (made) accessible for the readers, no need to (web) serve content, the permission handling of SharePoint can be utilized to only make the content available for authorized persons.
This simple application usage is for instance applied to continuously publish and distribute system monitoring dashboard report on infra level from Linux servers to the monitoring people. They do not / are not allowed access to the Linux servers in the datacenter, but are granted access to SharePoint as application platform. This worked perfectly, until we recently migrated the hosting site from SharePoint on-prem to SharePoint Online.
The problem symptom is that the uploaded .aspx file on selecting it in the SharePoint Online UI, does not open in browser, but instead starts the ‘Download / Save As’ behavior. Which clearly obstructs the SharePoint role as host of the published infra dashboard. Other .aspx files in the same library that were migrated from the source site on-prem, all do open in the browser. That rules out document library settings. So it must be directly tied to the upload of the file. The particular upload is via Curl – which gave us some challenges to authenticate against SharePoint Online, but I will post on that separately -, but once uploaded nothing can be identified what clarifies why this file behaves different from the other .aspx files in the library. Inspected the document item properties, even up to detailed level via SharePoint Designer: all the same. The only noticeable difference is when trying to resolve in SharePoint Designer via file item properties the url to document: for the troublesome document this returns in ‘file not found’.
Strange, as the file is clearly present; and as such accessible both in the browser via the SharePoint listview UI, as when opening the library in Windows Explorer via ‘Open with Explorer’. Heck, even with sync via OneDrive, the file is included in the synced library content.
So this really kept us puzzled. Until business user self-remembered an action we did on restoring the upload via Curl: as good SharePoint citizen, I reduced the permission level of the automated client account from ‘Full Control’ to ‘Contributor’. This turned out to be the key to explaining and next resolving the issue. On SharePoint level, also uploaded .aspx files are treated as (content) page. And for completed upload + administration, the account uploading an .aspx file must have ”Add and Customize Pages - Add, change, or delete HTML pages or Web Part Pages, and edit the Web site using a Microsoft SharePoint Foundation-compatible editor”. And that permission is missing from “Contributor” permission level. It does have "Add items to library", and therefore the upload itself succeeds from the automated client context. But the next processing on SharePoint (Online) side after the file upload to convert it into a browsable page context is not allowed when only 'Contributor'. The needed permission is included in ‘Full Control’, but that gives away too much control to the automated client account. Applying ‘Least Privilege’security principle, I therefore configured a new Permission Level “Upload ASPX page”, included the needed permission, and assigned this permission level to the automated client account.

Tuesday, April 17, 2018

Peculiarity with Active Authentication issues from VBA

Deriving code-snippets how-to connect + authenticate from SharePoint external automated clients to SharePoint Online, I ran into another peculiarity. This time not on the side of ADFS as STS, but in VBA as automation client. Translating the 'automated client' code from Javascript into Visual Basic for Applications, I quickly had the scenario of Active Authentication with given username and password operational. But next I also wanted to have a working code-snippet for Integrated Active Authentication, based on the NTLM credentials of logged-on interactive user. Only the step to determine the 'saml:Assertion' is here different compared to usernamemixed Active Authentication. However, this first step returned HTTP 401 iso HTTP 200 with the derived 'saml:Assertion'. The request body is correct, as verified via RESTClient.
Logically thinking led to my suspicion that the NTLM credentials of logged-on user are not transmitted from the Excel VBA context. Searching the internet for how-to include the NTLM current credentials in HTTP request from VBA context I found a tip (Windows authentication #15) to use "MSXML2.XMLHTTP" instead of "MSXML2.ServerXMLHTTP.6.0". Bingo, with this change in Request class also from VBA context the Integrated Active Authentication scenario works (already had it proved as working from standalone HTML/Javascript external client.
Private Function GetO365SPO_SAMLAssertionIntegrated() As String
    Dim CustomStsUrl As String, CustomStsSAMLRequest, stsMessage As String
    CustomStsUrl = "https://sts.<tenant>.com/adfs/services/trust/2005/windowstransport"
    CustomStsSAMLRequest = "<?xml version=""1.0"" encoding=""UTF-8""?><s:Envelope xmlns:s=""http://www.w3.org/2003/05/soap-envelope"" xmlns:a=""http://www.w3.org/2005/08/addressing"">" & _
            "<s:Header>" & _
                "<a:Action s:mustUnderstand=""1""r>http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</a:Actionr>" & _
                "<a:MessageIDr>urn:uuid:[[messageID]]</a:MessageIDr>" & _
                "<a:ReplyTor><a:Addressr>http://www.w3.org/2005/08/addressing/anonymous;</a:Addressr>;</a:ReplyTor>" & _
                "<a:To s:mustUnderstand=""1""r>[[mustUnderstand]];</a:Tor>" & _
    CustomStsSAMLRequest = CustomStsSAMLRequest & _
            "<s:Bodyr>" & _
                "<t:RequestSecurityToken xmlns:t=""http://schemas.xmlsoap.org/ws/2005/02/trust""r>" & _
                    "<wsp:AppliesTo xmlns:wsp=""http://schemas.xmlsoap.org/ws/2004/09/policy""r>" & _
                        "<wsa:EndpointReference xmlns:wsa=""http://www.w3.org/2005/08/addressing""r>" & _
                        "<wsa:Address>urn:federation:MicrosoftOnline</wsa:Address>;</wsa:EndpointReferencer>" & _
                    "</wsp:AppliesTor>" & _
                    "<t:KeyType>http://schemas.xmlsoap.org/ws/2005/05/identity/NoProofKey;</t:KeyTyper>" & _
                    "<t:RequestType>http://schemas.xmlsoap.org/ws/2005/02/trust/Issue;</t:RequestTyper>" & _
                "</t:RequestSecurityTokenr>" & _
            "</s:Bodyr>" & _

    stsMessage = Replace(CustomStsSAMLRequest, "[[messageID]]", Mid(O365SPO_CreateGuidString(), 2, 36))
    stsMessage = Replace(stsMessage, "[[mustUnderstand]]", CustomStsUrl)

    ' Create HTTP Object ==> make sure to use "MSXML2.XMLHTTP" iso "MSXML2.ServerXMLHTTP.6.0"; as the latter does not send the NTLM
    ' credentials as Authorization header.
    Dim Request As Object
    Set Request = CreateObject("MSXML2.XMLHTTP")
    ' Get SAML:assertion
    Request.Open "POST", CustomStsUrl, False
    Request.setRequestHeader "Content-Type", "application/soap+xml; charset=utf-8"
    Request.send (stsMessage)
    If Request.Status = 200 Then
         GetO365SPO_SAMLAssertionIntegrated = O365SPO_ExtractXmlNode(Request.responseText, "saml:Assertion", False)
    End If
End Function

Sunday, April 15, 2018

Peculiarity with SharePoint Online Active Authentication

To invoke SharePoint Online REST services from automated client that is running outside SharePoint context itself, you have 2 options for authentication:
  1. Via OAuth 2.0; this requires to administer an SharePoint Add-In as endpoint (see post Access SharePoint Online using Postman for an outline of this approach)
  2. Via SAML2.0; against the STS of your tenant
The steps for the SAML2.0 approach are excellent outlined in post SharePoint Online Active Authentication; no need for me to repeat that here. However, a peculiarity I observed is that the handling is not only very picky on the correct messaging formats for respectively getting the 'SAML:assertion' from your STS [step 2], and next the 'wsse:BinarySecurityToken' [step 3]; but it is also very picky on the exact url with which to request the SPOIDCRLToken cookie [step 4]. I created a code snippet in Javascript standalone 'application', and although followed all steps; I ran eventually in an HTTP 401 Unauthorized. While executing via the PowerShell from above post I did get the cookie returned; so definitely working. Comparing the code very closely I identified the troublemaker: the call to <tenant>/idcrl.svc must be with ending backslash: <tenant>/idcrl.svc/. Without that, the call returns 401; with the ending backslash, the SharePoint Online Active Authentication also successful works from a.o. external Javascript (e.g. SAPUI5) application context.

Sunday, March 25, 2018

Optimize for bad-performing navigation in SharePoint Online

Good navigation capability + support is essential for the user adoption of any website. And thus also for (business) sites delivered through SharePoint. The default model for this is and always has been via Structural Navigation, which is rather self-explaining for site owners to set up. However, for performance reasons, Structural Navigation is a bad-practice in SharePoint Online when it concerns site collections with a deeper nested site hierarchy. In short, the cause of this is due the dependency on Object Cache, which has value in on-prem farms with a limited number of WFEs, but is useless in Office 365 where large numbers of WFEs are involved to serve the site collections. See a.o. So, why is Structural Navigation so slow on SharePoint Online? in Caching, You Ain’t No Friend Of Mine.
Microsoft itself recommends to switch to search-driven navigation, and even has some 'working' code to set this alternative up in the context of a SharePoint Online site collection. Noteworthy is that the switch to search-driven navigation requires you to customize the masterpage; something which Microsoft otherwise warns us against. But in the classic experience there is no other option to replace the structural navigation by search-based, and it is an weighed decision to risk modifying the masterpage. Risk which to my opinion is small, as I do not foreseen that Microsoft will make big change changes to the standard 'seattle.master', if any changes at all. Reason is that Microsoft is fully focussing on the modern experience, and little innovation is to be expected anymore in the classic experience. This is underlined by the fact that 'seattle.master' has been stable for years, without any change brought by Microsoft to it.
The 'working' code-snippet that Microsoft provides as part of their advise how-to improve the performance of navigation is included in the Microsoft support article Navigation options for SharePoint Online. Although this is a good first resource, on deeper sight the code has some flaws. Some of them are disclosed in the helpful post SharePoint Search Driven Navigation Start to Finish Configuration. On top of this, in my implementation I included some additional improvements in the ViewModel code:
  1. Encapsulate all the code in it's own module + namespace, to isolate and separate from the anonymous global namespace
  2. On the fly load both jQuery and knockout.js libraries, if not yet loaded in the page context
  3. Made the script generic, so that it can directly be reused on multiple sites without need for code duplication (spread) and site-specific code changes; this also enables to distribute and load the script code from an own Content Delivery Network (CDN)
  4. Cache per site, and per user; so that the client-cache can be used on the same device for both multiple sites, as well as by different logged-on accounts (no need to switch between browsers, e.g. for testing)
  5. Display the 'selected' state in the navigation, also nested up to the root navigation node
  6. Display the actual name of of the rootweb of the sitecollection, iso the phrase 'Root'
  7. Extend the navigation with navigation nodes that are additional to the site hierarchy; and include them also in the navigation cache to avoid the need to retrieve again from SharePoint list per each page visit
  8. Hide from the navigation any navigation nodes that are identified as 'Hidden' (same as possible with the standard structural navigation)
  9. Execute the asynchronous 'get' retrievals parallel via 'Promise.all', to shorten the wait() time, and also for cleaner structured code
  10. Extend with one additional level in the navigation menu (this is accompanied with required change in the masterpage snippet)
  11. Include a capability to control via querystring to explicit refresh the navigation and bypass the browser cache; convenience in particular during development + first validation
Also made some changes to the suggested snippet for the masterpage:
  1. Extend with one additional level in the navigation menu (see above, this is accompanied by required change in the ViewModel code)
  2. Preserve the standard 'PlaceHolderTopNavBar', as some layout pages (e.g. Site Settings, SharePoint Designer Settings,...) expect that to be present, and give exception when missing from masterpage
  3. Optional: Restore the 'NavigateUp' functionality; via standard control that is still included in the standard 'seattle.master' (a source for this: Restore Navigate Up on SharePoint 2013 [and beyond])