0

About a month ago, all the external tables built upon parquet files(ADLS Gen2, Synapse) stopped working with the following error message:

Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.", 401

The access key wasn't rotated and even though I tried recreating new database scoped credentials, data sources, they didn't do anything. Then I tried creating a new blob container with the same data and I was able to create external tables and run select statements over them. Does anyone have a clue what the problem could be? At first I thought it was something from Azure, because the coincidence was that they had problems with Synapse. It may seem it's the SAS token, but if it so, why am I not allowed to create other external tables over new SAS tokens? Plus, when a SAS token expires, it throws a 403.

My guess is it is something on configuration for this specific blob, or maybe the Login that I'm using(admin login on SQL Dedicated pool).

Raul
  • 1
  • 3

1 Answers1

0

From the error it seems to be an authorization issue. If you are accessing the storage account from your synapse studio, the managed identity of your synapse workspace should have storage blob data contributor access on the storage account and container you are trying to access. Giving access using SAS key is not the best option rather use managed identity of your synapse workspace.

You can refer this link step 4 to achieve the same.

Snehal Sonwane
  • 71
  • 4
  • Indeed the plan is to switch to using a managed identity. I am running queries from SSMS, that means I'm login into the sql server, but the account i'm using is a Login. I can not find this login account in Azure. What I'm trying to say is that of course, switching the approach would work, but I'm trying to understand what happened behind the scenes that resulted in the login not being a viable approach anymore. Thank you for the answer! – Raul Jul 28 '22 at 08:03
  • If I understand correctly, you are using a login to connect to SSMS where the password is a SAS token for your storage account. The login that you create in sql server will be a local user and it will not be visible in Azure AD. If you want to access the storage account with a user in Azure AD either use managed identity or any user who is integrated with Azure AD (that is also a recommended approach). If you use SAS token for authenticating via login once the sas token expires your login will not be useful. – Snehal Sonwane Jul 28 '22 at 12:16
  • Indeed, but it is on azure sql server. But until one month ago, everything worked properly. And I have 2 servers in the same situation, on both this issue appeared at the same time – Raul Jul 28 '22 at 12:46
  • Was your SAS token valid for a month? Even if you create login in Azure SQL server it will be local user to that server and not be in Azure AD – Snehal Sonwane Jul 28 '22 at 12:54
  • It is valid. I just tried with the driver or whatever it is, "abfs" and it worked - I was able to run create statements for an external table using the same scoped credentials but changed the data source from using the "abfss" to using "abfs". It seems a bit odd so based on my research, it may be a TLS problem because it says that if the versions do not meet, it rejects my file and throws 401. And yes, it was valid. Because that user uses a SAS token to make the request. Now I need to understand the TLS versioning and things adjacent to it. – Raul Jul 28 '22 at 14:03