question

WaqasHussain-7796 avatar image
0 Votes"
WaqasHussain-7796 asked WaqasHussain-7796 answered

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

I created external tables using the steps specified here: https://docs.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:

  1. Create database scope credentials to access data inside blob storage

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

  2. 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]
    );

  3. 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 = [cashieslake_raw_marketing], LOCATION = N'sub_dir/customer_list_*.csv',FILE_FORMAT = [QuotedCsvWithHeaderFormat])
    GO

Using information on this page https://docs.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.

azure-synapse-analytics
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @waqashussain-7796,

Thanks for using Microsoft Q&A !!
Sorry but I haven't tried using Synapse with any other external platform like Redash. Also, what do mean by "Within Synapse workspace" do you mean using "Syanpse Studio"
Have you tried executing GRANT REFERENCES ON CREDENTIAL::[datalake_credentials] TO [redash]; using Synapse Studio?
119192-image.png

Any specific reasons you want to use Redash instead of using Synapse Studio ? Can you please elaborate your scenario here ?

Thanks
Saurabh

0 Votes 0 ·
image.png (74.1 KiB)

1 Answer

WaqasHussain-7796 avatar image
1 Vote"
WaqasHussain-7796 answered

Yes, I meant Syanpse Studio and the sql command you suggested, I tried that and mentioned in my post that it didn't work.
Anyway, anyone looking for the solution, it was to execute:

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

ref: https://stackoverflow.com/questions/68554261/cannot-find-the-credential-db-scoped-creds-because-it-does-not-exist-or-you

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.