CETAS in SQL Server 2022

Question

I have followed the below steps in SQL Server 2022:

Step 1: Create a master key

Step 2:

CREATE DATABASE SCOPED CREDENTIAL [BlobSAS]
WITH
    IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET   = '?sv=2021-06-08&ss=bfqt&srt=c<<>>';

Step 3:

CREATE EXTERNAL DATA SOURCE [BlobSource]
WITH (
    LOCATION   = 'abs://<<>>.blob.core.windows.net/dummy',
    CREDENTIAL = [BlobSAS],
);

Step 4:

CREATE EXTERNAL FILE FORMAT [CommaDelimited] WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (
        FIELD_TERMINATOR = N',',
        STRING_DELIMITER = N'"',
        FIRST_ROW = 2,
        USE_TYPE_DEFAULT = True
    )
)

Step 5:

CREATE EXTERNAL TABLE Demo WITH (
    LOCATION    = '/Test',
    DATA_SOURCE = [BlobSource],
    FILE_FORMAT = [CommaDelimited]
)
AS
SELECT
    2 AS C1,
    2 as C2

I am getting this error:

Access check for 'CREATE/WRITE' operation against 'abs://<<>>.blob.core.windows.net/dummy/Test' failed with HRESULT = '0x80070005'

Note: All permissions was provided while generating SAS

I also tried key route :

CREATE DATABASE SCOPED CREDENTIAL [BlobKey] WITH
    IDENTITY = '<<>>',
    SECRET = '<<>>';

CREATE EXTERNAL DATA SOURCE [BlobKeySource] WITH (
    LOCATION   = 'abs://<<>>.blob.core.windows.net/dummy',
    CREDENTIAL = [BlobKey]
);

and when executing the below statement via sys admin account

CREATE EXTERNAL TABLE Demo WITH (
    LOCATION    = '/Test',
    DATA_SOURCE = [BlobKeySource],
    FILE_FORMAT = [CommaDelimited]
)
AS
SELECT
    2 AS C1,
    2 as C2

I'm getting the error:

Msg 15151, Level 16, State 1, Line 1
Cannot find the CREDENTIAL 'BlobKey', because it does not exist or you do not have permission.

Answer 1

I've just had loads of issues trying to do the same. Please your code does all of the following: (It looks like 2 & 3 are fine, but it may be a useful reference for others).

• Shared access signature is generated for the container and not the storage account (Note this is different from how you would set it up in Azure Synapse SQL DW Pools)
• No Type specified in the external data source (sometimes documentation states HADOOP or BLOB_STORAGE)
• the location must be an abs:// link and not adls://.