Forum Discussion
Materialized Views in Mirrored Azure Databricks Catalog not working
Materialized views was recently enabled for Mirrored Azure Databricks Catalog (https://community.fabric.microsoft.com/blog/fbc_fabricupdatesblogs/announcing-support-for-materialized-views-and-streaming-tables-in-mirrored-azure/5377928) but I can't get it to work when the ADSL Gen 2 storage is behind a firewall. It works for regular delta tables but not for materialized views.
The exemption is for ADLS Gen 2 without firewall where I've managed to project it. So some success.
In Azure Databricks a service principal has external_use_schema permission and of course select on the table and use catalog. This principal is used for UC access. Furthermore, a Fabric Workspace, has resource instance allowance on the storage combined with Blob Read (RBAC) on the container. This makes it work for the delta tables but again, materialized views don't show up under the SQL Endpoint. When I look under "Monitor Catalog" the MVs are all listed as success but viewing them in the preview pane doesn't work either.
The error I get in SQL Endpoint is "Unable to sync the table due to insufficient permissions." and "Error: TableAccessForbidden". The confusion comes from regular delta tables working but not the materialized views.
I've enabled the config in the pipeline that run the tables (pipelines.externalMetadata.enabled: "true"). And again, this works when the storage doesn't have firewall.
Anyone, successfully been able to get MVs to work with a storage behind a firewall?
4 Replies
jubinsoniMemorable Member
Hi carlhsg,
I can't reproduce this, so what follows is a way to narrow it down rather than a confirmed cause. Your description gives a strong clue: the same service principal, the same workspace resource instance rule and the same Blob Read role work for regular Delta tables behind the firewall, but not for materialized views, and the Monitor Catalog page still reports the views as successful. That points at the data location rather than at permissions on the catalog.
My working hypothesis
A materialized view is not necessarily stored in the same account, container or path as the external tables you already opened up. If its data lives in a different location (for example a managed storage location for the catalog or schema), then the firewall exemption and the role assignment you created would not cover it. Listing succeeds because that is a metadata operation, while reading the files fails, which matches a preview pane that cannot load.
-- In Azure Databricks: where does the materialized view actually keep its data? DESCRIBE EXTENDED my_catalog.my_schema.my_materialized_view; -- Compare the Location with the storage account and container -- that your firewall rule and the RBAC assignment cover.Checks to run
Check What to compare If it differs Storage location of the view The location shown by the describe command versus the account and container in your resource instance rule Add the workspace to the firewall rules of that account, and grant the read role on that container too RBAC scope Role assignment on the container or account that holds the view data, not only the table container Assign the read role at the scope that covers both Which identity reads the files Whether the Fabric side uses the workspace identity or the service principal for the storage path Make sure the identity that reads files has the role and is allowed through the firewall Same behavior with a regular table in the same schema Whether a table stored in the view location also fails Confirms the location, not the object type, is the problem Region and network path Fabric capacity region versus storage region, and any private endpoint requirements Check the trusted workspace access prerequisites for your setup What to collect if it still fails
- The exact error from the SQL analytics endpoint and the preview pane, with timestamps.
- The storage account diagnostic logs for the failed read: a denied request shows which identity and which path was refused.
- The describe output for one working table and one materialized view side by side.
The storage account logs are the quickest way to turn this from a guess into a fact, since a firewall block and a missing role look different in the log. If the logs show the request never arrives, the network rule is the issue; if it arrives and is denied, the role assignment is.
So my overall approach would be:
1. Find where the materialized view stores its data 2. Make sure the firewall rule and read role cover that exact location 3. Compare with a regular table stored in the same place 4. Use storage diagnostic logs to see whether the block is network or role 5. If it persists, open a support case with the logs and both describe outputsAI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
Appreciate the detailed response.
The MVs are in the same storage account, container and directory as the delta tables which can be mirrored successfully (managed unity storage).
When reviewing the storage logs I did find an interesting difference between the successful reads of delta tables and unsuccessful MVs. There are authentication errors for the request to the storage files for MVs with "DelegationSAS" whereas with delta the request are successful OAuth. So there seems to be different authentication methods.
- ShafiUllah-bc
Resolver II
carlhsg The DelegationSAS vs OAuth difference in your storage logs is most likely the whole story. Trusted workspace access only admits Entra-authenticated requests: the Learn page lists organizational account, service principal and workspace identity as the only methods that work with a firewall-enabled storage account, and the mirroring tutorial says Fabric uses the Workspace Identity to traverse the firewall. Your Delta reads use OAuth, so the resource instance rule lets them in. A user delegation SAS is not one of those methods, so the firewall refuses it even though the path, container and roles are the same.
Where the SAS comes from is an inference, not documented: Unity Catalog credential vending on Azure returns exactly this token type (azure_user_delegation_sas), so the MV reads look like they use vended credentials instead of the workspace identity. Neither the Databricks mirroring docs nor the MV announcement mention firewalled storage for MVs, so I could not find a supported setting that makes a SAS pass a resource instance rule.
What you can do today:
1. Open a support ticket with the failing request IDs, the DelegationSAS log lines and one successful OAuth line for a Delta table in the same folder. It looks like a gap in the new MV support, and your logs show it clearly.
2. As a workaround, have the pipeline also write the MV result to a regular Unity Catalog Delta table in the same schema (for example a job task after each pipeline refresh that runs CREATE OR REPLACE TABLE ... AS SELECT * FROM your MV). Regular tables already mirror through OAuth in your setup. The cost is an extra copy and a refresh step.
3. Keep the firewall on. Opening the account just for MVs gives up the protection the resource instance rule gives you.
To check which requests are refused and by whom, run this against the storage diagnostic logs:
StorageBlobLogs | where TimeGenerated > ago(1d) and Uri has "<your MV folder>" | summarize Requests = count() by AuthenticationType, StatusCode, StatusText, CallerIpAddressIf every refused row is the SAS type and every OAuth row succeeds, that confirms it for the ticket.
If this solves it, please mark it as the solution.
References:
https://learn.microsoft.com/en-us/fabric/security/security-trusted-workspace-access
https://learn.microsoft.com/en-us/fabric/mirroring/azure-databricks-tutorial
- Kagiyama_yutaka
Skilled Sharer
Fabric now supports MV mirroring, but the linked Databricks page still says Fabric reads aren't supported. Firewall-protected Databricks workspace storage is also excluded.
Check if ur UC managed storage falls under that restriction. If not, ask Fabric support whether MVs work with firewall-protected ADLS Gen2 through the mirror's Network Security connection. Send both links, failed request IDs, UTC timestamps, StatusText, and the SAS/OAuth logs.
