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.

Create external table as select >> CETAS in MSFT SQL server 2022 — Nandan, Dec 10 '22 at 07:56
Have you tried recreating your shared access signature without the `?` character at the beginning? The SAS URI presented in the Azure GUI is usually a complete https:// URI and the `?` character is just a delimiter between the container name/path and the SAS Token itself. — AlwaysLearning, Dec 10 '22 at 08:51
The screenshot at [SAS token](https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview#sas-token) is incorrect by including the `?` character. See instead [Creating a Credential using a SAS Token](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-credential-transact-sql#d-creating-a-credential-using-a-sas-token)... _The SHARED ACCESS SIGNATURE secret should not have the leading `?`._ — AlwaysLearning, Dec 10 '22 at 09:03
hey @AlwaysLearning thanks for your inputs but even with or without the ? character I am getting the same error of access check — Nandan, Dec 10 '22 at 12:11

score 0 · Answer 1 · answered Jan 05 '23 at 10:40

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://.

CETAS in SQL Server 2022

1 Answers1