Synapse external table "Unauthorized"-CodePudding

I have created an external table in Azure Synapse from a parquet file stored in an ADLS Gen2 container. I have used the following three queries to create the datasource, the file format and the table:

CREATE EXTERNAL DATA SOURCE demo_external_datasource
WITH
(   LOCATION = 'https://<Storage account>.dfs.core.windows.net/<container>' 
     
)

CREATE EXTERNAL FILE FORMAT demo_parquet_file_format
WITH 
(
   FORMAT_TYPE = PARQUET
)
        
CREATE EXTERNAL TABLE <DATABASE>.<SCHEMA>.<TABLE NAME>
(
    [infa_operation_time] nvarchar(4000),
    [channel_key] bigint,
    [channel_name] nvarchar(4000)

)
WITH (
    LOCATION = '/<FOLDER>/',
    DATA_SOURCE = demo_external_datasource,
    FILE_FORMAT = demo_parquet_file_format

)

If I use the web client (Synapse studio) with the database administrator user I can query the table and get results, but when I try to do the same connecting via SQL Server Management Studio it throws the following error:

Not able to validate external location because The remote server returned an error: (401) Unauthorized.

I have tried to connect with the database admin and with my Azure Active Directory user, which is Owner of the Synapse Workspace and the ADLS Storage account, with the same result.

Thanks.

CodePudding user response：

I believe you need to create a Database Scoped Credential to your ADLS account:

CREATE DATABASE SCOPED CREDENTIAL AdlsCredentials
WITH IDENTITY = 'SHARED ACCESS SIGNATURE' ,
  SECRET = '{yourSecret}'

Then reference that in your External Data Source statement:

CREATE EXTERNAL DATA SOURCE {yourEDSname}
WITH 
  ( 
    LOCATION = 'https://{youradls}.blob.core.windows.net/{yourpath}/' , 
    CREDENTIAL = AdlsCredentials
  ) ;

CodePudding user response：

You could do the following:

-- Below creates a user. And then you can call the query using the user details
-- Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-overview?tabs=impersonation#permissions

CREATE LOGIN jpx WITH PASSWORD = 'XXXX';
CREATE USER jpx FROM LOGIN jpx;


CREATE CREDENTIAL [https://<storagealias>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'xxxx';
GRANT REFERENCES ON CREDENTIAL::[https://<storagealias>.dfs.core.windows.net/raw] TO jpx

Now the username-password combination can be used to login from Data Studio. Or in JDBC connection string.