I have setup a Synapse environment and filled my storage account with some sample Parquet files. I have then created a serverless SQL database and created some external tables over the Parquet files. All this works fine and I can query these tables fine from the Synapse UI and SSMS using AD Authentication.

The problem is I want to connect an app to the serverless SQL database which doesn't support AD authentication. Therefore I want to connect it using a standard SQL account. I have setup a SQL account (username and password) and I'm able to connect through SSMS, but not query any tables due to this error...

External table 'TableName' is not accessible because content of directory cannot be listed.

I assume this is a double-hop authentication problem because the SQL user doesn't have access to the storage account? I can't seem to find any guides on how to do this. Does anyone know?

CodePudding user response：

I've written a blog-post where this issue is tackled, as I've encountered this problem as well a few days ago. You can read it here.

Basically, it comes down to the fact that you have to:

• create a SQL login for your user
• create a credential in SQL that has the same name as the URL that points to the container in your datalake that contains the files you want to query
• grant reference rights on that credential to your SQL login
• create a user on your database for that login

Next to that, you also need to create some specific role-assignments.