Access Azure Storage account via Synapse through Managed Identity

Question

I am trying to connect to Azure Blob storage via Azure synapse through Managed Identity based on the below set of steps:

Assigned an Identity to the Server
Gave access to the Server on Blob storage as contributor
Executed the below queries

Create Master Key

CREATE DATABASE SCOPED CREDENTIAL MSI WITH IDENTITY = 'Managed Service Identity';
```
 CREATE EXTERNAL DATA SOURCE [BlobStorage] WITH
```
(
TYPE = hadoop,

LOCATION = 'abfss://<>@<>.dfs.core.windows.net', CREDENTIAL = MSI )
Created External File Format

When I am trying to create the External table, I am getting the below error:

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: AbfsRestOperationException: Operation failed: "This endpoint does not support BlobStorageEvents or SoftDelete. Please disable these account features if you would like to use this endpoint.", 409, HEAD, https://<<>>.dfs.core.windows.net/<<>>//?upn=false&action=getAccessControl&timeout=90'

So what am I missing out?

user5004137 user5004137 · Accepted Answer · 2021-01-02T12:53:26

Good morning

Here is an example script I use to show connecting to Blob Storage using identiy pass through (either managed or user identity), this example will work with SQL Serverless pools, with managed pools you will need to add additional parameters to specify hadoop. If problems then let me know. Their is also an additional Powershell script you may need at the end.

--Create a master key, once per database
--CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'RandomPassword!!££1132'; 
/* 
DROP EXTERNAL TABLE dbo.Test_useridentity 
DROP EXTERNAL DATA SOURCE blobstorage_via_useridentity

DROP EXTERNAL TABLE dbo.Test_managedidentity 
DROP EXTERNAL DATA SOURCE blobstorage_via_managedidentity 
DROP DATABASE SCOPED CREDENTIAL cred_via_managedidentity

DROP EXTERNAL FILE FORMAT textfile_csv_withheader
*/

--Create external file format for CSV 
CREATE EXTERNAL FILE FORMAT textfile_csv_withheader WITH (  
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS ( 
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '\"',
        FIRST_ROW  = 2
        )
    );
    
--Create Credentials for accessing external data source using various methods
--This has been done at storage account level for this example 
CREATE DATABASE SCOPED CREDENTIAL cred_via_managedidentity WITH IDENTITY =
'Managed Identity' GO

CREATE EXTERNAL DATA SOURCE blobstorage_via_managedidentity WITH (
    CREDENTIAL = cred_via_managedidentity,
    LOCATION = 'abfss://[email protected]' )

--Dont specify the credential for user identity passthrough 
CREATE EXTERNAL DATA SOURCE blobstorage_via_useridentity WITH (
    LOCATION = 'abfss://[email protected]' )

CREATE EXTERNAL TABLE dbo.Test_ManagedIdentity ( [col1] varchar(100),
[col2] varchar(100), [col3] varchar(100) ) WITH ( LOCATION =
'/test.csv',
    DATA_SOURCE = blobstorage_via_managedidentity,
    FILE_FORMAT = [textfile_csv_withheader] );

CREATE EXTERNAL TABLE dbo.Test_UserIdentity ( [col1] varchar(100),
[col2] varchar(100), [col3] varchar(100) ) WITH ( LOCATION =
'/test.csv',
    DATA_SOURCE = blobstorage_via_useridentity,
    FILE_FORMAT = [textfile_csv_withheader] );

--Added use 'synapseaccountname' to allow access via the managed identity as Storage Blob Data Reader 
select * from Test_ManagedIdentity  --This will work for Private Links, others will not
--Added my user to the storage '[email protected]' account as Storage Blob Data Reader, can take 5-10 mins to replicate 
select * from Test_UserIdentity

The powershell script you may need depending on your security setup to allow connecting through the firewall is --

$resourceGroupName = "xxxx-rg-name"
$accountName = "xxxxSynapseAccountNamexxxx"
$tenantId = "Guid for your Azure Tenant"
$resourceId1 = "/subscriptions/xxxxx-aaaa-sssss-guid/resourcegroups/xxxx-rg-name/providers/Microsoft.Synapse/workspaces/xxxxSynapseAccountNamexxxx"

Add-AzStorageAccountNetworkRule -ResourceGroupName $resourceGroupName -Name $accountName -TenantId $tenantId -ResourceId $resourceId1

$rule = Get-AzStorageAccountNetworkRuleSet -ResourceGroupName $resourceGroupName -Name $accountName
$rule.ResourceAccessRules

Hope this helps, let me know if any issues

Access Azure Storage account via Synapse through Managed Identity

1 Answers