Recently we started a new pilot project for Microsoft Fabric using D365 F&O (ERP) as the data source utilizing Synapse Link to get it out of Dataverse. If you are familiar with this architecture pattern, you know it can be pretty painful at times. Alas, Fabric Link will not work for us at this time, so I’ll just leave it at that for now. Just know that this problem is specific to a Synapse Link setup.
Previously, Spark 3.4 was not available to use for Synapse Link. That has creating a bit of a panic from people using D365 F&O with Synapse Link, because Spark 3.3 is going out of support on March 31, 2025. I don’t know what the cost of D365 F&O is for most, but I’m pretty sure it’s like a gazillion dollars. Recently I saw people were starting to use Spark 3.4 with D365 F&O and Synapse Link, but they were also having trouble.
Getting around some other issues we’ve been encountering, we were finally able to set up our Synapse Link. The setup screen confirmed we needed to use Spark 3.3.
UPDATE: The UI below now properly says Spark 3.4, but I’m leaving this here in case it happens with another version in the future.
Here’s a close up in case you can’t see it:
The problem was, after I filled out all the other required information, there was nothing in the drop down box for spark. I confirmed on the Azure side that everything was set up correctly and that Synapse and the storage account were seeing each other, but nothing in the drop box.
Now at this point I could drag this post out and tell you all the things I did to try and fix it, but I’m getting a little annoyed at unnecessarily long posts lately, so I just skip to the solution: Spark 3.4 is actually required now.
Once we recreated a spark 3.4 pool, all of a sudden it appeared in the drop down box and we could move to the next screen. Unfortunately right after we got that fixed we ran into a Spark 3.4 bug, but that was fixed and pushed out in about 2 days. Finally we can move on to the Fabric portion of our project.
Note: we did let Microsoft know about erroneous message for 3.3, but as of yesterday it was still showing up when you go to set up a new Synapse Link. Showing up correctly when I checked again on Feb 6th.
If you’ve bought a P1 reserved capacity, you may have been told “No worries – it’s the same as an F64!” (Or really, this is probably the case for any P to F sku conversion.) Just as you suspected – that’s not entirely accurate. And if you are trying to create Fabric shortcuts on a storage account that uses a virtual network or IP filtering – it’s not going to work.
The problem seems to lie in the fact that P1 is not really an Azure resource in the same way an F sku is. So when you go to create your shortcut following all the recommend settings (more on that in a minute), you’ll wind up with some random authentication message like the one below “Unable to load. Error 403 – This request is not authorized to perform this operation”:
You may not even get that far and just have some highly specific error message like “Invalid Credentials”:
Giving the benefit of the doubt – you may be thinking there was user error. There are a gazillion settings, maybe we missed one. Maybe, something has been updated in the last month, week, minute… Fair enough – let’s go and check all of those.
Building Fabric shortcuts, means you are building OneLake shortcuts. So naturally I first found the Microsoft Fabric Update Blog announcement that pertained to this problem: Introducing Trusted Workspace Access for OneLake Shortcuts. That walks through this EXACT functionality, so I recreated everything from scratch and voila! Except no “voila” and still no shortcuts.
Okay, well – no worries, there’s another link at the bottom of the update blog: Trusted workspace access. Surely with this official and up-to-date documentation, we can get the shortcuts up and running.
Immediately we have a pause moment with the wording “can only be used in F SKU capacities”. It mentions it’s not supported in trial capacities (and I can confirm this is true), but we were told that a P1 was functionally the same as an F64 so we should be good right?
Further down the article, there is a mention of creating a resource instance rule. If this is your first time setting all of this up, you don’t even need this option, but it may be useful if you don’t want to add the Exception “Allow Azure services on the trusted services list to access this storage account.” to the networking section of your storage account. But this certainly won’t fix your current problem. Still, good to go through all this documentation and make sure you have everything set up properly.
One additional callout I’d like to make is the Restrictions and Considerations part of the documentation. It mentions: Only organizational account or service principal must be used for authentication to storage accounts for trusted workspace access. Lots of Microsoft support people pointed to this as our problem, and I had to show them not only was it not our problem, but it wasn’t even correct. It’s actually a fairly confusing statement because the a big part of this article is setting up the workspace identity, and then that line reads like you can’t use workspace identity to authenticate. I’m happy to report using the workspace identity worked fine for us once we got our “fix” in (I use that term loosely) and without the fix we still had a problem if we tried to use the other options available for authentication (including organizational account).
After some more digging, on the Microsoft Fabric features page, we see that P SKUs are actually not the same as F SKU in some really important ways. And using shortcuts to an Azure Storage Account that are set using anything but to Public network access: Enabled from all networks (which BTW – is against Microsoft best practice recommendations) is not going to work on a P1.
The Solution
You are not going to like this. You have 2 options. The first one is the easiest, but in my experience very few enterprise companies will want to do this since it goes against Microsoft’s own best practice recommendation: Change your storage account Network setting to: Public network access enabled from all networks.
Don’t like that option? You’re probably not going to like #2 either. Particularly if you have a long time left on your P SKU capacity. The solution is to spin up a F SKU. In addition to your P SKU.And as of the writing of this article, you can not convert a P SKU to an F SKU, meaning if you got that reserved capacity earlier this year – you are out of luck.
In our case, we have a deadline for moving our on-prem ERP solution to D365 F&O (F&SCM) and that deadline includes moving our data warehouse in parallel. Very small window for moving everything and making sure the business can still run on a new ERP system with a completely new data warehouse infrastructure.
We’d have to spend a minimum of double what we are paying now, 10K a month instead of 5k a month, and that’s only if we bought a reserved F64 capacity. If we wanted to do a pay-as-go, that 8K+ more a month, which we’d probably need to do until we figure out if we should do 1 capacity, or multiple (potentially smaller) capacities to separate prod/non-prod/reporting environments. We are now talking in the range of over 40K additional at a minimum just to use the shortcut feature, not to mention we currently only use a tiny fraction of our P1 capacity. I can’t even imagine for companies that purchased a 3-year P capacity recently. (According to MS, you could have bought that up until June 30 of this year.)
Ultimately many companies and Data Engineers in the same position will need to decide if they do their development in Fabric, Synapse, or something else all together. Or maybe, just maybe, Microsoft can figure out how to convert that P1 to an F64. Like STAT.
This error has become the bane of my existence – with multiple configuration setups. Basically, anytime I dared to change the Synapse workspace to not allow all access in the Network settings, I was bound to run into it. Today’s tidbit only addresses ONE way that might solve this error, but it is so maddingly simple that I decided to throw it up on my blog. I’ll save the rant of all the different rabbit holes I’ve been down in the last month for the “BEST PRACTICE SAYS YOU HAVE TO DO THIS <oh except it’s not allowed in these cases and I’m going to bury that information on some small page.>” Hopefully I’ll post all the different ways to address this problem at some point.
First let’s look at the error message that you see when you open up your Synapse workspace. “Failed to load. Failed to load one or more resources due to forbidden issue, error code 403.”
Hmmmmm, what is this “View details” you speak of?
Oh, ok. It’s just really telling me all the things that aren’t working. Awesome. And don’t let that “download diagnostics” button fool you. It’s not going to tell you anything helpful. (I know, shocking.)
So what’s the 2 second fix you can check before getting lost down your own rabbit hole? A little thing in the Azure portal, under your Synapse workspace in the Network settings. Drum role……..make sure you have your own IP address (ahem, “Client IP address”) added as a rule with whatever other network rules you’ve got going on. ARE YOU FREAKING KIDDING ME?
If you are thinking “yea, duh, there is a little button that says ‘Add client IP'” then ShutIT. They put that image slam in the middle of one of the Microsoft Learn pages without context in the middle of something else completely, so by the time you (ok me) finish the other thing that the article was about, I’ve completely forgotten about this rando item. And apparently I’m not the only one because I’ve screen shared with a ton of super smart people (including MS peeps) and no one even noticed my IP wasn’t added.
For the record, my new mantra is “they created Fabric because they realized they had to simplify Synapse configurations. Microsoft realized too many people were getting pissed.”
UPDATE: If using PBI as a developer using desktop, you may have to add that person’s IP address to the firewall rules as well. Otherwise they may have issues refreshing tables.
Recently we ran into an issue with one of our Production SQL Server boxes. We knew the fix: we’d have to upgrade to SQL Server Enterprise. And quick. My server guy asked me if he needed to block out time THAT DAY so we could get things rolling. It’s a reasonable question, he needs to make time in his schedule to do such tasks. I said no.
WHAT? WHY? Because years of experience (and impulsivity) have taught me you first stop and think.
Some examples of things that I had to think about in this situation:
Did I want to upgrade our version along with our edition? (Yes, yes, I did)
If yes, what version – the latest? (Probably not)
Do I want a new server or is this an in-place upgrade? (New Server)
What else does could this affect? (Lots)
Tons of other things to think about, but since this is post is supposed to be a tidbit, we will stop there. Here’s the thing – we could have affected a lot.
Let’s address each of these examples to give us an idea of what can go wrong. First off, our current SQL Server version is in extended support and we are missing out on features I want to implement. These features match with our company’s goals over the next few years, and our next big lift should be more Azure focused than SQL Server version focused. That means I don’t want to install another version 1 year from now. I’m a busy gal and 1 year goes by quickly!
Does that mean I want the latest version? More bang for the buck – right? Brent Ozar’s post Which Version of SQL Server Should You Use? gave me pause. And when I thought about it more, I realized we may have compatibility issues with other apps that interact with our databases on that server. (BTW: checked with one of the vendors and they confirmed they’ve done ZERO testing on the latest SQL Server version and do not know any current clients that are using it.) So I needed to really weigh benefits versus risk on what version we should go to.
What about in-place upgrades? While I’ve done more in-place upgrades than I care to count, occasionally they can cause unexpected issues; I didn’t want to add more variables when I was already tackling an issue fix. Full stop.
And finally – what else could this affect? This is such a fundamentally important question that really I could have just had that as a singular bullet point. How does your data flow? Are apps that are part of the data flow process going to play nice with your changes? What is connecting to it? Do you even know? What is your plan when you get a ton of support calls because things aren’t working that you didn’t even know about? If you’ve never encounter this type of scenario – go take a look at the book: The Phoenix Project. I listened to the audio version years ago and seriously LOL’d at some parts because I felt like I had lived it. I’m not alone in this.
Final thought: Before being the superhero and trying to fix something super fast in Production, stop and think. You’ll probably find a temporarily solution that will hold you over until you’ve tested out any major changes. And that’s your tidbit from the trenches for today.
Data Work Mom 
