How to select data (via SQL) in Synapse serverless SQL via pyspark (within Synapse) or any SQL editor?

Question

I built a kind of data lake in Synapse where I organize via queries.

My data is in an Azure Data Lake Storage ADLS Gen2.

Now I would like to use a database user to access this view with, for example, SQL Editor (which is installed on my desktop) or PySpark (from Spark Pool).

For this I did the following:

1. I created a SAS token for the storage (where my data is located).
2. I created a login, a database and a user
3. I create a DATABASE SCOPED CREDENTIAL WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'my SAS token'.
4. I give the user the right to read the CREDENTIALs
5. I create a corresponding view v_abc (select * from OPENROWSET ...)
6. I give the user the right to read this view v_abc

For this I also followed the instructions from Microsoft: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?WT.mc_id=Portal-Microsoft_Azure_Support&tabs=user-identity

This all sounds logical to me so far, but it doesn't work anymore. I did the whole thing once before in February, before we decided to use Synapse.

What error am I getting? The query cannot be executed because the files from the OPENROWSET cannot be listed.
Original message: *Content of directory on path 'mydatafolder**' cannot be listed.*

What am I doing wrong?

Answer 1

Hello @Oliver Rothland ,

Thanks for the question and using MS Q&A platform.

Could you please make sure the below prerequisite is met:

Moreover, you have mentioned that you are using OPENROWSET, so please make sure the appropriate credentials are created and used as called out in the documentation.

Do let us know how it goes.

Thank you