Can a SharePoint Site Be an Office Trusted Location? Macros, Trusted Sites and Essential Eight

I’m partway through an Essential Eight project, and the Office macro control has turned out to be the fiddliest part of it. The plan sounded simple enough. Roll out the Microsoft 365 Apps security baseline to everyone with macros blocked, then give the small group of people who still rely on legacy spreadsheets a separate policy: macros allowed, but only from our SharePoint environment and our mapped Z: drive.

The file share side is well documented. SharePoint is not. When I went looking for how to make a SharePoint site a trusted location, I found four different answers. Add the site to Trusted Sites. No, that doesn’t work, use Trusted Locations. No, Trusted Locations only take file paths. Actually it works if you type the https address exactly. Most of those answers are partly right, which is why the topic is so confusing.

After working through Microsoft’s documentation and starting to test it in our pilot, the explanation turned out to be fairly simple. Office checks macros at two separate gates, most advice only deals with one of them, and the Microsoft 365 Apps baseline can switch off the trusted locations you’re depending on without making it obvious. This article walks through all three, and how to tell which one is stopping your macro.

Start with the banner

Before changing any policy, look at what the user actually sees when they open the file. The message tells you a lot about which control is blocking the macro.

That third row catches people out. If your policy is Disable all without notification, which is a common choice in Essential Eight builds, there’s no message to tell the user why nothing happened. To them, the spreadsheet is just broken.

If you see the red bar, check whether the file is carrying Mark of the Web. In PowerShell, read the hidden zone information stored alongside the file:

Get-Content -Path .\Budget.xlsm -Stream Zone.Identifier

If it returns ZoneId=3, Windows has marked the file as coming from the Internet zone. If you get an error saying the stream doesn’t exist, the file has no Mark of the Web, and the red bar is coming from somewhere else, such as a network share that Windows treats as an internet location.

Office has two gates, not one

The banners line up with two separate checks Office makes before it runs a macro. Keeping them apart in your head makes everything else in this article easier to follow.

The first gate asks whether the file came from the internet. Windows tags downloads and email attachments with Mark of the Web, and since 2022 Office blocks macros in those files by default. The Microsoft 365 Apps baseline enforces it with the policy Block macros from running in Office files from the Internet. This is the red bar.

The second gate asks whether macros are allowed at all. That’s your VBA Macro Notification Settings policy (in Excel it’s just called Macro Notification Settings), along with any trusted locations and trusted publishers you’ve configured. A file with no Mark of the Web still has to get past this gate.

Microsoft documents the order Office works through (Microsoft Learn: Macros from the internet are blocked by default). A file in a trusted location opens with macros enabled. If it isn’t in one, signed macros from a trusted publisher are enabled. If neither applies, your macro policies decide.

So trusted locations and trusted publishers get you through both gates at once. Everything else people suggest for SharePoint, such as Trusted Sites, the Unblock checkbox or Open in Desktop App, only deals with the first gate. If your second gate is set to disable macros, those fixes on their own won’t make a macro run.

Trusted Sites and Open in Desktop App

The most common advice is to put your tenant in the Trusted Sites zone, and Microsoft does document it. You use the Site to Zone Assignment List policy to add https://yourtenant.sharepoint.com, and https://yourtenant-my.sharepoint.com for OneDrive.

That stops files downloaded from SharePoint being treated as internet files. Windows only adds Mark of the Web for the Internet and Restricted Sites zones by default, and Microsoft notes that a file recorded as coming from Trusted Sites isn’t blocked by default. In other words, it gets rid of the red bar.

It doesn’t touch your macro settings. If your policy disables macros, a file from a trusted site still has its macros disabled. I suspect that’s why so many forum threads end with “I added it to Trusted Sites and it still doesn’t work”. The person had already locked down the second gate.

There’s a wider cost too. Trusted Sites gives the whole site elevated trust in Windows, not just in Office. And as Microsoft points out, it doesn’t change who can upload files. Anyone who can add a file to that site can add a macro-enabled file that skips the internet block.

Opening a file with Open in Desktop App, or through the OneDrive sync client, has the same effect on the first gate. Microsoft confirms neither method adds Mark of the Web. The second gate still applies.

Can an https SharePoint address be a trusted location?

This is where the forum answers disagree most. Microsoft’s trusted locations documentation gives the most useful clue: web folders can be trusted locations, but only ones that support WebDAV or the FrontPage Server Extensions RPC protocol are recognised (Microsoft Learn: Trusted Locations for Office files). Admins on Microsoft’s own community forum have reported SharePoint addresses working as trusted locations, but only when the https path was typed in exactly as Office sees it, and even then not every time (Microsoft Tech Community: Excel Trusted Locations and OneDrive/SharePoint).

So it can work, but two things make it fragile.

First, an https web folder isn’t on the local device, so Office treats it as a network trusted location. That only works if network trusted locations are allowed, which is exactly the setting the baseline turns off. More on that shortly.

Second, the path has to match. The same library can be reached through the site address, the library address, a Teams channel’s files tab or a sharing link, and they don’t all look the same to Office. If the file opens from an address that doesn’t match your trusted location, including whether subfolders are trusted, the macro is blocked.

Find out which path Office is actually using

When a trusted location isn’t being honoured, the first thing I’d check is where Office thinks the file lives. In the open file, go to File > Info. The location shown under the file name is the path Office is using, and Copy path gives you the exact string. If it starts with https:// when you were expecting a local folder, or the other way round, you’ve found your problem.

Compare that string with the trusted locations listed under File > Options > Trust Center > Trust Center Settings > Trusted Locations. Locations deployed by policy appear in their own section there, so you can confirm the policy actually arrived on the device.

Synced libraries as a local trusted location

There’s another option that sidesteps network trusted locations completely. Sync the SharePoint library to the user’s device with the OneDrive client, then make the local synced folder the trusted location.

It has two genuine advantages. Files brought down by the sync client don’t get Mark of the Web, so they’re through the first gate. And because the synced folder is a local path, it works even with network trusted locations turned off. The trusted location policy accepts environment variables, so one path can cover every user:

%USERPROFILE%\Contoso\Finance - Macros

The folder name is built from your organisation’s name and the site and library names, so check it on a synced device before you deploy it.

The weakness is the path-matching problem again, in a sneakier form. If the user opens the file from the synced folder in File Explorer, Office sees the local path and trusts it. If they open the same file from Teams, the SharePoint website or a link in an email, Office sees the https address instead, and the local trusted location doesn’t apply. The same spreadsheet works on Monday and fails on Tuesday, depending on how it was opened. The File > Info check above will show you which one happened.

It’s workable if users open macro files from the synced folder, ideally through a desktop shortcut that points there. Just expect a support call or two while people get used to it.

Library permissions become your security boundary

Whichever SharePoint option you choose, think about what you’ve just created. Anyone with edit rights to that library can add a file, and it will sync into a folder Office has been told to trust. Microsoft makes the same point about Trusted Sites: trusting the location doesn’t change SharePoint permissions, so controlling who can add files is up to you.

For macros, that means a dedicated library, read-only for the people who use the macros, with edit rights limited to whoever reviews them. Pointing a trusted location at a general team library where everyone can upload isn’t a control. It’s a bypass.

The baseline can switch your trusted locations off

This is the part I’d most want another admin to know before they start, because it fails without any obvious error.

Microsoft recommends setting Allow Trusted Locations on the network to Disabled as part of the Microsoft 365 Apps baseline. It’s easy to read that as “stop users adding their own network locations”. It goes further. Disabled blocks network trusted locations entirely, including ones the admin has configured, for example through the Trusted Location #1 policy (Microsoft Learn: Trusted Locations for Office files).

Now apply that to a typical rollout. The baseline goes to every user. The trusted locations policy goes to the macro users. A mapped drive like Z: is a network share, and an https SharePoint path is a web folder, so both count as network locations. A macro user who receives both profiles gets one policy saying network trusted locations aren’t allowed, and another saying trust Z:. The trusted location will show up in the Trust Center, and the macros will still be blocked.

So macro users need Allow Trusted Locations on the network set to Enabled, and they mustn’t receive the baseline’s Disabled value as well. When two Intune profiles set the same setting differently, Intune reports a conflict, and a conflicted setting may not apply at all. There are two sensible ways to handle it:

• Duplicate the baseline for macro users with that one setting changed, and exclude the macro group from the main baseline. It’s the tidiest result, but you now have two baselines to keep in step when Microsoft updates them.
• Take the setting out of the baseline profile and manage it in the Settings Catalog instead, Disabled for most users and Enabled for the macro group.

Either way, keep Allow mix of policy and user locations disabled, as the baseline recommends, so only locations you define by policy count and users can’t add their own.

Check what a device actually received

Intune’s per-setting status in the admin centre is the first place to look for conflicts. On the device itself, the policy values for Excel are written under the user’s policy key:

HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\Excel\Security\Trusted Locations

Look for the AllowNetworkLocations value. If it’s 0, the policy is blocking network trusted locations for that user, whatever the Trust Center shows. If it’s missing altogether, the policy hasn’t been applied, and the user’s own Trust Center checkbox decides, which isn’t something you want to leave to chance in an Essential Eight build. Word and PowerPoint have their own equivalent keys.

Test all of this on a pilot device that receives exactly the same set of profiles as a real macro user. A trusted location that’s listed but not honoured looks perfectly fine until someone opens a file.

Trusted macros aren’t scanned by default

One more setting matters if you use trusted locations, and it ties directly to Essential Eight, which requires macro antivirus scanning at every maturity level.

Office can report macro behaviour to your antivirus at run time through the Antimalware Scan Interface. The Macro Runtime Scan Scope policy decides which documents that covers. When it’s set to Enable for low trust documents, scanning is skipped for files opened from a trusted location, for trusted documents, and for macros signed by a trusted publisher. Only Enable for all documents removes those exceptions (DISA STIG V-223284: Macro Runtime Scan Scope). Security researchers at Outflank found the default scope left trusted locations out of scanning, and showed how that could be abused (Outflank: Bypassing AMSI for VBA).

Think about what that means. The files you’ve deliberately allowed to run macros are the very ones a low-trust scope won’t scan. If you’re using trusted locations, set the scope to Enable for all documents. The policy’s own description warns it can slow down affected VBA projects, so make sure your heaviest macro users are in the pilot.

How the options compare

Putting it together, this is how each approach behaves for macro files kept in SharePoint:

The last row is the one Microsoft recommends, and it’s easy to see why. A trusted publisher doesn’t care where the file lives. SharePoint, a file share, a synced folder or a local copy all behave the same, because the trust comes from the signature rather than the path. The one documented exception is Excel add-in files that carry Mark of the Web, where Microsoft says a trusted publisher signature isn’t honoured (Microsoft Learn: Macros from the internet are blocked by default).

What I’m doing

Signing legacy code that nobody has maintained in years is a project in its own right, so for now trusted locations are the pragmatic choice. This is the design I’m working towards:

• The baseline for everyone, with macros from the internet blocked and network trusted locations disabled.
• A separate profile for the macro group only, which allows network trusted locations and defines a small number of narrow ones, with the conflicting baseline setting handled so the two profiles don’t fight.
• Dedicated, locked-down locations in SharePoint and on the file share, where only the people who review macros can write.
• Macro Runtime Scan Scope set to Enable for all documents, so trusted macros are still scanned.
• Maintained macros moving to a trusted publisher over time, which removes the dependence on paths altogether.

We’re not targeting Maturity Level Three for this control, where only sandboxed, trusted-location or trusted-publisher macros can run and only privileged reviewers can write to trusted locations (ASD: Essential Eight maturity model). But restricting who can write to a trusted location makes sense at any level. Without it, you haven’t built a control. You’ve documented a way around one.

Checklist: macros stored in SharePoint

• Read the banner first: red means Mark of the Web, yellow means users can choose, nothing at all means macros are disabled without notification.
• Check a suspect file’s zone with Get-Content -Stream Zone.Identifier.
• Don’t expect Trusted Sites, Unblock or Open in Desktop App to run macros if your policy disables them.
• Use File > Info to see the exact path Office is using, and compare it with your trusted locations.
• If you trust a synced folder, make sure users open macro files from the synced path.
• Check whether your baseline disables Allow Trusted Locations on the network, and resolve the conflict for macro users.
• Confirm AllowNetworkLocations on a pilot device that receives the same profiles as a real macro user.
• Keep Allow mix of policy and user locations disabled.
• Use a dedicated macro library or folder, with write access limited to the people who review macros.
• Set Macro Runtime Scan Scope to Enable for all documents.
• Plan to move maintained macros to a trusted publisher.

So, can a SharePoint site be an Office trusted location? Sometimes, if the path matches and the baseline lets it. The more useful lesson is that a trusted location is only one of two gates, the baseline may be switching it off for you, and by default the macros you trust most are the ones Office scans least. Once you know those three things, macros from SharePoint stop being a guessing game.

Sources

• Microsoft Learn: Macros from the internet are blocked by default in Office
• Microsoft Learn: Trusted Locations for Office files
• Microsoft Tech Community: Excel Trusted Locations and OneDrive/SharePoint
• DISA STIG V-223284: Macro Runtime Scan Scope
• Outflank: Bypassing AMSI for VBA
• ASD: Essential Eight maturity model

Daniel Lutton

From my early days on the helpdesk through roles as a service desk manager, systems administrator, and network engineer, I’ve spent more than 25 years in the IT world. As I transition into cyber security, my goal is to make tech a little less confusing by sharing what I’ve learned and helping others wherever I can.