Cannot find the CREDENTIAL '', because it does not exist or you do not have permission

Question

I created external tables using the steps specified here: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=shared-access-signature.

Within Synapse workspace, I am able to access external table data (as it uses my AD credentials). However, it doesn't work from an external platform, redash in this case. In order to access data from redash, I created a db user and this is where I think I am missing a step to somehow grant this user to access database scope credentials.

Steps, I took to create external table with required creds:

Create database scope credentials to access data inside blob storage

CREATE DATABASE SCOPED CREDENTIAL datalake_credentials
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'SAS TOKEN';

Create external datasource using credential created in previous step

CREATE EXTERNAL DATA SOURCE datalake_raw_marketing
WITH ( LOCATION = 'https://mydatalake.blob.core.windows.net/raw/marketing'
          , CREDENTIAL= [datalake_credentials]
);

Finally create an external table using datasource

CREATE EXTERNAL TABLE [dbo].[Customers]
(
    [Id] [varchar](36),
    [FirstName] [varchar](100),
    [Email] [varchar](100),
    [Date] [varchar](100),
    [Group] [varchar](100)
)
WITH (DATA_SOURCE = [datalake_raw_marketing], LOCATION = N'sub_dir/customer_list_*.csv',FILE_FORMAT = [QuotedCsvWithHeaderFormat])
GO

Using information on this page https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=shared-access-signature I tried "Grant permissions to use credential" using:

GRANT REFERENCES ON CREDENTIAL::[datalake_credentials] TO [redash];

But it always results in:

Cannot find the CREDENTIAL 'datalake_credentials', because it does not exist or you do not have permission.

score 3 · Accepted Answer · answered Jul 28 '21 at 13:02

3

Your credential is DATABASE SCOPED, so you need to include that in the statement:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{credential_name}] TO [{user}];

answered Jul 28 '21 at 13:02

Joel Cochran

7,139
2
30
43

thanks mate, it worked! i was digging the docs but never came across this, by any chance do you have any reference/docs? – Waqas Jul 28 '21 at 13:17
Unfortunately, no - IIRC, I had this exact same problem and was given this solution by someone on the Synapse team. – Joel Cochran Jul 28 '21 at 16:33

Cannot find the CREDENTIAL '', because it does not exist or you do not have permission

1 Answers1