Synapse Storage Integration

Azure Data engineering Part 4

4 min readJan 25, 2022

We can configure access between Azure resources using the service Principals or managed identity.

A service principal name (SPN) is a unique identifier of a service instance. A service Principal in simple terms is a service account. Service principals allow provisioning an account that only has enough permissions and scope to run a task within a predefined set of Azure resources. The Service Principals are commonly used for running automation tasks, runbooks, and Azure deployment.

Managed identities are very similar to Service principals. Managed identities are created for us in Azure. Managed identities are always associated with Azure resources and not with applications or third-party connectors. Service principals can be added for third-party connectors.

System assigned managed identity is linked to a single Azure Resource, like Virtual Machine, a Storage Account, Web App. System assigned managed identity gets automatically created and assigned to that Azure resource, and will also get deleted when you delete the resource. User-Assigned managed identity is created manually and likewise manually assigned to an Azure resource. The lifecycle of a User-Assigned Managed Identity is NOT tied to the lifecycle of the Azure resource to which it is assigned. That means it the Azure resource gets deleted, the User-Assigned Managed Identity will not be deleted from Azure.

Storage account does not have managed identity. We can assign one to it.
Add a new storage account called stgsyn0001 .
Assign managed identity using Powershell.

$stgName = "stgsyn0001"$rg =  "rg-azuretraining-poc-001"$stg = Get-AzStorageAccount `
   -StorageAccountName $stgName `
   -ResourceGroupName $rg $stg.identitySet-AzStorageAccount -ResourceGroupName $rg  `
    -AccountName $stgName  `
    -AssignIdentity$stg = Get-AzStorageAccount `
   -StorageAccountName $stgName `
   -ResourceGroupName $rg$stg.identity

Get Synapse managed identity
Assign storage blob data reader role to Synapse managed identity to read data from lake

#Get system managed Identity
$rg =  "rg-azuretraining-poc-001"
$snypase="synp-azuretraining-poc-ws001"
$Synpaseidentity=(Get-AzSynapseWorkspace  ` 
   -ResourceGroupName $rg  -Name $snypase).Identity#Get application ID/Service principal
Get-AzADServicePrincipal -ObjectId  $identity.PrincipalIDGet-AzStorageAccount  -ResourceGroupName $rgName -Name stgsyn0001
$role = Get-AzRoleDefinition "Storage Blob Data Reader"
$role
$StorageAccount = Get-AzStorageAccount     `
          -ResourceGroupName $rg -Name $stgName
$Storagescope= $StorageAccount.Id
New-AzRoleAssignment   `
    -ObjectId $Synpaseidentity.PrincipalID    `
    -RoleDefinitionName "Storage Blob Data Reader"    `
    -Scope  $Storagescope

Synapse is granted a reader role assignment in IAM.

Get Raj Rathi’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Now we are able to add a Linked connection using Synapse studio. Connect to external data by selecting Gen2 storage.

Using managed identity create linked services as shown below in Synapse studio.

Publish the linked service.

RBAC/ACL

Azure Data Lake Storage Gen2 implements an access control model that supports both Azure role-based access control (Azure RBAC) and access control lists (ACLs).

If you are granting permissions by using only ACLs (no Azure RBAC), then to grant a security principal read or write access to a file, we will need to give the security principal Execute permissions to the root folder of the container, and to each folder in the hierarchy of folders that lead to the file.

Because of the way that access permissions are evaluated by the system, you cannot use an ACL to restrict access that has already been granted by a role assignment. That’s because the system evaluates Azure role assignments first, and if the assignment grants sufficient access permission, ACLs are ignored.

We are ready to fetch data from lined server after RBAC/ACL is complete.

Read data directly from datalake file in Synapse studio

select top 10 *
from openrowset(
bulk 'https://stgsyn0001.dfs.core.windows.net/ttt/t1.csv',
format = 'csv',
parser_version = '2.0' )  with (
date_rep varchar(6),
geo_id varchar(6)  )
as rows

Create external table and read data from datalake

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat1')
CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat1]
WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
FORMAT_OPTIONS (
FIELD_TERMINATOR = ',',
USE_TYPE_DEFAULT = FALSE
))
GO
IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'ttt_stgsyn0001_dfs_core_windows_net1')CREATE EXTERNAL DATA SOURCE [ttt_stgsyn0001_dfs_core_windows_net1]
WITH (
LOCATION = 'abfss://ttt@stgsyn0001.dfs.core.windows.net'
)GO
CREATE EXTERNAL TABLE a11 (
[C1] nvarchar(4000))
WITH (
LOCATION = 't1.csv',
DATA_SOURCE = [ttt_stgsyn0001_dfs_core_windows_net1],
FILE_FORMAT = [SynapseDelimitedTextFormat]
)
GO
SELECT TOP 100 * FROM dbo.a11