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:
CREATE DATABASE SCOPED CREDENTIAL datalake_credentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'SAS TOKEN';
CREATE EXTERNAL DATA SOURCE datalake_raw_marketing
WITH ( LOCATION = 'https://mydatalake.blob.core.windows.net/raw/marketing'
, CREDENTIAL= [datalake_credentials]
);
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://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.
Your credential is DATABASE SCOPED, so you need to include that in the statement:
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{credential_name}] TO [{user}];
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments