A common problem that people face while setting up their External Tables with polybase is running into the exception below. "This Request is not authorized to perform this action". The error occurs commonly when enabling Firewall restrictions on the Storage Account or you have configured your external data source incorrectly and the credentials provided does not have access to the storage endpoint.
Exception:
Msg 105019, Level 16, State 1, Line 57
External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:
HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: StorageException: This request is not authorized to perform this operation.'
Historically we did not allow you to connect to a secured storage account via Polybase the connection was not possible with the use of the WASBS driver and the Storage Key as per the following example :
-- Create a database master key if one does not already exist, using your own password. This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo'
;
-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH
IDENTITY = '<my_account>'
, SECRET = '<azure_storage_account_key>'
;
-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH
( LOCATION = 'wasbs://daily@logs.blob.core.windows.net/'
, CREDENTIAL = AzureStorageCredential
, TYPE = HADOOP
)
;
Solution :
In order to connect to a Secured Storage account with Polybase one has to change to the newly created ABFSS driver and Managed Service Identity Credential which connects to a new endpoint.
NOTE : Only Gen 2 Storage Accounts support this configuration
First create the Managed Service Identity for the Logical Server hosting the Azure DW within Azure Powershell
Connect-AzAccount
Select-AzSubscription -SubscriptionId your-subscriptionId
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-database-servername -AssignIdentity
Next grant the relevant permissions to the Managed Service Identity on the storage account
Under your storage account, navigate to Access Control (IAM), and click Add role assignment. Assign Storage Blob Data Contributor RBAC role to your SQL Database server.
Create the new Credential in SQL to make use of the Manged Service Identity
CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';
External Data Source should now be created with newly created credentials , driver and endpoint as per highlighted example below
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net', CREDENTIAL = msi_cred);
For detailed instructions and information refer to the following documentation
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview...
If all of the required actions are not completed you will not be able to connect to the secured storage account.
