Unable to Query Serverless Pool View in Azure Synapse using SQL Admin Credentials

Question

I have set up a Serverless SQL pool in Azure Synapse that is querying a view I had set up of a linked Azure Data Lake.

CREATE VIEW DeviceTelemetryView
AS SELECT corporationid, deviceid, version, Convert(datetime, dateTimestamp, 126) AS dateTimeStamp, deviceData FROM
   OPENROWSET(
       BULK 'https://test123.dfs.core.windows.net/devicetelemetry/*/*/*/*/*/',
       FORMAT = 'PARQUET'
    ) AS [result]
GO

Using my Azure AD credentials from with synapse studio or SSMS I have no issues querying this View. When I try to query using my SQL Admin account I get the following error:

Cannot find the CREDENTIAL 'https://test123.dfs.core.windows.net/devicetelemetry/////*/', because it does not exist or you do not have permission.

It is important that I am able to query using SQL Admin Creds as we are wanting to query this View via our application for various reports and thus don't want to use AAD creds.

I have tried the SO solution provided here: GRANT Database Scoped Credential syntax gives mismatched input error

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[WorkspaceSystemIdentity] TO [sqlAdmin];

As this seems to be the default credential that was created when linking my DataLake to Synapse however this gives me the following error when run against the db where my view exists:

Cannot find the database scoped credential 'WorkspaceSystemIdentity', because it does not exist or you do not have permission.

Answer 1

You would need to create server-scoped credential to allow access to storage files.

Server-scoped credential

These are used when SQL login calls OPENROWSET function without DATA_SOURCE to read files on some storage account. The name of server-scoped credential must match the base URL of Azure storage (optionally followed by a container name). However, SQL users can't use Azure AD authentication to access storage and serverless SQL pool doesn't return subfolders unless you specify /** at the end of path.