Connect-PnPOnline in Azure Runbook using a SSL certificate from Azure Key Vault

When working with multiple Automation Accounts (AAs) and Runbooks in Azure, you typically need to upload an SSL certificate to each individual Automation Account. This can quickly become inefficient and difficult to maintain.

A better solution is to use Azure Key Vault — a secure centralized service for managing cryptographic keys and certificates. By storing your SSL certificate in Azure Key Vault, you can easily access and use it across all your Runbooks without the need to upload it repeatedly to each Automation Account.

Steps to Implement:

1. Import Required Modules

   • Import the Az.KeyVault module in your Automation Account.

   • Import the PnP.PowerShell module (recommended version: 2.12.0) in your Automation Account.

2. Upload SSL Certificate

   • Upload a valid SSL certificate to Azure Key Vault.

3. Rigister Azure App

   • Upload the certificate to the app

4. Access Certificate in Runbook

• Use the following PowerShell script to connect to SharePoint Online via PnP PowerShell, using the SSL certificate stored in Key Vault.

# Disable update check for the current PowerShell session

$env:POWERSHELL_UPDATECHECK = "Off"

### Parameters ###

$tenantID = <tenant id>

$clientID = <azure app client id>

$KeyVaultName = <keyvault name="">

$CertName = <certificate name uploaded to key vault>

$subscriptionId = <your subscription ID>

$Url = <spo site url>

### END Parameters ###

# Authenticate to Azure using the managed identity of the Automation Account

Connect-AzAccount -Identity

Set-AzContext -Subscription $subscriptionId

#Retrive the certificate

$secretSecureString = Get-AzKeyVaultSecret -VaultName $KeyVaultName -Name $CertName

$secretPlainText = ConvertFrom-SecureString -AsPlainText -SecureString $secretSecureString.SecretValue

$secretPlainText.Substring(0, 4)

# Connect using thumbprint

Connect-PnPOnline -Url $Url -ClientId $clientID -CertificateBase64Encoded $secretPlainText -Tenant $tenantID

Microsoft Loop - What you need to know?

Microsoft Loop is a cloud-based collaboration tool within the Microsoft 365 ecosystem that enables real-time teamwork and project management through shared workspaces and interactive components.

It is designed to enhance collaboration among teams by providing a unified platform where users can create, share, and edit content in real-time. It integrates seamlessly with other Microsoft 365 applications like Word, Excel, Teams, and Outlook, allowing for a fluid workflow without the need to switch between different tools.

Key Features:

• Loop Components: These are interactive elements such as lists, tables, and notes that can be embedded in various Microsoft applications. They allow multiple users to collaborate on the same content simultaneously, ensuring that everyone has access to the most up-to-date information. 
• Real-Time Collaboration: Changes made to Loop components are reflected instantly across all platforms where they are used, minimizing confusion and ensuring that all team members are on the same page.
• Workspaces: Microsoft Loop allows users to create dedicated workspaces for projects, where they can organize tasks, track progress, and manage resources in one centralized location.
• Integration with Microsoft 365: Loop is designed to work seamlessly with other Microsoft tools, making it easy to incorporate data from Excel, documents from Word, and communications from Teams into a single collaborative environment.
• Templates and Customization: Users can utilize pre-built templates to streamline their workflow and customize their workspaces to fit their specific project needs.

Benefits of Using Microsoft Loop:

• Enhanced Productivity: By centralizing communication and project management, Microsoft Loop helps teams work more efficiently and reduces the time spent switching between different applications.
• Improved Clarity: With all project-related information in one place, teams can maintain clarity and focus on their goals without the distraction of fragmented tools.
• Flexibility: Loop's design allows for easy adjustments and updates, making it suitable for dynamic work environments where requirements may change frequently.

Getting Started with Microsoft Loop

To start using Microsoft Loop, users can log into their Microsoft 365 account and access the Loop application. From there, they can create workspaces, invite team members, and begin collaborating on projects using Loop components.

In summary, Microsoft Loop is a powerful tool for modern teamwork, designed to facilitate collaboration and streamline project management within the Microsoft 365 ecosystem. Its real-time capabilities and integration with existing Microsoft tools make it an essential resource for teams looking to enhance their productivity and communication. 

Microsoft Flow (delete files and folders from a document library)

Do you know how to delete files and folders from SharePoint Online document library using Microsoft Flow?

When I received the task to delete files and folders older than 30 days from SharePoint Online with Microsoft Flow I was thinking this will be an easy one, but I was wrong 😒

I used Recurrence as a trigger (run every 30 days), Get items, Apply to each and Delete item actions to select all files and folders older than 30 days:

But there is a problem with this configuration if you have folders with files in the document library. Get items will collect all folders and files, each and every one of them are having unique internal ID. It will delete folders with the files than all other files that are older than 30 days and it will try to delete the files from the folder(s) which are already deleted when the folder(s) has been deleted and it will throw and error: 404 item not found. The flow will fail but all the files and folders will be deleted.

How to avoid this error? First we need to get all the files and all the folders older than 30 days.

Get items (only files) has the following configuration (first you need to enable the Filter Query from Advanced parameters):

• Created date should be less than addDays(utcNow(), -30, 'yyyy-MM-dd') and FSObjType equal 0 (0 is fo files and 1 is for folders)

After you have all the files with created date older than 30 days you apply for each action and delete them.

Last step is to select only folders older than 30 days and delete them, the setup looks like the screenshot below:

Hope this article help you solve this issue. Please share your feedback.

[ATTENTION] Be careful with Copy-SPSite and Colligo!

Analysis of Copy-SPSite and Colligo

Folks be careful when using Colligo in your organization, careful with Copy-SPSite. Otherwise you may end-up with several issues regarding Colligo is unable to sync some sites:

According to the description provided by Microsoft Copy-SPSite:

Use the Copy-SPSite cmdlet to make a copy of a site collection from an implied source content database to a specified destination content database.The copy of the site collection has a new URL and a new SiteID. When you have database snapshot capabilities on a computer running SQL Server, a temporary snapshot of the source database is created for the duration of the copy to prevent any data changes during the copy process. If you do not have database snapshot capabilities on the server running SQL Server, you can back up the source and restore it to the destination to get the same result.

Copy-SPSite is creating a new site with new URL and a new SiteID, but the WebID is the same. The WebId of the original site and cloned one are the same. Yes that is true the WebID stay the same and this is what is confused for Colligo. Because Colligo relays on WebID as unique identifier.

How to get that? Here are the steps:

1. Find out which is the "Storage Location" for Colligo Briefcase. Go to View->Options

2. Go to this location, you will see "Sites.db" file:

3. Open the file with "DB Browser for SQL Lite", but first close Colligo and open Webs table.

Under Server Name is the WebID of the site and if you go to SharePoint server you will see it is the same GUID provided by Get-SPWeb. You can compare both WebID's of the sites, original ones and the one created with Copy-SPSite ... they are the same. Of course the logic that is used by Colligo is not allowing you to synchronize sites created with Copy-SPSite.

Interesting is that OneDrive for Business does not have this problem and synchronization is working fine. Why? Because Microsoft use a different logic for unique identifier. Normally the OneDrive for Business local cache access database is located here: C:\Users\Pavlov Aleksandar\AppData\Local\Microsoft\Office\15.0\OfficeFileCache\CentralTable.accdb. Open the DB in Access and than open MasterFile table.

As you can see the logic used by Microsoft for storing the information about the site is OneDrive for Business is GUID/relative path, in this case STID${CB935F8C-1D73-4BEC-B54E-D2E23D013CDC}/sites/loadtest22.

At the beginning to me it was also too strange to believe that WebID of two or more sites could be the same and in my opinion Microsoft changed this logic a while ago. But this may be a huge problem for companies using Colligo for file synchronization between SharePoint and local PC if somebody from Colligo do not pay attention to this problem.

Hope you will find this post helpful.

If so please share it.
-----------------------------------------------------------------------------

Deploying new solutions in SharePoint 2013 takes too much time

One of my customers is having TEST, QA and PROD environment. Where QA and PROD are equal (same amount of WFE servers (in this case 2 WFE servers), same configuration and settings, in case PROD is down we can switch to QA in a minutes and it will be the same). 

What happened: since a while deploying a new version of the solutions takes too much time, around 1.5 hours, compared with the past and QA - 15 min. It is horrible to stay in front of the monitor and wait the slow deployment to finish. Yes, it finished without errors but waiting was killing me. I wasn't aware of any changes in infrastructure level that can lead to this slowness. 

I have to mention that there wasn't any slowness on the sites and locally on the server everything was working fine, no high memory or CPU consumption, nothing that could tell you way the deployment is so slow. 

In QA environment the deployment time was normal, around 15-20 min. So this also exclude the .wsp's from the suspected root cause.

Start looking for a solutions in internet and I was surprised how many article and post related to SharePoint slowness, sites are loading too slow and etc. but nothing related to my case. That's why I decided to write this post because I was able to find the root cause.

Incidentally I opened two RDP sessions to the WFE servers and size of the windows was different, guess what:

Can you see where is the problem from the picture above?

Yes, that is right. 

Both servers are having different time, the second server was 5 min behind the real time.

OK potentially I thought this could be the reason and went to check the time zones, but on both servers the time zones were correct, but not the time. After a while I checked the services and found out that "Windows Time" services on both machines were set to disable. Check how it is QA and there the services were running. Enabling the services and set them to automatic was the fix I needed.    

In the future if you have a slow deployment make sure you check the "Windows Time" service, set it to automatic and run it.

Hope you will find this post helpful, if so please share it.

Enjoy!

Proactively increase the max site storage quota

With this post I would like to show you my way of proactively increasing the site storage quota. My task was to eliminate the need of users to request site storage quota increase. I wrote a small PowerShell script which you can download from here. 

The script will check the current site size, compared it with the max site storage quota assigned and if the current occupied storage is over 80% the site is a potential candidate for increase the max site storage quota. Of course you can change this value to whatever it is suitable for you. You will be able also the see the assigned quota template, if you have custom quota created or it will be shown as "No Template Applied". That means that "Individual" quota has been assigned, but you will still need to increase the assigned max storage size. The Read/Only sites are displayed in the report only if the warning quota is reached, in my case the warning quota is 1024MB, so you have to switch back to Write/Read before you change the quota. Inside the script if it is necessary you can change the warning quota limit (please read the hints/comments inside the script). 

The output looks like:

The output will be stored in a file and this file will be sent via email. Please check the settings in the script to understand to whom to send the report. Reports older than 2 days will be removed from the folder.

So give it a try and let me know if it works or not.

Enjoy!

Could not retrieve profile schema from server

How many of you faced this strange behavior of SharePoint 2013 designer?

When trying to use User Profile as a Data Source in a workflow. Something similar to the screen shot below:

Try looking in google did not give me the expected solution. A lot of people suggested to grant permissions of the web app pool account to the search service database. But what actually that means? I granted read permissions to all search related databases, but as you expect the problem was still persisting.

So many articles were referring to Search I was start thinking that the problem is really there. But all of the explanations were not exactly correct. The right way to make it working is:

1. Go to Central Admin
2. Go to Manage Service Applications
3. Select Search Service Application, but don't click on it
4. Select Administrators
5. Add the application pool account of the web application
6. Grant "Read (Diagnostic Pages Only)" permission and click OK

Afterwards everything should work normally.

For now I don't have exact explanation why these settings are necessary. Will try to find out and post here as a comment.