Error in SSMS when running query from SQL On-Demand endpoint

I am attempting to pull in data from a CSV file that is stored in an Azure Blob container and when I try to query the file I get an error of

File 'https://<storageaccount>.blob.core.windows.net/<container>/Sales/2020-10-01/Iris.csv' cannot be opened because it does not exist or it is used by another process.

The file does exist and as far as I know of it is not being used by anything else.

I am using SSMS and also a SQL On-Demand endpoint from Azure Synapse.

What I did in SSMS was run the following commands after connecting to the endpoint:

1. CREATE DATABASE [Demo2];
2. CREATE EXTERNAL DATA SOURCE AzureBlob WITH ( LOCATION 'wasbs://<container>@<storageaccount>.blob.core.windows.net/' )

3. SELECT * FROM OPENROWSET (
           BULK 'Sales/2020-10-01/Iris.csv',
           DATA_SOURCE = 'AzureBlob',
           FORMAT = '*'
           ) AS tv1;
   
   

I am not sure of where my issue is at or where to go next. Did I mess up anything with creating the external data source? Do I need to use a SAS token there and if so what is the syntax for that?

Answer

I figured out what the issue was. I haven’t tried Armando’s suggestion yet.

First I had to go to the container and edit IAM policies to give my Active Directory login a Blob Data Contributor role. The user to give access to will be your email address for logging in to your portal.

https://docs.microsoft.com/en-us/azure/storage/common/storage-auth-aad-rbac-portal?toc=/azure/synapse-analytics/toc.json&bc=/azure/synapse-analytics/breadcrumb/toc.json

After that I had to re-connect to the On-Demand endpoint in SSMS. Make sure you login through the Azure AD – MFA option. Originally I was using the On-Demand endpoint username and password which was not given access to the Blob Data Contributor role for the container.

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