Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Permissions Issue Connecting Power BI to Azure Synapse Table

Power BI Desktop Import: “Access is denied on WORKING.<table>” via Navigator, but works via SQL Statement. Preview sometimes passes; full Refresh fails

 

Environment

  • Power BI Desktop: <Version: 2.147.931.0 64-bit (September 2025)>
  • Data source: Azure SQL / Synapse (Dedicated/Serverless) — using Azure AD auth
  • Data model: TRUSTED views → TRUSTED TVFs → WORKING external tables (Parquet via External Data Source + External File Format)
  • Schemas involved: TRUSTED, WORKING (owner aligned under a shared owner, e.g., dba)
  • Permissions granted to my AAD user/group: SELECT on TRUSTED, WORKING, RAW (confirmed by DBA)

Issue

  • Importing via Navigator (selecting tables/views from the tree) fails for a few new views with:

Failed to save modifications to the server… OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: The query failed because the access is denied on table 'WORKING.<TableName>'.

(Includes “Distributed request ID” and “Total size of data scanned is 0 MB”, which looks like Synapse behavior.)

  • Import via SQL statement for the same view (e.g., SELECT * FROM TRUSTED.v_<Name>) succeeds.
  • In Power Query Editor: clicking Refresh Preview on the table often succeeds and fails sometimes; however, after Close & Apply, the model Refresh fails with the same access-denied error.
  • Older views with the same logical pattern (TRUSTED view → TRUSTED TVF → WORKING external parquet) refresh fine. The issue hits a set of newer views.

What I’ve already tried

  • Cleared and re-established credentials in Data Source Settings (Azure AD).
  • Verified I’m using the same AAD identity as SSMS (SSMS queries pass).
  • Turned off parallel loading, auto date/time, relationship autodetect.
  • Turned off PQ profiling (column quality/profile/distribution).
  • Retried getting data in a new PBIX (only the failing view).
  • Import via SQL statement works; Import via Navigator fails (consistent).
  • DBA confirmed: SELECT granted on TRUSTED, WORKING, RAW; owners aligned; no recent changes there.
  • Tried running queries on the same views/tables in SSMS and it works fine.

Need to know the reason it is failing and throwing error on access denied and any ideas or suggestions would help!

Need Help

5 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

    This is not a Power BI bug but an Azure Synapse permissions detail.

    Root cause
    When using Navigator, Power BI needs metadata permissions on the external objects, not just SELECT. Newer external views require more than SELECT to resolve properly.

    Fix
    Ask your DBA to grant the following in addition to SELECT:

    -- Metadata visibility
    GRANT VIEW DEFINITION ON SCHEMA::WORKING TO [Your-AAD-Group];
    GRANT VIEW DEFINITION ON SCHEMA::TRUSTED TO [Your-AAD-Group];

    -- External object references
    GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[CredName] TO [Your-AAD-Group];
    GRANT REFERENCES ON EXTERNAL DATA SOURCE::[DsName] TO [Your-AAD-Group];
    GRANT REFERENCES ON EXTERNAL FILE FORMAT::[ParquetFmt] TO [Your-AAD-Group];

    -- Data access (already confirmed but keep for completeness)
    GRANT SELECT ON SCHEMA::WORKING TO [Your-AAD-Group];
    GRANT SELECT ON SCHEMA::TRUSTED TO [Your-AAD-Group];


    Quick validation

    Run SELECT TOP 1 * FROM WORKING.<Table> under your AAD identity.

    Try SELECT * FROM sys.external_data_sources. If you cannot see rows without VIEW DEFINITION or REFERENCES, that is the missing piece.

    Confirm that the new views reference the same EXTERNAL DATA SOURCE and credentials as the older ones.

    If after applying these grants the issue persists, open a Microsoft Support ticket so they can trace it directly on your Synapse environment.

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Ritaf1983,
      The issue still did not resolve.

      We do this one by default----GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[CredName] TO [Your-AAD-Group];

       

      The following however throws a syntax error: "Incorrect syntax near 'external'"
      GRANT REFERENCES ON EXTERNAL DATA SOURCE::[DsName] TO [Your-AAD-Group];
      GRANT REFERENCES ON EXTERNAL FILE FORMAT::[ParquetFmt] TO [Your-AAD-Group];

      I have escalated this issue to Miscrosoft Team. They are investigating the issue. Hopefully, they will come up with a reason to what the issue is!

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi Anonymous,

    Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Ritaf1983 for sharing valuable insights.

     

    Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

     

    Thank you for being part of the Microsoft Fabric Community.

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

    Hello Anonymous,

    We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

    Thank you.

  • v-ssriganesh's avatar
    v-ssriganesh
    Icon for Community Support rankCommunity Support
    1 year ago

     

    Hello Anonymous,

    Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

    Thank you.