// Certificate Errors in Synapse Serverless SQL

I’ve stumbled upon an error a few weeks ago when working with a client and trying to read some Parquet files from a new Blob container from the build-in Storage account in an existing Synapse Workspace. If you’ve only worked with CSV untill now, you should also try out the Parquet files - they know how to perform fast when done right - but that’s possibily another blogpost.

The database has been setup months ago to read data from “old” Blob containers and things was working out pretty nice.

Until this day.

The problem

I coded a new view on the SQL endpoint to extract data from Parquet files stored in the Blob container just created. It worked like a charm for me as the admin of the environment (ofcourse ✌️). But when trying to load data to Power BI from the service-account (a SQL user) I began to get errors like this one:

Msg 15151, Level 16, State 1, Line 9
Cannot find the CREDENTIAL 'https://xxx.dfs.core.windows.net/newfilesystem/path1/path2/*.parquet',   
because it does not exist or you do not have permission.

I started to look for the other queries from Power BI and if they where failing too. But they all worked just fine - it was only this one new view. Banging my head against the wall several times, made me read the documentation from Microsoft you can find it here.

Very nicely a bit down, it states that the credentials for the SQL server does not get updated automatically when new Blobs are created in the Storate account.

So back to the basics of setting this up.

Create the credentials

With my fat fingers I managed to write below statement to create a new credential with a corresponding shared access signature, please note that the ‘?’ before the secret is not to be there as stated in the documentation from Microsoft.

CREATE CREDENTIAL [https://xxx.dfs.core.windows.net/newfilesystem/path1/path2/*.parquet]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=<something very secret and not for all to see 😉>'
GO

Ofcouse with a correct secret from Azure.

Quickly back to Power BI and try things out - but with a heartbroken same result as just before.

Then I just had to read the entire documentation - there is a first for everything - telling that new credentials also needs to be granted access to from the SQL user with below script - you can read the details here:

GRANT REFERENCES ON CREDENTIAL::[https://xxx.dfs.core.windows.net/newfilesystem/path1/path2/*.parquet] TO [powerbi];

YAY!

Now it works again - all hands above head and dancing around the office.

The takeaway is to make sure that the credentials for new Blob containers are added to the Synapse workspace and added reference to the needed SQL users. And read the documentation instead of banging my head against the wall

Happy coding ☕