Serverless Inconsistencies

I was setting up an Azure Synapse Serverless Pools demo environment based on a the excellent data lakehouse architecture originally created by Andy Cutler. I inadvertedly created a shared access signature (SAS) token to reference my data lake storage that expired the very next day. When I went to update it, everything went pear-shaped. I’ll show you what happened to me so you can avoid it:

Creating the original credentials

I started with creating a scoped credential that referred to my original SAS token.

CREATE DATABASE SCOPED CREDENTIAL [SasTokenAA]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '?sv=2021-06-08&ss=b&srt=co&sp=rwdlacx&se=2022-06-16T09:07:00Z&st=2022-06-16T09:00:00Z&spr=https&sig=xxxxxxxxxx';
GO

As you can see, that token was set to expire on the 8th.

On the 8th, this code worked flawlessly:

SELECT top 1 *,
CAST(REPLACE(fct.filepath(1),'OrderDatePartition=','') AS DATE) AS FilePathDate
FROM 
OPENROWSET 
(
    BULK 'raw/sourcedatapartitionsalesorder/*/*.csv',
    DATA_SOURCE = 'ExternalDataSourceDataLakeSASAA',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE,
    FIELDTERMINATOR ='|'
) AS fct

When I came back on the 9th, however, I was greeted with an error:

11:15:34 AM
Started executing query at Line 12
Msg 13807, Level 16, State 1, Line 1
Content of directory on path 'raw/sourcedatapartitionsalesorder/*/*.csv' cannot be listed.
Total execution time: 00:00:00.165

It is not a very good error message, I’ll say that, but after a short troubleshooting session I narrowed it down to the SAS token for the data lake. Not to worry, I’ll generate a new one and update the scoped credential.

Updating the credentials

Updating a credential is simple - just use ALTER SCOPED CREDENTIAL, like this:

ALTER DATABASE SCOPED CREDENTIAL [SasTokenAA]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = '?sv=2021-06-08&ss=b&srt=co&sp=rwdlacx&se=2023-06-16T09:07:00Z&st=2022-06-16T09:00:00Z&spr=https&sig=xxxxxxxxxx';
GO

…unfortunately I still got the error below:

11:32:12 AM
Started executing query at Line 12
Msg 13807, Level 16, State 1, Line 1
Content of directory on path 'raw/sourcedatapartitionsalesorder/*/*.csv' cannot be listed.
Total execution time: 00:00:00.202

I reached out to a few people, and as I was talking to Pawel Potasinski from the Synapse product team over Twitter, he offhandedly commented on the leading question mark in the SAS string. Turns out that there is an inconsistency in the way Serverless handles scoped credentials. Leaving the question mark in the string is fine for CREATE SCOPED CREDENTIAL, but not for ALTER SCOPED CREDENTIAL.

Running ALTER SCOPED CREDENTIAL again, this time omitting the leading question mark like this:

ALTER DATABASE SCOPED CREDENTIAL [SasTokenAA]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'sv=2021-06-08&ss=b&srt=co&sp=rwdlacx&se=2023-06-16T09:07:00Z&st=2022-06-16T09:00:00Z&spr=https&sig=xxxxxxxxxx';
GO

once again made Serverless behave as expected. I’ve put in feedback and hopefully it’ll be solved soon.

Alexander Arvidsson