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

Sunday, March 18, 2018

Beware: set site to readonly impacts the permission set overviews

In our execution process of migrating collaboration sites from SharePoint 2010 to SharePoint Online, on completion we apply the following go-live steps:
  1. Set the lock status of source site to ‘readonly' (Lock or Unlock site collections)
  2. Enable a redirect from the root-url of the source site to the root-url of the target migrated site (This is a real success and much appreciated by our end-users: as it is almost impossible for each to remember to update own bookmarks to the sites now migrated. Definitely a best practice I recommend to everyone doing a migration to SharePoint Online !!)
  3. Send out communication to the site owner that his/her site is migrated. Migration issues that were not identified during the User Acceptance Test (UAT) of the migration will be handled as after-care
Immediate after the go-live of a migrated site, the after-care period starts. One of the issues that may arise is that a site visitor has lost authorizations compared to the old source site. We then compare the source vs the migrated site (note: in our custom IIS Redirect HttpModule we’ve incorporated the capability to bypass the automatic redirection) to investigate the complaint. Something to be aware of when comparing the permission set, is that via our go-live actions we’ve impacted also the source site: setting the site to readonly, results that in the “check permissions” display per checked user a large list of “Deny” permissions will be enlisted.
Nothing to be surprised (⇨ 'Deny' permissions are set via User Policy on webapplication level, and cannot be set on the level of individual site collections [1] [2]) or worried about, once you understand where this overwhelming list is actually coming from. And that you can safely ignore them in understanding what the actual “productive” permission set of the checked account was on the old source site.

Monday, March 5, 2018

Users need 'Use Client Integration Features' permission to launch OneDrive Sync from SharePoint Online library

Business users typical like very much the way-of-working via explorer with files stored in SharePoint libraries. Were we in the past limited to doing that either via IE as browser with 'Open with Explorer', or via setting a Map Network Drive; nowadays the better alternative is to work via OneDrive Sync. Our business users underline that advise. However, some reported that they were unable to execute OneDrive Sync from a specific library, while a colleague could do this successful. My initial suspicions were towards issues with browser (this was actually the case with related user issue, failure to 'Open with Explorer' - there the immediate cause was that person's favorite browser is Chrome iso IE, and Chrome does not support 'Open with Explorer' without additional extensions), automatic Office 365 login not working as it should (recent changed in our tenant from old to new sign-in experience, accompanied by a change from 'Keep Me Signed In' to 'Stay signed in'), inconsistency in the components of the local installed Office 365 suite, missing OneDrive license, ...
However, upon following the investigation route that for colleague it works, I compared their permissions. To observe that the business users for which click on Sync does not launch the OneDrive Sync client, miss the specific permission 'Use Client Integration Features' in their SharePoint authorizations. This is a required permission for a.o. OneDrive Sync launch. Included the permission in their applied Permission Level, and problem resolved.

Wednesday, February 21, 2018

Migrated SharePoint 2010 workflow cannot be opened Online in SharePoint Designer 2013

On migration of SharePoint 2010 site to SharePoint Online, it is supported that SharePoint Designer workflows are migrated also. They are classified in their new destination as 'SharePoint 2010 workflows'. There is a difference though qua maintenance tooling: one has to switch from using SharePoint Designer 2010 to SharePoint Designer 2013 edition. Such a switch may result in an issue: "SharePoint designer cannot display the item".
The root cause of this is within the cache-handling of SharePoint Designer. Both the 2010 and 2013 versions use the same local location on your workstation to store cached files. However, the structure within the cached files is not forwards-compatible from SharePoint Designer 2010 to 2013. To resolve the error, one can apply the following approaches:
  1. Disable usage of 'cache' capability in SharePoint Designer 2013: it will then no longer try to load + reuse the cached files that were initially created on your workstation by opening the workflow via SharePoint Designer 2010
  2. Cleanup the local cache to remove the SharePoint 2010 versions of the cached workflow files: delete all cached files from these local locations (Resource: SharePoint Designer cannot display the item (SharePoint 2013))
    • C:\Users\<UserName>\AppData\Roaming\Microsoft\SharePoint Designer\ProxyAssembleCache
    • C:\Users\<UserName>\AppData\Roaming\Microsoft\Web Server Extensions\Cache
    • C:\Users\<UserName>\AppData\Local\Microsoft\WebsiteCache
  3. (Get yourself a new / other laptop:) Open the workflow in SharePoint Designer on another workstation, on which the workflow was not managed previously via SharePoint Designer 2010 when still on SharePoint 2010

Monday, February 12, 2018

PowerShell to assess the external access authorization per site

As clarified in previous post, Azure AD Access Reviews capability although promising qua concept, is in it's current implementation yet unfit to assess the external access per site. But luckily we have PowerShell, which enables us per site, determine the collection of guest authorizations and ask site owner to review + re-confirm the authorizations. Crucial is to provide insight and awareness; who all has access authorization to my business site, and as site / business owner I still are ok with each indivdual guest authorization? For those not / no longer; explicit revoke, for good secure housekeeping in your external shared site.
PowerShell script to assess the external authorization per site in the tenant:

Access Review of guest users into the SharePoint tenant

#Connection to SharePoint Online
try {
    Connect-SPOService -Url $SPOAdminSiteUrl -ErrorAction Stop
} catch {

$externalUsersInfoDictionary= @{}

$externalSharedSites = Get-SPOSite | Where-Object {$_.SharingCapability -eq "ExistingExternalUserSharingOnly"}
foreach ($site in $externalSharedSites)
    $externalUsersInfoCollection= @()

    $position = 0
    $page = 0
    $pageSize = 50
    while ($position -eq $page * $pageSize) {
        foreach ($externalUser in Get-SPOExternalUser -Position ($page * $pageSize) -PageSize $pageSize -SiteUrl $site.Url | Select DisplayName,Email,WhenCreated) {
            if (!$externalUsersInfoDictionary.ContainsKey($externalUser.Email)) {
                $externalUsersInfoDictionary[$externalUser.Email] = @()
            $externalUsersInfo = new-object psobject 
            $externalUsersInfo | add-member noteproperty -name "Site Url" -value $site.Url
            $externalUsersInfo | add-member noteproperty -name "Email" -value $externalUser.Email
            $externalUsersInfo | add-member noteproperty -name "DisplayName" -value $externalUser.DisplayName
            $externalUsersInfo | add-member noteproperty -name "WhenCreated" -value $externalUser.WhenCreated
            $externalUsersInfo | add-member noteproperty -name "Preserve Access?" -value "Yes"


    if ($externalUsersInfoCollection.Count -ne 0) {
        $exportFile = "External Access Review (" + $site.Url.SubString($site.Url.LastIndexOf("/")+ 1) + ")- " +  $(get-date -f yyyy-MM-dd) + ".csv"
        $externalUsersInfoCollection |  Export-Csv $exportFile -NoTypeInformation

# Export matrix overview: per user, in which of the external sites granted access
$externalUsersInfoCollection= @()

$externalUsersInfoDictionary.Keys | ForEach-Object {
    $externalUsersInfo = new-object psobject
    $externalUsersInfo | add-member noteproperty -name "User Email" -value $_

    foreach ($site in $externalSharedSites) {
        if ($externalUsersInfoDictionary[$_].Contains($site.Url)) {
            $externalUsersInfo | add-member noteproperty -name $site.Url -value "X"           
        } else {
            $externalUsersInfo | add-member noteproperty -name $site.Url -value ""             


$exportFile = "External Access Review user X site - " +  $(get-date -f yyyy-MM-dd) + ".csv"
$externalUsersInfoCollection |  Export-Csv $exportFile -NoTypeInformation