A storage account that is created as part of an Azure Function App out of the box has some configuration tradeoffs that can be considered vulnerabilities. Let’s look at what can be changed to improve the security of the storage account without affecting the functionality of the Azure Function App.
Public (anonymous) Access
Storage account blob anonymous (public ) access should be disallowed. Though having this Enabled does not allow anonymous access to blobs automatically, it is recommended to disable public access unless the scenario requires it. Storage account that support Azure Function App is not supposed to be shared anonymously.
Navigate to your Storage Account at Azure portal, Settings/Configuration, then for “Allow Blob anonymous access” select “Disabled”:
MS: “When allow blob anonymous access is enabled, one is permitted to configure container ACLs to allow anonymous access to blobs within the storage account. When disabled, no anonymous access to blobs within the storage account is permitted, regardless of underlying ACL configurations. Learn more about allowing blob anonymous access“.
Storage account key access
This is also considered as vulnerability and it is recommended to disable Storage account key access.
NB: (Jan 2025) It is not possible to Set “Allow storage account key access” to Disabled if you are using Consumption or Elastic Premium plans on both Windows and Linux without breaking the function app. For other plans – it’s possible but requires some work.
Set “Allow storage account key access” to Disabled:
MS: “When Allow storage account key access is disabled, any requests to the account that are authorized with Shared Key, including shared access signatures (SAS), will be denied. Client applications that currently access the storage account using Shared Key will no longer work. Learn more about Allow storage account key access“
The problem is it seems like the function app ootb is configured to use key access, so just disabling storage account key access breaks the function app.
For the function app to adopt this new setting, you’d need:
provide access for the function app to the storage account
reconfigure the function app for authorization via Microsoft Entra credentials
Below is the my guide on how to connect Azure Data Factory to SharePoint and how to deal with connection error code 23201 “Failed to get metadata of odata service, please check if service url and credential is correct and your application has permission to the resource“
Scenario
You are configuring Azure Data Factory pipeline. You want to connect to SharePoint List as a data source.
Update (Nov 2024): Azure Data Factory V2 supports connection to SharePoint using certificate. So we do not need to provide legacy ACS permissions to the app registration. Instead we’d provide just Sites.Selected API permissions and use certificate to authenticate ADF to to Microsoft 365.
Below is legacy (classic) approach connecting ADF to SharePoint
To establish connection to SharePoint site you need to provide Site Url, tenant Id, service principal Id and service principal key:
Service principal
Service principal here could be
SharePoint app-only service principal registered at SharePoint site via appregnew.aspx
Azure registered app (app registered in EntraId)
In both cases you get “service principal” – which is App Id or Client Id and “service principal key” which is app secret (client secret).
Note: in Sep 2023 Microsoft implemented update to all Microsoft 365 tenants. According to the update, by default only tenant administrators can create or update ACS service principal by default. If site collection admin starting from Oct 2023 can register SharePoint app-only spn via appregnew.aspx or provide ACS-based permissions via appinv.aspx – that means tenant admins switched this back.
So, if registering a new SharePoint app-only service principal is still available for your tenant – you can get service principal Id and key from SharePoint via appregnew and/or provide ACS-based permissions via appinv – and there should be no problem connecting to SPO list from ADF (June 2024), but please review special note below.
If a site collection administrator or owner tries to register app in SharePoint with appregnew.aspx or provide permissions to the app with appinv.aspx – and he/she gets: Your SharePoint tenant admin doesn’t allow site collection admins to create (update) app permissions. Please contact your SharePoint administrator:
that means registering service principal in SharePoint is disabled. In this case Microsoft recommend using Azure application registration – with MS Graph API Sites.Selected and SharePoint Sites.Selected API permissions configured, consented by tenant admin and with access to specific SharePoint site provided by SharePoint admins (refer to this article for more details).
Issue
If you obtained service principal Id and key as Azure Registered App – connection to SharePoint site from Azure Data Factory does not always works. When you configure connection and click test – you might get an error:
Failed to get metadata of odata service, please check if service url and credential is correct and your application has permission to the resource. Expected status code: 200, actual status code: BadRequest, response is :
...
<title>Request Error</title>
<body>...
<p class="heading1">Request Error</p>
<p>The server encountered an error processing the request. See server logs for more details.</p>
...</body>
Reason
So the issue above is a combination of two controversial circumstances:
Microsoft discourages using SharePoint app-only service principals and disabled ability for site owners to register SharePoint app-only service principals and provide ACS-based permissions in favor of Azure Registerd Apps with Sites.Selected based permissions.
Azure Data Factory still require ACS-based permissions (Upd: June 2024 – still true)
Solution
If Microsoft disabled ability for site owners to to provide ACS-based permissions for the app – that does not mean it is fully disabled. It turns out – SharePoint admins are still able to register SharePoint app-only principals and provide ACS-based permissions.
The recommended steps are:
register Application in Azure (in EntraId, not in SharePoint) to get App (client) Id this could be done by user from Azure App Registrations (or, if this ability is disabled by tenant admin – there must be a way for users to request an application registered in Azure)
provide to this App Id ACS-based permissions at the target SharePoint site via appinv.aspx – this is done by a person who got at the same time two roles
SharePoint admin role enabled and
The specific site collection administrator permissions so if your role is a regular user or developer (not an admin) – you’d request this service from your admins
provide Sites.Selected permissions for the App to the target Site again, this is something you’d need to request from your admins – tenant admin should be able to provide admin consent to SharePoint and Graph API Sites.Selected permissions for your app and SharePoint admin should be able to provide actual permissions for the app to the site.
Technical Details
(as per June 2024 – still true) Actually, the only you need is to provide any ACS-based access for the application. Even to another site, web or list. You can also remove this just provided ACS-based access. It seems like the moment you click “Trust” when you provide access via AppInv.aspx – something is triggered in Microsoft Identity Management token issuing mechanics so Azure Data factory connection starts working (assuming Sites.Selected access was provides).
Surely connection will work if you provide only ACS-based permissions (with no Sites.Selected permissions), but this is what we all want to avoid by any means.
More fun! Connection to entire site will start working even if you provide SharePoint app-only (ACS-based) permissions to some specific list. Though later, when you try to ingest data – you will be able to ingest only this list data.
Environment this is tested:
Powershell module used to enable/disable 16.0.24120.12000 Microsoft.Online.SharePoint.PowerShell
Microsoft implemented Sites.Selected API permissions for Azure registered apps in 2021-2022 as a preferred way to access specific SharePoint site with application credentials. Microsoft recommend using Azure registered apps instead of SharePoint App-Only service principals and “softly” push developers toward Azure registered apps. Microsoft recently (Aug-Sep 2023) implemented an update and pushed it to all existing Microsoft 365 tenants – so that ability for site admins to register service principals at sites is turned off by default.
So starting Aug-Sep 2023 site owners/admins cannot register and provide ACS-based permissions for apps to their SharePoint sites.
In 2024 Microsoft announced EOL for SharePoint app-only spns and ACS-based permissions.
Special note
This article is written in 2023 with the sole purpose to help you resolve the issue. And it all is still true (validated in June 2024). But! I assume that sooner or later (before April 2026) Microsoft will address it’s own issue and update Azure Data Factory so ADF will be accepting permissions provided via Sites.Selected only. That is why – at the moment – I strictly recommend:
Use Azure Registered App (not a SharePoint app-only spn)
Get both types of permissions for this app:
modern – Sites.Selected SharePoint and Graph API permissions
old/classic – ACS-based permissions
if so – your data pipeline should continue working smoothly when MS implement modern authentication in ADF.
The idea is to deploy FastAPI to Azure Functions the way auto-generated interactive documentation would be public, but actual API would be protected. Pamela solved it with Azure API Management and subscription keys:
“One of my goals was to have the documentation be publicly viewable (with no key) but the FastAPI API calls themselves require a subscription key. That split was the trickiest part of this whole architecture, and it started at the API Management level.”
Update (May 2023): You can use Get-PnPAzureACSPrincipal to returns the lists of all Azure ACS principals installed in your Tenant including subsites.
Scenario
Developers in the organization can use both – Azure Apps and SharePoint Apps to work with SharePoint sites in their “daemon” applications.
It is recommended to use Azure apps so, you want to know – what are SharePoint Apps registered and their owners, who registered SharePoint Apps. Eventually you would disable SharePoint Apps-only principal but before that you’d move Devs from SP-App-only to Azure App (see Disable Custom App Authentication).
(SharePoint App-only service principals aka SP-App-Only are SPN or App registered from within SharePoint using AppRegNew.aspx system page).
One of the approaches is to track Apps/Owners with Unified Audit Log
returns events with operation = ‘Add service principal.’ Nice, but… if an app was registered in Azure – event contains an UPN under UserIds property:
Unfortunately, in case with registering app in SharePoint, an audit log event will be like:
i.e. UserId registerd is “spo_service@support.onmicrosoft.com”, so we do not know who registered a SharePoint-only app
In theory – we could use events recorded immediately before and after “Add service principal” event to track a user and site who has registered a SharePoint-only app… But for me it seems like too complicated for automation.
Instead we can do simple search through audit log for events “AppRegNew.aspx page visited”. This gives us a good approximation of who registered SP-App-only principal. Worst scenario – we reach more people than we really need (including those who started registering sp-app-only but did not complete) but all of them would be definitely our target auditory.
this would give you all users who loaded “/_layouts/15/appregnew.aspx” page
Update: Sites.Selected API MS Graph permissions was introduced by Microsoft in 2021. It was a good move towards site-level development, but still developers were limited with only what MS Graph API provides for SharePoint dev. So devs had to use AppInv.aspx at site level to provide ACS permissions to their apps to be able to use SharePoint CSOM and REST APIs. Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So now devs should be fully happy without ACS-based permissions.
You are a software developer. Your company uses Microsoft Office 365 (SharePoint, Teams etc.). The need is to work with a specific site collection programmatically (from code – Python, C#, Java, PowerShell, JavaScript etc.) – e.g. upload/download documents, update list items, search etc.
The code must run without user interaction (unattended, aka daemon app). Sometimes this is also called “SharePoint Automation”.
The solution is based on a new Graph API feature – Sites.Selected and a classic SP-Only app.
Solution
Register an Azure App and configure it as usual. Select API Permissions blade and add two permissions: – Microsoft Graph -> Applications Permissions -> “sites.selected” – SharePoint -> Applications Permissions -> “sites.selected“
Request “Grant admin consent” from a tenant/global admin
Request SharePoint admin to run PowerShell code (e.g. this one) to assign proper permissions to your azure app for a specific site collection (consider site owner consent)
(optionally) Provide SharePoint API permissions: (require Site Collection Owner/Admin account) – use https://YourTenant.sharepoint.com/teams/YourSite/_layouts/15/appinv.aspx to add SharePoint API permissions to your app. E.g. full control permissions to site collection would be
Sites.Selected API MS Graph permissions was introduced by Microsoft in 2021. It was a huge step forward, but still devs were limited with MS Graph API against SharePoint. So devs had to use AppInv at site level to provide ACS permissions to their apps to use SharePoint CSOM and REST APIs. Recently Microsoft introduced Sites.Selected SharePoint API permissions for registered Azure Apps! So now devs should be fully happy without ACS-based permissions AppInv.aspx. (See more here on disabling SP Apps Only SPNs)
You run some PnP PowerShell code unattended e.g. daemon/service app, background job – under application permissions – with no user interaction. Your app needs to connect to SharePoint and/or Microsoft Graph API. Your organization require authentication with a certificate (no secrets). You want certificate stored securely in Azure Key Vault.
Solution (Step-by-step process)
Obtain a certificate (create a self-signed or request trusted)
In Azure where you have Microsoft 365 SharePoint tenant
Create a new Registered App in Azure; save App (client) id, Directory (Tenant) Id
Configure App: add MS Graph and SharePoint API application (not delegated) permissions
Upload the certificate to the app under “Certificates & secrets”
In Azure where you have paid subscription (could be same or different)
Create an Azure Key Vault
Upload certificate to the Key Vault manually (with GUI)
While you develop/debug your custom daemon application at your local machine
Provide permissions to the Key Vault via Access Control and Access Policies to your personal account
Connect to Azure (the one where your Key Vault is) running Connect-AzAccount – so your app can get a Certificate to authenticate to SharePoint Online
For your application deployed to Azure (e.g. Azure Function App )
Turn On managed identity (Your Function App -> Identity -> Status:On) and Save; notice an Object (Principal) Id just created
Provide for your managed identity principal Id permissions to the Key Vault via Key Vault Access Policies, so when your daemon app is running in the cloud – it could go to the key Vault and retrieve Certificate
Here is the sample PowerShell code to get certificate from Azure Key Vault and Connect to SharePoint with PnP (Connect-PnPOnline):
# ensure you use PowerShell 7
$PSVersionTable
# connect to your Azure subscription
Connect-AzAccount -Subscription "<subscription id>" -Tenant "<tenant id>"
Get-AzSubscription | fl
Get-AzContext
# Specify Key Vault Name and Certificate Name
$VaultName = "<azure key vault name>"
$certName = "certificate name as it stored in key vault"
# Get certificate stored in KeyVault (Yes, get it as SECRET)
$secret = Get-AzKeyVaultSecret -VaultName $vaultName -Name $certName
$secretValueText = ($secret.SecretValue | ConvertFrom-SecureString -AsPlainText )
# connect to PnP
$tenant = "contoso.onmicrosoft.com" # or tenant Id
$siteUrl = "https://contoso.sharepoint.com"
$clientID = "<App (client) Id>" # Azure Registered App with the same certificate and API permissions configured
Connect-PnPOnline -Url $siteUrl -ClientId $clientID -Tenant $tenant -CertificateBase64Encoded $secretValueText
Get-PnPSite
PowerShell is our best friend when it comes to ad-hoc and/or scheduled reports in Microsoft 365. PnP team is doing great job providing more and more functionality with PnP PowerShell module for Office 365 SharePoint and Teams.
Small and medium business organizations are mostly good, but for large companies it might be a problem due to just huge amount of data stored in SharePoint. PowerShell reports on all users or all sites might run days… which is probably OK if you run this report once, but totally not acceptable if you need this report e.g. daily/weekly or on-demand.
How can we make heavy PowerShell scripts run faster?
Of course, you start with logic (algorithm) and leveraging full PowerShell functionality (e.g. PowerShell 7 parallelism or PnP batching).
(examples)
What if you did everything, but it still takes too long? You need something like brute force – the closer your code runs to your tenant – the better. What are the option? – Automation account runbook (+workflow) – Azure Function Apps – Azure VM in the region closest to your Tenant
Automation account runbook (+workflow)
Seemed like a good option, but not something Microsoft promotes. Even opposite – automation accounts support only PowerShell 5 (not 7), no plug-ins for VS Code and recently there were messages on some retirement or smth.
Meantime, I tested it – and did not find any significant increasing in speed. In a nutshell, what is behind this service? Same windows machines running somewhere in Azure .
