Azure Synapse Serverless query error with Access check for 'CREATE/WRITE' operation against blob failed with HRESULT = '0x80070005'.

Question

hi,
i am trying to execute the below query and i getting error as HRESULT = '0x80070005'.:
I have given "Storage Blob Data Contributor" access to synapse analytics resource. can anyone help me?.

CREATE EXTERNAL TABLE [dbo].[PopulationCETAS] WITH (
LOCATION = 'populationParquet/',
DATA_SOURCE = [MyDataSource23],
FILE_FORMAT = [ParquetFF23]
) AS
SELECT
*
FROM
OPENROWSET(
BULK 'csv/population-unix/population.csv',
DATA_SOURCE = 'sqlondemanddemo',
FORMAT = 'CSV', PARSER_VERSION = '2.0'
) WITH (
CountryCode varchar(4) COLLATE Latin1_General_100_CI_AS_SC_UTF8 ,
CountryName varchar(64) COLLATE Latin1_General_100_CI_AS_SC_UTF8 ,
Year int,
PopulationCount int
) AS r;

Started executing query at Line 1

Access check for 'CREATE/WRITE' operation against 'https://sangilistorage.dfs.core.windows.net/csv/populationParquet/' failed with HRESULT = '0x80070005'.
Total execution time: 00:00:01.488

Answer 1

Hi,

I tried by executing a query to access ADLS using serverless SQL.

 drop DATABASE sangilidb;  
  
CREATE DATABASE sangilidb;  
  
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXXXXXX';  
CREATE DATABASE SCOPED CREDENTIAL [ManagedIdentity] WITH IDENTITY = 'Managed Identity';  
  
CREATE EXTERNAL DATA SOURCE test1 WITH (  
    LOCATION = 'https://sangilistorage1.blob.core.windows.net/test/'  
);  
  
SELECT COUNT_BIG(*)  
FROM OPENROWSET  
  (  
      BULK 'Employee_Records.parquet',  
      DATA_SOURCE = 'test1',  
      FORMAT='PARQUET'  
  ) AS ts  

I am still facing issue: "File 'https://sangilistorage1.blob.core.windows.net/test/Employee_Records.parquet' cannot be opened because it does not exist or it is used by another process."

I have given only blob data contributor access to synapse and it can be observed from the screenshot.

I can able to access the files and run queries on top the ADLS account that is attached with the Synapse as primary and I face this error only when i added my external ADLS account and try querying it.

Thanks,
Sangili