Control storage account access for serverless SQL pool in Azure Synapse Analytics

User identity, also known as "Microsoft Entra pass-through", is an authorization type where the identity of the Microsoft Entra user that logged into serverless SQL pool is used to authorize data access. Before accessing the data, the Azure Storage administrator must grant permissions to the Microsoft Entra user. As indicated in the Supported authorization types for database users table, it's not supported for the SQL user type.

You need to be a member of the Storage Blob Data Owner, Storage Blob Data Contributor, or Storage Blob Data Reader role to use your identity to access the data. As an alternative, you can specify fine-grained ACL rules to access files and folders. Even if you are an Owner of a Storage Account, you still need to add yourself into one of the Storage Blob Data roles. To learn more about access control in Azure Data Lake Store Gen2, review the Access control in Azure Data Lake Storage Gen2 article.

Shared access signature (SAS) provides delegated access to resources in a storage account. With SAS, a customer can grant clients access to resources in a storage account without sharing account keys. SAS gives you granular control over the type of access you grant to clients who have an SAS, including validity interval, granted permissions, acceptable IP address range, and the acceptable protocol (https/http).

You can get an SAS token by navigating to the Azure portal -> Storage Account -> Shared access signature -> Configure permissions -> Generate SAS and connection string.

To enable access using an SAS token, you need to create a database-scoped or server-scoped credential

A service principal is the local representation of a global application object in a particular Microsoft Entra tenant. This authentication method is appropriate in cases where storage access is to be authorized for a user application, service, or automation tool. For more information on service principals in Microsoft Entra ID, see Application and service principal objects in Microsoft Entra ID.

The application needs to be registered in Microsoft Entra ID. For more information on the registration process, follow Quickstart: Register an application with the Microsoft identity platform. Once the application is registered, its service principal can be used for authorization.

The service principal should be assigned to the Storage Blob Data Owner, Storage Blob Data Contributor, and Storage Blob Data Reader roles in order for the application to access the data. Even if the service principal is the Owner of a storage account, it still needs to be granted an appropriate Storage Blob Data role. As an alternative way of granting access to storage files and folders, fine-grained ACL rules for service principal can be defined.

To learn more about access control in Azure Data Lake Store Gen2, review Access control in Azure Data Lake Storage Gen2.

Managed service identity or managed identity is also known as an MSI. An MSI is a feature of Microsoft Entra ID that provides Azure services to an Azure service, in this case, for your serverless SQL pool. The MSI is created automatically in Microsoft Entra ID. This identity can be used to authorize the request for data access in Azure Storage.

Before accessing the data, the Azure Storage administrator must grant permissions to the managed service identity for accessing data. Granting permissions to MSI is done the same way as granting permission to any other Microsoft Entra user.

You can access publicly available files placed on Azure storage accounts that allow anonymous access.

To access storage that is protected with the firewall via a user identity, you can use the Azure portal or the Az.Storage PowerShell module.

Azure Storage firewall configuration via Azure portal

1. Search for your Storage Account in Azure portal.
2. In the main navigation menu, go to Networking under Settings.
3. In the section Resource instances, add an exception for your Azure Synapse workspace.
4. Select Microsoft.Synapse/workspaces as a Resource type.
5. Select the name of your workspace as an Instance name.
6. Select Save.

Azure Storage firewall configuration via PowerShell

Follow these steps to configure your storage account and add an exception for the Azure Synapse workspace.

1. Open PowerShell or install PowerShell

2. Install the latest versions of the Az.Storage module and Az.Synapse module, for example in the following script:

   Install-Module -Name Az.Storage -RequiredVersion 3.4.0
   Install-Module -Name Az.Synapse -RequiredVersion 0.7.0
   

   Important

   Make sure that you use at least version 3.4.0. You can check your Az.Storage version by running this command:

   Get-Module -ListAvailable -Name Az.Storage | Select Version
   

3. Connect to your Azure Tenant:

   Connect-AzAccount -Environment AzureChinaCloud
   

4. Define variables in PowerShell:

   • Resource group name - you can find this in Azure portal in the Overview of your storage account.
   • Account Name - name of the storage account that is protected by firewall rules.
   • Tenant ID - you can find this in Azure portal in Microsoft Entra ID, under Properties, in Tenant properties.
   • Workspace Name - Name of the Azure Synapse workspace.

       $resourceGroupName = "<resource group name>"
       $accountName = "<storage account name>"
       $tenantId = "<tenant id>"
       $workspaceName = "<Azure Synapse workspace name>"
   
       $workspace = Get-AzSynapseWorkspace -Name $workspaceName
       $resourceId = $workspace.Id
       $index = $resourceId.IndexOf("/resourceGroups/", 0)
       # Replace G with g - /resourceGroups/ to /resourcegroups/
       $resourceId = $resourceId.Substring(0,$index) + "/resourcegroups/" ` 
           + $resourceId.Substring($index + "/resourceGroups/".Length)
   
       $resourceId
   

   Important

   The value of the $resourceid returned by the PowerShell script should match this template: /subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace} It's important to write resourcegroups in lower case.

5. Add an Azure storage account network rule:

       $parameters = @{
           ResourceGroupName = $resourceGroupName
           Name = $accountName
           TenantId = $tenantId 
           ResourceId = $resourceId
       }
   
       Add-AzStorageAccountNetworkRule @parameters
   

6. Verify that storage account network rule was applied in your storage account firewall. The following PowerShell script compares the $resourceid variable from previous steps to the output of the storage account network rule.

       $parameters = @{
           ResourceGroupName = $resourceGroupName
           Name = $accountName
       }
   
       $rule = Get-AzStorageAccountNetworkRuleSet @parameters
       $rule.ResourceAccessRules | ForEach-Object { 
           if ($_.ResourceId -cmatch "\/subscriptions\/(\w\-*)+\/resourcegroups\/(.)+") { 
               Write-Host "Storage account network rule is successfully configured." -ForegroundColor Green
               $rule.ResourceAccessRules
           } else {
               Write-Host "Storage account network rule is not configured correctly. Remove this rule and follow the steps in detail." -ForegroundColor Red
               $rule.ResourceAccessRules
           }
       }
   

Shared access signatures cannot be used to access firewall-protected storage.

Service principal cannot be used to access firewall-protected storage. Use a managed service identity instead.

You need to enable the Allow trusted Microsoft services setting and assign an Azure role to the system-assigned managed identity for that resource instance.

In this case, the scope of access for the resource instance corresponds to the Azure role assigned to the managed identity.

You cannot access firewall-protected storage using anonymous access.

Microsoft Entra users can access any file on Azure storage if they are members of the Storage Blob Data Owner, Storage Blob Data Contributor, or Storage Blob Data Reader role. Microsoft Entra users don't need credentials to access storage.

SQL authenticated users can't use Microsoft Entra authentication to access storage. They can access storage through a database credential using Managed Identity, SAS Key, Service Principal or if there is public access to the storage.

The following script creates a server-level credential that can be used by the OPENROWSET function to access any file on Azure storage using SAS token. Create this credential to enable a SQL principal to use the OPENROWSET function to read files protected with a SAS key on the Azure storage. The credential name must match the URL.

In the following sample query, replace <mystorageaccountname> with your actual storage account name, and <mystorageaccountcontainername> with the actual container name:

CREATE CREDENTIAL [https://<mystorageaccountname>.dfs.core.chinacloudapi.cn/<mystorageaccountcontainername>]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1BrIPBNJ3VYEIq78%3D';
GO

Optionally, you can use just the base URL of the storage account, without container name.

The following script creates a server-level credential that can be used to access files in a storage using Service principal for authentication and authorization. AppID can be found by visiting App registrations in Azure portal and selecting the App requesting storage access. Secret is obtained during the App registration. AuthorityUrl is URL of Microsoft Entra ID Oauth2.0 authority.

CREATE CREDENTIAL [https://<storage_account>.dfs.core.chinacloudapi.cn/<container>]
WITH IDENTITY = '<AppID>@<AuthorityUrl>' 
, SECRET = '<Secret>'

The following script creates a server-level credential that can be used by OPENROWSET function to access any file on Azure storage using the Azure Synapse workspace managed identity, a special type of managed service identity.

CREATE CREDENTIAL [https://<storage_account>.dfs.core.chinacloudapi.cn/<container>]
WITH IDENTITY='Managed Identity'

Optionally, you can use just the base URL of the storage account, without container name.

Server-level credential isn't required to allow access to publicly available files. Create data source without credential to access publicly available files on Azure storage.

Microsoft Entra users can access any file on Azure storage if they are members of the Storage Blob Data Owner, Storage Blob Data Contributor, or Storage Blob Data Reader roles. Microsoft Entra users don't need credentials to access storage.

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>'
)

SQL authenticated users can't use Microsoft Entra authentication to access storage. They can access storage through a database credential using Managed Identity, SAS Key, Service Principal or if there is public access to the storage.

The following script creates a credential that is used to access files on storage using SAS token specified in the credential. The script creates a sample external data source that uses this SAS token to access storage.

-- Optional: Create MASTER KEY if not exists in database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Very Strong Password>'
GO
CREATE DATABASE SCOPED CREDENTIAL [SasToken]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
     SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=rwdlacup&se=2019-04-18T20:42:12Z&st=2019-04-18T12:42:12Z&spr=https&sig=lQHczNvrk1KEIq78%3D';
GO
CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>',
          CREDENTIAL = SasToken
)

The following script creates a database-scoped credential that can be used to access files in a storage using service principal for authentication and authorization. AppID can be found by visiting App registrations in Azure portal and selecting the App requesting storage access. Secret is obtained during the App registration. AuthorityUrl is URL of Microsoft Entra ID Oauth2.0 authority.

-- Optional: Create MASTER KEY if not exists in database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Very Strong Password>

CREATE DATABASE SCOPED CREDENTIAL [<CredentialName>] WITH
IDENTITY = '<AppID>@<AuthorityUrl>' 
, SECRET = '<Secret>'
GO
CREATE EXTERNAL DATA SOURCE MyDataSource
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>',
          CREDENTIAL = CredentialName
)

The following script creates a database-scoped credential that can be used to impersonate current Microsoft Entra user as Managed Identity of service. The script creates a sample external data source that uses workspace identity to access storage.

-- Optional: Create MASTER KEY if not exists in database:
-- CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Very Strong Password>
CREATE DATABASE SCOPED CREDENTIAL SynapseIdentity
WITH IDENTITY = 'Managed Identity';
GO
CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>',
          CREDENTIAL = SynapseIdentity
)

The database scoped credential doesn't need to match the name of storage account because it will be explicitly used in DATA SOURCE that defines the location of storage.

Database scoped credential isn't required to allow access to publicly available files. Create a data source without credential to access publicly available files on Azure storage.

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.blob.core.chinacloudapi.cn/<container>/<path>'
)