question

SangilimuruganS-8968 avatar image
0 Votes"
SangilimuruganS-8968 asked sangilimurugan-9166 commented

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

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

80309-access.png


azure-synapse-analyticsazure-blob-storage
access.png (59.5 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

SangilimuruganS,

It looks like you are working through this tutorial? https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-external-table-as-select

Without sharing anything that would be sensitive info, can you give a little more detail on how MyDataSource23 is setup? The shared access signature given in the tutorial will not work on your storage account. Instead you would want to generate a SAS token for your storage account or use something like:

 CREATE DATABASE SCOPED CREDENTIAL [ManagedIdentity]
 WITH IDENTITY = 'Managed Identity';

And use that to setup your external data source.


0 Votes 0 ·

Hi,

Thanks for helping. Yes i am trying to execute the turorial.
I am using 2 data sources here. one is MyDataSource23 (my blob) and for which SAS is generated. second data source is "sqlondemanddemo".

CREATE DATABASE SCOPED CREDENTIAL [SasTokenWrite23]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
GO

CREATE EXTERNAL DATA SOURCE [MyDataSource23] WITH (
LOCATION = 'https://sangilistorage.dfs.core.windows.net/csv', CREDENTIAL = [SasTokenWrite23]
);
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF23] WITH (
FORMAT_TYPE = PARQUET,
DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

Thanks,
Sangili

0 Votes 0 ·
SamaraSoucy-MSFT avatar image SamaraSoucy-MSFT SangilimuruganS-8968 ·

You can use either SAS token or Managed Identity/Contributor role but not both at the same time. Adding the role doesn't help if you are using SAS.

The next thing to do is to make sure you are generating your SAS token correctly. They need to be generated for the specific container and they can expire:
81265-2021-03-24-12-05-29-azure-webjobs-hosts-microsoft.png


0 Votes 0 ·
Show more comments

1 Answer

sangilimurugan-9166 avatar image
1 Vote"
sangilimurugan-9166 answered sangilimurugan-9166 commented

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

82758-access1.png



access1.png (77.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Assuming the file does exist, the most common reason is authentication issues. You do still need to add the credentials to the data source since this is a separate storage account:

  CREATE EXTERNAL DATA SOURCE test1 WITH (
      LOCATION = 'https://sangilistorage1.blob.core.windows.net/test/',
      **CREDENTIAL = [ManagedIdentity]**
  );



0 Votes 0 ·

Hi,

Issue got recified and it is working now. I really appreciate your help. Thank you so much for your guidance.

Thanks,
Sangili

0 Votes 0 ·