Forum Discussion

ShivaPr's avatar
ShivaPr
Icon for Advocate I rankAdvocate I
22 days ago
Solved

when selecting table from Azure Synapse dedicated SQL pool

Hi Team,

I’m connecting Power BI Desktop to an Azure Synapse dedicated SQL pool. The connection succeeds and I can see the database, but when I select the table dbo.BD, I get this error:

Can anyone tell me which steps may be missing on the Azure side (credentials, external data source, storage permissions, firewall)?

Thanks in advance!


Regards 
J.ShivaPrasad

data engineering
  • jubinsoni's avatar
    jubinsoni
    19 days ago

    Hi ShivaPr,

    The path and credential checks above are the right starting point. A few extra things I would look at, because error 16562 says "location does not exist or it is used by another process", and that second half is often the real cause.

    First, confirm the object chain. Power BI selects dbo.BD, but the error names dbo.BDRatesHourlyRate, so dbo.BD is most likely a view over that external table. Run this in Synapse Studio with the same login Power BI uses:

    -- what does dbo.BD depend on?
SELECT OBJECT_NAME(referencing_id) AS referencing_object,
       referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.BD');

-- where does the external table point?
SELECT t.name, t.location, ds.name AS data_source,
       ds.location AS data_source_location
FROM sys.external_tables t
JOIN sys.external_data_sources ds
  ON t.data_source_id = ds.data_source_id
WHERE t.name = 'BDRatesHourlyRate';

-- can this login read it directly?
SELECT TOP 10 * FROM dbo.BDRatesHourlyRate;

    Then check these less obvious causes:

    a load (ADF / pipeline / Spark) overwrites the folder
  while Power BI queries it -> files briefly missing
LOCATION folder is empty, or only holds files starting
  with _ or . (treated as hidden by PolyBase)
storage firewall blocks Synapse -> "Allow trusted
  Microsoft services" off, or the workspace managed
  identity lacks Storage Blob Data Reader

    The hidden-file behavior and the LOCATION rules are described in the CREATE EXTERNAL TABLE documentation.

    If the timing explanation fits, the most robust fix for Power BI is not to read the external table directly at all. I would land the data in an internal table after each load and point Power BI there:

    -- run after each load completes
IF OBJECT_ID('dbo.BDRatesHourlyRate_curated') IS NOT NULL
    DROP TABLE dbo.BDRatesHourlyRate_curated;

CREATE TABLE dbo.BDRatesHourlyRate_curated
WITH (DISTRIBUTION = ROUND_ROBIN,
      CLUSTERED COLUMNSTORE INDEX)
AS
SELECT * FROM dbo.BDRatesHourlyRate;

    That removes the storage dependency from the Power BI refresh, avoids reads while files are being overwritten, and is usually much faster in a dedicated SQL pool.

    So my overall approach would be:

    confirm the view -> external table chain
-> rule out concurrent writes and empty/hidden folders
-> verify firewall and managed identity access
-> serve Power BI from an internal table

    That way Power BI reads a stable table, and the external table stays a staging layer only.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

7 Replies

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    21 days ago

    Hi ShivaPr​ 

    The screenshot shows SQL error 16562 for the external table dbo.BDRatesHourlyRate. Power BI can connect to the database and read its metadata, but that does not confirm that the SQL pool can access the underlying storage files. The wording “used by another process” does not, by itself, prove that a file is locked.

    Also, the error names a different object from dbo.BD. If dbo.BD is a view, it may reference this external table.

    First, connect through SSMS or Synapse Studio to the same endpoint and database, using the same identity as Power BI, and run:

    SELECT TOP (1) * FROM dbo.BD;

    If the same error occurs there, investigate the external-table/storage configuration rather than changing the Power BI connector.

    Ask your database administrator to inspect the failing external table’s existing configuration:

    SELECT et.name AS ExternalTable, et.location AS TableLocation, ds.name AS DataSourceName, ds.location AS DataSourceLocation, ds.type_desc AS DataSourceType, c.name AS CredentialName, c.credential_identity AS CredentialIdentity FROM sys.external_tables AS et JOIN sys.external_data_sources AS ds ON et.data_source_id = ds.data_source_id LEFT JOIN sys.database_scoped_credentials AS c ON ds.credential_id = c.credential_id WHERE et.object_id = OBJECT_ID(N'dbo.BDRatesHourlyRate');


    The Azure-side checks are:

    1. Storage location: Confirm that the external data source’s base location and the table’s relative location resolve to the correct container and folder/files. Check path casing, moved/deleted files, and whether a pipeline is replacing the files while they are queried.
    2. Credential and storage permissions: Verify the credential attached to that external data source. If it uses a managed identity, check storage access for that identity, not just your own account. For read-only access, Storage Blob Data Reader at the appropriate scope is one option. If access is ACL-based, verify file-read, directory-listing and parent-directory traversal permissions. For a key/token-based credential, check validity and applicable permissions.
    3. Storage networking: Check whether the storage firewall permits access from the dedicated SQL pool. Successful Power BI-to-Synapse connectivity does not establish Synapse-to-storage connectivity. Do not assume that a private endpoint used by a Spark job or pipeline automatically provides the SQL pool’s storage access.
    4. SQL permissions: Confirm that the Power BI identity has SELECT permission on the required table/view. A successful test using a different administrator account does not rule out an identity-specific issue.

    Once the query succeeds with the Power BI identity, retry the table preview. If it succeeds in SSMS but still fails in Power BI, compare the endpoint, database and authentication settings between the two clients.

    Thanks.

  • Kagiyama_yutaka's avatar
    Kagiyama_yutaka
    Icon for Skilled Sharer rankSkilled Sharer
    20 days ago

    I think the table mismatch matters. Power BI selects dbo.BD, but error 16562 names dbo.BDRatesHourlyRate. SQL connectivity works, but the external read fails.

    In Synapse Studio, check sys.objects, then sys.sql_expression_dependencies for view references. Use sys.external_tables and sys.external_data_sources to trace the storage path and credential. Check storage permissions and network rules. Dedicated SQL pools run outside the managed VNet.

    Try SELECT TOP 10 * FROM dbo.BDRatesHourlyRate with the same SQL identity. If it works, compare the Power BI query and connection context.

  • ShivekMaharaj's avatar
    ShivekMaharaj
    Icon for Community Champion rankCommunity Champion
    19 days ago

    Hi ShivaPr​,

    The important clue is that Power BI can connect to the dedicated SQL pool, but the failure only occurs when it tries to read the external table.

    That means I would focus on the external table definition and its underlying storage rather than on the Power BI connection itself.

    Microsoft documents that a dedicated SQL pool external table depends on:

    the external table definition
the external data source
the database-scoped credential
the underlying Blob/ADLS path

    If any of those no longer match, Synapse can return the kind of “location does not exist” error shown in your screenshot.

    I would first run this directly in Synapse Studio using the same database:

    SELECT TOP 10 *
FROM dbo.BDRatesHourlyRate;

    If that fails there as well, Power BI is only surfacing a Synapse/storage problem.

    Then inspect the external object definitions and confirm the location is still valid:

    SELECT *
FROM sys.external_tables;

SELECT *
FROM sys.external_data_sources;

SELECT *
FROM sys.database_scoped_credentials;

    Microsoft’s external table guidance explains how the table points to the external data source and storage location.

    I would check:

    • whether the referenced container/folder/file still exists
    • whether the path was renamed or moved
    • whether the SAS/storage credential expired or changed
    • whether the workspace managed identity/service principal still has storage access
    • whether storage firewall/private endpoint rules changed
    • whether the external table definition still points to the correct path


    For dedicated SQL pool, Microsoft supports credentials such as SAS, storage key, service principal and managed identity for external storage access, as documented in the external data source guidance.

    So my troubleshooting order would be:

    query the external table directly in Synapse
-> inspect external table/data-source definitions
-> verify the physical storage path
-> verify the credential and storage permissions
-> only then return to Power BI

    If the SELECT succeeds in Synapse Studio but fails only from Power BI, then I would compare the authentication identity Power BI is using and the permissions available to that identity.

    But based on the error shown, I would start with the external table/storage layer first.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • jubinsoni's avatar
    jubinsoni
    Icon for Solution Sage rankSolution Sage
    19 days ago

    Hi ShivaPr,

    The path and credential checks above are the right starting point. A few extra things I would look at, because error 16562 says "location does not exist or it is used by another process", and that second half is often the real cause.

    First, confirm the object chain. Power BI selects dbo.BD, but the error names dbo.BDRatesHourlyRate, so dbo.BD is most likely a view over that external table. Run this in Synapse Studio with the same login Power BI uses:

    -- what does dbo.BD depend on?
SELECT OBJECT_NAME(referencing_id) AS referencing_object,
       referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.BD');

-- where does the external table point?
SELECT t.name, t.location, ds.name AS data_source,
       ds.location AS data_source_location
FROM sys.external_tables t
JOIN sys.external_data_sources ds
  ON t.data_source_id = ds.data_source_id
WHERE t.name = 'BDRatesHourlyRate';

-- can this login read it directly?
SELECT TOP 10 * FROM dbo.BDRatesHourlyRate;

    Then check these less obvious causes:

    a load (ADF / pipeline / Spark) overwrites the folder
  while Power BI queries it -> files briefly missing
LOCATION folder is empty, or only holds files starting
  with _ or . (treated as hidden by PolyBase)
storage firewall blocks Synapse -> "Allow trusted
  Microsoft services" off, or the workspace managed
  identity lacks Storage Blob Data Reader

    The hidden-file behavior and the LOCATION rules are described in the CREATE EXTERNAL TABLE documentation.

    If the timing explanation fits, the most robust fix for Power BI is not to read the external table directly at all. I would land the data in an internal table after each load and point Power BI there:

    -- run after each load completes
IF OBJECT_ID('dbo.BDRatesHourlyRate_curated') IS NOT NULL
    DROP TABLE dbo.BDRatesHourlyRate_curated;

CREATE TABLE dbo.BDRatesHourlyRate_curated
WITH (DISTRIBUTION = ROUND_ROBIN,
      CLUSTERED COLUMNSTORE INDEX)
AS
SELECT * FROM dbo.BDRatesHourlyRate;

    That removes the storage dependency from the Power BI refresh, avoids reads while files are being overwritten, and is usually much faster in a dedicated SQL pool.

    So my overall approach would be:

    confirm the view -> external table chain
-> rule out concurrent writes and empty/hidden folders
-> verify firewall and managed identity access
-> serve Power BI from an internal table

    That way Power BI reads a stable table, and the external table stays a staging layer only.

    AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.

  • v-csrikanth's avatar
    v-csrikanth
    Icon for Community Support rankCommunity Support
    19 days ago

    Hi ShivaPr​ 

    We would like to inquire whether have you got the chance to check the solutions provided by other users in community to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

  • pallavi_r's avatar
    pallavi_r
    Icon for Super User rankSuper User
    21 days ago

    Hi ShivaPr​ ,

    Since the connection to the dedicated SQL pool is working, we can start by checking the external table and its underlying storage.

    • First, check whether dbo.BD is an external table and verify its external data source and location.
    • Make sure the referenced files and folders exist and aren’t being moved, deleted, renamed or updated by another process.
    • Check that the database-scoped credential is configured correctly and the identity has the required storage permissions.
    • If the storage account has firewall rules or private endpoints, verify that they aren’t blocking access.
    • Run SELECT TOP 10 * FROM dbo.BD directly in Synapse Studio. If it fails there too, then check the external table or storage configuration. If it works, check the Power BI connection and permissions next.

    The HDFS error sounds like an issue with accessing the underlying files, but lets first run these test first to narrow it down.

    If this post helps, please accept this as a solution. Appreciate your kudos.

    Thanks,

    Pallavi

    • ShivaPr's avatar
      ShivaPr
      Icon for Advocate I rankAdvocate I
      19 days ago

      Hi Pallavi Thanks for some points, sorry got update from data team, it is serverless sql pool. any points for those