Removing orphaned OneDrive secondary site collection admins

This is a scenario where, the user was deleted from Azure AD months ago but the OneDrive secondary site collection administrator permission assignments (OneDrive secondary admin) were intact as a thumbprints. This target account  supposed to be a service account utilized during a file server migration project and apparently assigned with OneDrive secondary site collection admin permission across all users in the tenancy.

The generic SharePoint Online commands did not do the job because “The user account does not exists in the AD” hence the identity validation fails at the first place. The OneDrive admin UI will do the job for a single OneDrive account but doesn’t help much in bulk operation scenarios like the one I dealt with.

Workaround: To remove this I used SharePoint PnP PowerShell command which was the only way around it.

Add yourself first in to one of the site collections (OneDrive accounts) before running the command so that you can verify the status ‘before’ and the result ‘after’.

For a single site collection (OneDrive Personal site in this case), run PowerShell as admin and execute these lines after customizing with your tenant, URL and user details. For this case, we will be using ‘Span ID’ to point to the abandoned account which usually goes as follows i:0#.f|membership|service.svc@tenant.onmicrosoft.com

#Config Variables - Customize this to match yours 
$SiteURL = "https://mantoso-my.sharepoint.com/personal/manoj_karunarathne_mantoso_com"
$UserID="i:0#.f|membership|account@tenant.onmicrosoft.com"
 
#Connect to PnP Online Service MFA
Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
#sharepoint online powershell delete user from site collection
Remove-PnPUser -Identity $UserID -Force

If your result is similar to below, the command has done its job ! now go check that permission box and you should not see that account anymore.

Advertisement

Export All Sites, libraries and lists of a SharePoint Online Site Collection

If you remember classic SharePoint, it had that nice looking (and yet unreliable sometimes) feature called “SharePoint Site Structure” which was eventually deprecated as move & copy functions were introduced. This was very insightful to understand the site and content hierarchy across the entire SharePoint farm.

However, let’s assume you want to review your modern day SharePoint Online hierarchy every once in a while, and make sure your sites, libraries and lists are aligning to best practices as far as the depth of the site levels? Or, you just want to know what sort of sites exist in your site collection, we still have a manual way of getting those information out using a simple PowerShell script. This may not be the best sophisticated way of getting a handy report which can probably be obtained using a 3rd party tool.

Unless its a test environment, we rarely notice any Office 365 tenant without MFA enabled, so this script is Modern-Auth friendly and supports MFA. You can generate a basic report of all sites, libraries and lists in a specific site collection by defining the site collection name and CSV path to save it.

###Function to Get Lists and Libraries of a web
Function Get-SPOSiteInventory([Microsoft.SharePoint.Client.Web]$Web)
{
    Write-host -f Yellow "Getting Lists and Libraries from site:" $Web.URL
 
    ###Get all lists and libraries
    $SiteInventory= @()
    $Lists= Get-PnPList -Web $Web
    foreach ($List in $Lists)
    {
        $Data = new-object PSObject
        $Data | Add-member NoteProperty -Name "Site Name" -Value $Web.Title
        $Data | Add-member NoteProperty -Name "Site URL" -Value $Web.Url
        $Data | Add-member NoteProperty -Name "List Title" -Value $List.Title
        $Data | Add-member NoteProperty -Name "List URL" -Value $List.RootFolder.ServerRelativeUrl
        $Data | Add-member NoteProperty -Name "List Item Count" -Value $List.ItemCount
        $Data | Add-member NoteProperty -Name "Last Modified" -Value $List.LastItemModifiedDate
        $SiteInventory += $Data
    }
 
    ###Get All Subwebs
    $SubWebs = Get-PnPSubWebs -Web $Web
    Foreach ($Web in $SubWebs)
    {
        $SiteInventory+= Get-SPOSiteInventory -Web $Web
    }
    Return $SiteInventory
}
 
###Config Variables
$SiteURL = "https://sitename.sharepoint.com/sites/PWA"
$CSVFile = "C:\temp\filename.csv"
 
###Get Credentials to connect
 
Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
    ###Get the Root Web
    $Web = Get-PnPWeb
 
    ###Call the function and export results to CSV file
    Get-SPOSiteInventory -Web $Web | Export-CSV $CSVFile -NoTypeInformation
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red

As you execute it, you’ll be prompted for credentials and the report will be generated (duration might depend on the number of site collections and the weight of each)

Original script used in this scenario was published in this article of SharePoint Diary

Error when trying to open a OneDrive uploaded file from Outlook client application: The page that you are trying to access cannot be loaded

From the first look this error definitely sounds like “Office 365 ATP Safe links or Safe attachments” policy components blocking the files behind the scenes, but it’s not !. Well, it could be the same error in such scenarios but in my case, Safe Link or Safe Attachment policies were not the issue.

Scenario: Users trying to share content within the organization by uploading them to a OneDrive/SharePoint location chosen from the dropdown as attached to the Outlook email on the go.

Attach a file to email and upload it to OneDrive/SharePoint

Attach a file from SharePoint/OneDrive

Once added the cloud based file to the mail, this is how it looks. Then send it out

Emails are smoothly delivered to the recipients however, when they try to open them (by simply clicking on the URL), recipients get the above error (The page that you are trying to access cannot be loaded)

This happens only when:

• Users use desktop application of Outlook (not happening in OWA, files are accessed in OWA without an issue)
• Or, Attached the file in to OneDrive or SharePoint as shown below (not happening when file URL is pasted to Outlook email)

The environment had Office 365 ATP safe link and Safe attachment policies implemented properly. And the exceptions are added to trusted partners across the globe for this company (as a multi-national)

Resolution: Due to the criticality of this organization-wide behavior, I worked with Microsoft Support team towards a fix and here’s what we did.

We ran a fiddler session while opening the file from both OWA and Client App and reviewed the recording – OWA is working fine while outlook not able to access the wrapped URL. It looked like outlook API used for calling ATP is not functioning well.

Microsoft further analyzed by collecting below information and then engaged the Product Group:

• Collect fiddler trace for both OWA and Outlook to make comparison
• Copy the Wrapped URL from OWA and Outlook
• Collect the corresponding message sample

As of now, Microsoft Product Group for ATP have not identified if it is a misconfiguration or product related bug, however, I receive constant responses stating that they are actively working towards a resolution. I will update this space as soon as I hear anything applicable towards a resolution/ETA.

Workaround: The only workaround for this is to request users to make use of Outlook Web whenever a file needs to be opened that is received via an email.

Change default File Open Behavior of SharePoint Online

The default option in SharePoint online to open files stored in document libraries is “Open in Browser”. You can leave it like that as long as you don’t have an specific requirement to change this behavior.

In some cases, end users prefer to open file by client application due to many reasons and they are fair reasons, mostly.

1. Client application offers rich capabilities which allows users to get things done effectively and efficiently
2. Some organizations using SharePoint as the central document management platform across the entire company. At this point they might prefer to have the files opened by client application as default.

There are various ways to enable this functionality which impact different levels.

1. Document library level
2. Single site collection level
3. Across the entire tenant

If you’d like to set a specific document library to open files in client application by default. Simply log in to your SharePoint online tenant, direct to the desired library and select the radio button under the Advance Settings as you wish (This overrides the setting applied at site collection level and open documents in client application instead of browser)

Navigate to the Document Library –> Click on Settings gear –> Library Settings from the menu.

Under General Settings –> Click on “Advanced Settings”

To enable this for a specific site collection (applies for the entire collection unless you have chosen from individual libraries manually as shown in the screenshot above to opt out)

To do this for an specific site collection, we have to activate a site collection level feature. Simply log in to your SharePoint online tenant, direct to the desired site.

Go to Site Settings –> Site Collection Features

Click on “Activate” button next to “Open Documents in Client Applications by Default” feature

You can use the following PowerShell script to do get the same thing done in a bulk mode across all site collections in the tenant (ORG WIDE).

This is a SharePoint PnP PowerShell script which uses an CSV file as the source for site names.

1. First you have to get all the site URLs exported from the SharePoint Admin Centre in Office 365 Admin Portal
2. Then save it as an CSV file and point this script to that file (Change the CSV path in the script)

Your CSV should look like this (Site URLs separated in to individual columns, not rows. If you are having hard time getting this format, it’s quite easy, use the Transpose feature under Paste special)

Note: Obviously, this script will only cover the existing site collections of your tenant. For any upcoming new site collection created after running this has to enable it manually again.

$username = Read-Host "Provide the username"
$password = Read-Host -Prompt "Password for $username" -AsSecureString
$O365credential = New-Object PSCredential($userName,$passWord)

# Chnage CSV path here
$site = Import-csv C:\Official\Tools\remain.csv

Foreach ($URL in $site.URL)
{
try {
    Connect-PnPOnline -Url $URL -Credentials $O365credential

    Write-Host "Connected to " $URL 
    Write-Host "Enabling features on" $URL 

	# Enter Feature Id & scope

    Enable-PnPFeature -Identity 8a4b8de2-6fd8-41e9-923c-c7c3c00f8295 -Force -Scope Site

    Write-Host "Disconnecting from " $URL 
    Disconnect-PnPOnline    
    }
    Catch 
    {
    Write-Host "Got error" $error
    }
}
Write-Host "Completed"

Activate a SharePoint Online feature across multiple sites using PnP PowerShell

Unlike SharePoint On-premise, the Online SharePoint platform has a limitation when it comes to Manageability. However, with new PnP capabilities, you have more power than before. One of the capability I recently used across few client tenant is “Enable-PnPFeature”.

SharePoint Patterns and Practices (a.k.a PnP) contains a library of PowerShell commands (PnP PowerShell) that allows you to perform complex provisioning and artifact management actions using CSOM towards SharePoint Online (SPO) and On-Premise. This unified management capability addresses the gap between On-Premise and Online SharePoint backend.

This following short script enables an specific feature across multiple sites (as defined in the CSV file). Make sure you replace the file path and feature ID to match your requirements.

Prerequisites:

• You must have PnP PowerShell module installed in your PC and ready to connect
• Retrieve the feature ID list and choose the right one
• Keep the list of sites in CSV format stored in the folder path given the script

#Activate Feature for multiple sites 

$username = Read-Host "Provide the username"
$password = Read-Host -Prompt "Password for $username" -AsSecureString
$O365credential = New-Object PSCredential($userName,$passWord)

# Chnage CSV path here
$site = Import-csv C:\Tools\SitesList.csv

Foreach ($URL in $site.URL)
{
try {
    Connect-PnPOnline -Url $URL -Credentials $O365credential

    Write-Host "Connected to " $URL 
    Write-Host "Enabling features on" $URL 

	# Enter Feature Id & scope

    Enable-PnPFeature -Identity 8a4b8de2-6fd8-41e9-923c-c7c3c00f8295 -Force -Scope Site

    Write-Host "Disconnecting from " $URL 
    Disconnect-PnPOnline    
    }
    Catch 
    {
    Write-Host "Got error" $error
    }
}
Write-Host "Completed"

Excel unable to access SharePoint Online files, fails with an error “sorry we couldn’t open”

This happened to few of my clients time to time in SharePoint online environments. You may have seen it but weirdly for some users only? You are not alone.

One of the errors is ‘Sorry we couldn’t open https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’

And, the other error is – ‘Microsoft Excel cannot access the file ‘https://mantoso.sharepoint.com/DocumentLibrary/excelfile.xlsx’. There are several possible reasons:

• The file name or path does not exist.
  
• The file is being used by another program.
  
• The workbook you are trying to save has the same name as a currently open workbook.’

This issue in my perspective, can be caused by Office Document Cache of your Office Desktop application. Here’s how I managed to get rid of it.

Open Windows Explorer and copy and paste one of the following locations into the address bar:

Clearing Office Document Cache for Office 2016

%localappdata%\Microsoft\Office\16.0\OfficeFileCache

Clearing Office Document Cache for Office 2013

%localappdata%\Microsoft\Office\15.0\OfficeFileCache

Select all files beginning with ‘FS’

And delete those files. Restart the Excel application and you should now be able to open files from SharePoint.

OneDrive sync error: ‘You are already syncing this account’

This message appeared on plenty of end user devices across many of my clients when they try to synchronize their SharePoint Libraries using OneDrive sync client (Not OneDrive library itself though), no big deal, it was all about browser in our case (could differ in some case as well, I presume). The exact error is ‘You’re already syncing this account’. Open your OneDrive – Your Organization Name folder or sign in with a different account’

A common nature of the scenario was that, everyone got this error was using either Microsoft Edge or Internet Explorer. The immediate solution was using an alternative browser instead, we tried Chrome and it worked like charm.

When Chrome prompted options, choose: ‘Open URL : OneDrive client protocol’

And, you can now start syncing the library

Retrieve and export Office 365 Group Members (Part01)

Members of an Office 365 group can retrieved and exported in to a CSV file using Office command-line capabilities.

I recently had an requirement in a large enterprise setup to retrieve the members list of specific set of top level groups and export them in to a CSV file for a auditing purpose. This could be a common requirement, hence here it goes as a quick blog.

We will cover up two scenarios here.

1. Retrieve and export members of an specific Office 365 group (Part01)
2. Retrieve and export members of all Office 365 groups (Part 02)

Let’s cover up the 1st scenario. Just log in to Office 365 portal and head on to Admin Center page –> Groups –> click on the respective group and copy the group mail

Now let’s run PowerShell as an Administrator and make sure the execution policy is set to remote sign (if you haven’t yet, run this on the device you are going to do this task – set-executionpolicy remotesigned)

Here are the next few lines piece by piece. Also I have given the full script of the whole thing at the end.

$Credential = Get-Credential

Enter the Admin credentials for your tenant here.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection

Import-PSSession $Session -DisableNameChecking

Import command might take a few seconds just to load up the modules as shown below.

Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member

Now, replace the “Identity” parameter here and run this line.

Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member

And here’s the command to export what we retrieved. Again, replace the <Identity> and <Path> here before running.

Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member | Select DisplayName,PrimarySmtpAddress | Export-CSV "C:\Exports\MembersList.csv" -NoTypeInformation

And, after you running the last line, you should now be able to see the CSV file created under the given path.

Just open it and it should have all the entries as shown below in my example.

Here’s the full script of the same thing. You can run it all at once as well.

### Get O365 Credentials
$Credential = Get-Credential
   
### Provision the session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection
   
### Import session
Import-PSSession $Session -DisableNameChecking
 
### Retrieve Members of Office 365 Group
Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member
  
### Remove the session 
Remove-PSSession $Session

### Export
Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member | Select DisplayName,PrimarySmtpAddress | Export-CSV "C:\Exports\MembersList.csv" -NoTypeInformation

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor. Please do not copy/duplicate the content of the post unless you are authorized by me to do so.

### Get O365 Credentials
$Credential = Get-Credential
   
### Provision the session
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection
   
### Import session
Import-PSSession $Session -DisableNameChecking
 
### Retrieve Members of Office 365 Group
Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member
  
### Remove the session 
Remove-PSSession $Session

### Export
Get-UnifiedGroup -Identity "InternalIT@mantoso.onmicrosoft.com" | Get-UnifiedGroupLinks -LinkType Member | Select DisplayName,PrimarySmtpAddress | Export-CSV "C:\Exports\MembersList.csv" -NoTypeInformation

Office 365 Multi-Geo Part02 (Planning and recommendations)

Multi Geo capability is a little complex topic to wrap up in a single article as Office 365 is a diverse platform with multiple set of business tool offerings. Eventually, Multi-geo configuration can affect to most of these workloads at highest level. That’s where the whole article was split in to 4 stages in order to give you a better and comfortable reading experience with necessary breaks.

Part 1: Get Started

Part 2: Planning and recommendation

Part 3: Configuration

Part 4: Managing and Maintaining

I have gone through the introductory and concept briefing in the part 01 of this article series. Now let’s continue with this 2nd stage which describes planning and recommendations for Office 365 Multi-geo.

Test run – Highly Critical

Try out with a test user/s first. Consider having some test users for each use case as shown below and try out the changes with these users before you roll out in production.

• Have an existing test user who has an active Office365 account with Exchange, SharePoint, OneDrive being used (with available content)
• Try to add the capability for this user only
• Move the user to new PDL
• Move OneDrive content accordingly
• Test the functionality for Exchange, OneDrive and SharePoint

Initial rollout (pilot run, targeted run) – Critical

After you have tested with the above single user, use a small group of people (5 would be ideal) as the pilot run. In most cases this group would be from IT staff as they are well aware of the approach and changes, technically.

every user should have the preferred data location (PDL) defined so that when the new workloads are created (such as those who do not use OneDrive right now perhaps later) they’ll be provisioned in the new PDL. Office365 will use central Location for those users with no PDL defined. The recommendation is, better to set PDL for all users.

Prepare a list of users with their User Principle Name (UPN) and include your Test users, pilot users and other groups batches in order. This will help you in the configuration stage and will make the procedures easily and well tracked.

Considerations for Hybrid Scenarios

Azure AD Connect supports Multi Geo by allowing synchronization of the PreferredDataLocation attribute for user objects from AADC version 1.1.524.0 onwards. However, this may vary for each organization  and if you are fully cloud with no on-premise dependency, please ignore.

The schema of the object type User in the Azure AD Connector is extended to include the PreferredDataLocation attribute. The attribute is of the type, single-valued string.
The schema of the object type Person in the metaverse is extended to include the PreferredDataLocation attribute. The attribute is of the type, single-valued string.

PreferredDataLocation attribute is not synchronized by default. This functionality is currently intended for larger organization (Its eventually the size than the scenario at the moment). You have to plan for an Local AD (on –premise) attribute which will hold the “Office 365 Geo Location” for your hybrid users as there is no PreferredDataLocation attribute by default in on-premise Active Directory. Further, PreferredDataLocation attribute can be managed by PowerShell for Azure Cloud User Objects but not for Synchronized Objects. For synchronized objects (Hybrid), you must make use of AD Connect application.

Before you start on technical configurations, I would highly recommend you to digest these articles and beware of the outcomes:

• Administering a Office 365 Multi Geo environment (By Microsoft)
• User Experience in a Multi Geo Office 365 Environment
• Configuring Synchronized identities for a Multi Geo setup using AADC

stay tuned for the part 03 (technical steps)

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor. Please do not copy/duplicate the content of the post unless you are authorized by me to do so.

Fix: Save List as a Template capability is missing in SharePoint Online

Are you struggling to save a SharePoint Online list as a template just like I did recently? Then you are reading the right post.

Well, worry no more as this article will demonstrate the solution to simply get it fixed.

Usually when you go to your SharePoint list/library settings page, you can easily spot the “save list as a template” link. But if you check the SharePoint online – this is missing.

Now let’s get our good friend back. The reason behind this, is a feature which is disabled by default.

Log in to office.com using your admin credentials and head on to Admin Center –> SharePoint Admin Center –> Classic SharePoint Admin Center (Yes ! at this moment, you really need to go to back to classics, but don’t worry at all, it wont change anything).

Go to “Settings” from the left navigation and scroll down until you see the “Custom Script” section.

Under this section, enable both these features (Allow users to run custom script on personal sites | Allow users to run custom script on self-service created sites)

And hit that “OK” button at the bottom of the page to save the changes. This might take about 24 Hours to affect as it depends on the Office 365 maintenance job schedules.

Run the following PowerShell code to enable it in “Site Collection Level” – (this affects immediately)

##SharePoint Online Admin Center and Site Collection URLs
$AdminCenterURL = "https://TenantNameGoesHere-admin.sharepoint.com/"
$SiteURL="https://TenantNameGoesHere.sharepoint.com/sites/SiteNameGoesHere"
 
##Connecting to SPO Service
Connect-SPOService -url $AdminCenterURL -Credential (Get-Credential)
 
##Enable the feature
Set-SPOSite $SiteURL -DenyAddAndCustomizePages $False

It will prompt you to provide your Office 365 SharePoint Admin credentials for the 3rd command.

Last line is for the real job !

That should immediately affect the target site collection.

Now if you head back to the list/library settings page, you will immediately notice the feature is back.

DISCLAIMER NOTE: This is an enthusiast post and is not sponsored by Microsoft or any other vendor. Please do not copy/duplicate the content of the post unless you are authorized by me to do so.