error while opening csv file from Synapse Serverless SQL Pool

Question

Hallo,
i am trying to open a csv file from a storage account via Synapse Serverless sql pool. This is the query:

SELECT
TOP 100 *
FROM
OPENROWSET(
BULK '*********************************/test/test.csv',
FORMAT = 'CSV',
        PARSER_VERSION = '2.0'
) AS [result]

This is the error that I am getting:

File '-' cannot be opened because it does not exist or it is used by another process

My user already have Contributor role on the storage account.

Any helps?

Answer 1

Hello @Maltese Paolo ,

When using OPENROWSET command to access the Azure storage account, it requires "blob data contributor" or "blob data reader" role in the storage account.

Note that if we do not specify any credential in the OPENROWSET command, it will pass through the credential of the current user who login to the serverless SQL pool.

In your case, this credential didn't get granted the required permissions.

Also, the Contributor role in the storage account doesn't grant you the data access permissions as the Blob Data Contributor role does

a. To use passthrough authentication, grant the user Blob Data Contributor role in the target storage account.
b. Alternately, specify credential in the OPENROWSET command to use the managed identity of the Synapse workspace, which has been granted required permission to the storage account also works.

Below is the sample script for your reference:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'  
  
   
CREATE DATABASE SCOPED CREDENTIAL msicred  
  
WITH IDENTITY = 'Managed Identity'  
  
   
CREATE EXTERNAL DATA SOURCE [dls-msi]  
  
WITH  
  
(LOCATION = 'abfss://******@dlscompoc.dfs.core.windows.net',  
  
    CREDENTIAL = msicred)  
  
   
SELECT  
  
    TOP 100 *  
  
FROM  
  
    OPENROWSET(  
  
        BULK 'Input Your URLpath',  
  
        DATA_SOURCE = 'dls-msi',  
  
        FORMAT='PARQUET'  
  
    ) AS [result]  

Below online document about OPENROWSET command may be helpful as well.
How to use OPENROWSET in serverless SQL pool - Azure Synapse Analytics | Microsoft Learn

also, please go through the below document regarding this error.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/resources-self-help-sql-on-demand

Query fails because file cannot be opened

If your query fails with the error 'File cannot be opened because it does not exist or it is used by another process' and you're sure both file exist and it's not used by another process it means serverless SQL pool can't access the file. This problem usually happens because your Azure Active Directory identity doesn't have rights to access the file or because a firewall is blocking access to the file. By default, serverless SQL pool is trying to access the file using your Azure Active Directory identity. To resolve this issue, you need to have proper rights to access the file. The easiest way is to grant yourself 'Storage Blob Data Contributor' role on the storage account you're trying to query.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators