Azure CREATE DATABASE SCOPED CREDENTIAL usage for CREATE EXTERNAL TABLE

We have "oauth2-style" credentials for Azure blob storage, and want to use those credentials to load/save data in Azure Synapse. We issue a command like

CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY='myclientid@https://login.microsoftonline.com/mytenantid/oauth2/token' , SECRET='***'

This form works for COPY INTO command, e.g.

COPY INTO table (c1,c2,...)  FROM 'https://myaccount.blob.core.windows.net/container/folder/' 
WITH ( FILE_TYPE='CSV', 
CREDENTIAL=(IDENTITY= 'myclientid@https://login.microsoftonline.com/mytenantid/oauth2/token', SECRET='***')
)

But when I used the saved credential in a CREATE TABLE AS SELECT command

CREATE EXTERNAL DATA SOURCE MyDataSource WITH (
LOCATION='wasb://[email protected]/', 
CREDENTIAL=MyCredential
)
CREATE EXTERNAL TABLE MyTable WITH (
   LOCATION='wasb://[email protected]/test',
   DATA_SOURCE = MyDataSource,
   FILE_FORMAT = MyFormat
) AS
SELECT * FROM sometable

I get this:

Msg 105019, Level 16, State 1, Line 47
External file access failed due to internal error: 'Parameters provided to connect to the Azure storage account are not valid.'

Do oauth2-style credentials work for this? If not, what do I need to use instead?

UPDATE: It would appear that, despite much documentation to the contrary, the SAS form must be used

CREATE DATABASE SCOPED CREDENTIAL [SasTokenWrite]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '***'

As I mentioned, the documentation says:

"When accessing Azure Storage Account (V2) or Azure Data Lake Storage Gen2, the IDENTITY must be SHARED ACCESS SIGNATURE."

Yet my portal for the storage account shows: Storage (general purpose v1)

Can anyone explain why the SAS token is required for CREATE EXTERNAL TABLE command but not for COPY INTO?

UPDATE: After several code reviews and combining the ADL2 and ABS credential logic, I find that ABS simply does not work, but ADL2 does, and ABS credentials work with COPY INTO but not CREATE EXTERNAL TABLE. I chalk this up to a bug in polybase. If anyone can show a counter-example I would love to see it.

Solution

Why is the SAS token required for CREATE EXTERNAL TABLE command but not for COPY INTO?

In COPY INTO SAS is also supported. As you see here there is table mentioned which file format supports which authentication method, SAS is supported for all file formats.

Do oauth2-style credentials work for this? If not, what do I need to use instead?

As far as oauth2-style credentials it is supported for both ADLS Gen1 and ADLS Gen 2 the issue is might with blob storage.

While connection ADLS with oauth2 credentials you need to use abfss endpoint

CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    IDENTITY = 'ClientId@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
    SECRET = 'secret';

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    LOCATION = 'abfss://[email protected]',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);