在 Azure Synapse Analytics 中控制无服务器 SQL 池对存储帐户的访问

项目
08/14/2024

无服务器 SQL 池查询直接从 Azure 存储中读取文件。对 Azure 存储中文件的访问权限是在以下两个级别控制的：

存储级别 - 用户应具有访问基础存储文件的权限。你的存储管理员应当允许 Microsoft Entra 主体读取/写入文件，或者生成将用来访问存储的共享访问签名 (SAS) 密钥。
SQL 服务级别 - 用户应已被授予使用外部表读取数据或执行 OPENROWSET 函数的权限。在此部分中详细了解所需权限。

本文介绍可用的凭据类型，以及为 SQL 和 Microsoft Entra 用户进行的凭据查找是如何执行的。

存储权限

Synapse Analytics 工作区中的无服务器 SQL 池可以读取 Azure Data Lake Storage 中存储的文件的内容。需要配置存储的权限让执行 SQL 查询的用户能够读取文件。有 3 种方法来启用文件的访问权限：

基于角色的访问控制 (RBAC)：通过它能够将角色分配给存储所在的租户中的某些 Microsoft Entra 用户。读取者必须是存储帐户上存储 Blob 数据读取者、存储 Blob 数据参与者或存储 Blob 数据所有者角色的成员。在 Azure 存储中写入数据的用户必须是存储 Blob 数据参与者或存储 Blob 数据所有者角色的成员。存储所有者角色并不意味着用户也是存储数据所有者。
通过访问控制列表 (ACL)，可对 Azure 存储中的文件和目录定义精细的“读取(R)”、“写入(W)”和“执行(X)”权限。可将 ACL 分配给 Microsoft Entra 用户。如果读取者要读取 Azure 存储中某个路径上的文件，必须对该文件路径中的每个文件夹具有“执行(X)”ACL 权限，对文件具有“读取(R)” ACL 权限。详细了解如何设置存储层中的 ACL 权限。
共享访问签名 (SAS) ：使读取者能够使用限时令牌访问 Azure Data Lake Storage 上的文件。读取者甚至无需进行 Microsoft Entra 用户身份验证。 SAS 令牌包含授予读取者的权限以及令牌的有效期限。 SAS 令牌是任何不需要位于同一 Microsoft Entra 租户中的用户进行时间限制的访问的好选择。可以在存储帐户或特定目录上定义 SAS 令牌。若要详细了解 SAS 令牌，请参阅使用共享访问签名 (SAS) 授予对 Azure 存储资源的受限访问权限。

替代方法是，可通过允许匿名访问来使你的文件公开可用。如果你有非公共数据，则不得使用此方法。

支持的存储授权类型

如果文件不是公开可用的，则登录到无服务器 SQL 池的用户必须获得访问和查询 Azure 存储中文件的授权。可以使用四种授权类型来访问非公共存储：用户标识、共享访问签名、服务主体和托管标识。

注意

Microsoft Entra 直通是创建工作区时的默认行为。

用户标识（也称为“Microsoft Entra 直通”）是一种授权类型。使用这种授权时，登录到无服务器 SQL 池的 Microsoft Entra 用户的标识将用于授予数据访问权限。在访问数据之前，Azure 存储管理员必须向 Microsoft Entra 用户授予权限。如数据库用户支持的授权类型表中所示，SQL 用户类型不支持该类型。

重要

客户端应用程序可能会缓存 Microsoft Entra 身份验证令牌。例如，Power BI 会缓存 Microsoft Entra 令牌，并在一小时内重复使用同一令牌。如果在执行查询的过程中，该令牌过期，则长时间运行的查询可能会失败。如果查询时遇到 Microsoft Entra 访问令牌过期导致查询失败，请考虑切换到服务主体、托管标识或共享访问签名。

需要是存储 Blob 数据所有者、存储 Blob 数据参与者或存储 Blob 数据读取者角色的成员才能使用标识访问数据。或者，你可以指定细化 ACL 规则以访问文件和文件夹。即使你是存储帐户的所有者，也仍需将自己添加到存储 Blob 数据角色之一。若要详细了解 Azure Data Lake Store Gen2 中的访问控制，请参阅 Azure Data Lake Storage Gen2 中的访问控制一文。

共享访问签名 (SAS) 提供对存储帐户中的资源的委托访问权限。使用 SAS，客户可向客户端授予对存储帐户中的资源的访问权限，而无需共享帐户密钥。通过 SAS 可以精细控制向具有 SAS 的客户端授予的访问权限类型，包括有效期间隔、授予的权限、可接受的 IP 地址范围，以及可接受的协议 (https/http)。

可以导航到“Azure 门户”->“存储帐户”->“共享访问签名”->“配置权限”->“生成 SAS 和连接字符串”来获取 SAS 令牌。

重要

生成共享访问签名 (SAS) 令牌时，该令牌开头会包含问号 (?)。若要在无服务器 SQL 池中使用该令牌，必须在创建凭据时删除问号 (?)。例如：

SAS 令牌：?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=lQHcEIq78%3D

若要允许使用 SAS 令牌进行访问，需要创建数据库范围或服务器范围的凭据

重要

无法使用 SAS 令牌访问专用存储帐户。请考虑切换到托管标识或 Microsoft Entra 直通身份验证以访问受保护的存储。

跨租户方案

如果 Azure 存储位于与 Synapse 无服务器 SQL 池不同的租户中，则建议使用通过服务主体进行授权的方法。也可以进行 SAS 授权，但不支持托管标识。

授权类型	受防火墙保护的存储	非受防火墙保护的存储
SAS	支持	支持
Service Principal	不支持	支持

注意

如果 Azure 存储受 Azure 存储防火墙保护，则服务主体不受支持。

支持用于数据库用户的授权类型

下表提供了适用于 Azure Synapse Analytics 无服务器 SQL 终结点的不同登录方法的可用 Azure 存储授权类型：

授权类型	SQL 用户	Microsoft Entra 用户	服务主体
用户标识	不支持	支持	支持
SAS	支持	支持	支持
服务主体	支持	支持	支持
托管标识	支持	支持	支持

支持的存储和授权类型

可以使用授权类型和 Azure 存储类型的以下组合：

授权类型	Blob 存储	ADLS Gen2
SAS	支持	支持
Service Principal	支持	支持
托管标识	支持	支持
用户标识	支持	支持

跨租户方案

如果 Azure 存储位于与 Azure Synapse Analytics 无服务器 SQL 池不同的租户中，则建议使用通过服务主体进行授权的方法。也可以使用共享访问签名授权。不支持托管服务标识。

授权类型	受防火墙保护的存储	非受防火墙保护的存储
SAS	支持	支持
服务主体	不支持	支持

注意

如果 Azure 存储受 Azure 存储防火墙保护，且位于另一个租户中，则服务主体不受支持。请改用共享访问签名 (SAS)。

受防火墙保护的存储

通过创建资源实例规则，可以将存储帐户配置为允许访问特定无服务器 SQL 池。访问受防火墙保护的存储时，请使用用户标识或托管标识。

注意

Azure 存储上的防火墙功能目前为公共预览版。

下表提供了适用于 Azure Synapse Analytics 无服务器 SQL 终结点的不同登录方法且受防火墙保护的可用 Azure 存储授权类型：

授权类型	SQL 用户	Microsoft Entra 用户	服务主体
用户标识	不支持	支持	支持
SAS	不支持	不支持	不支持
服务主体	不支持	不支持	不支持
托管标识	支持	支持	支持

若要通过用户标识访问受防火墙保护的存储，可以使用 Azure 门户或 Az.Storage PowerShell 模块。

通过 Azure 门户配置 Azure 存储防火墙

在 Azure 门户中搜索你的存储帐户。
在主导航菜单中，转到“设置”下的“网络”。
在“资源实例”部分中，为 Azure Synapse 工作区添加例外情况。
选择 Microsoft.Synapse/workspaces 作为资源类型。
选择你的工作区名称作为实例名称。
选择“保存” 。

通过 PowerShell 配置 Azure 存储防火墙

按照以下步骤配置存储帐户，然后为 Azure Synapse 工作区添加例外。

打开 PowerShell 或安装 PowerShell
安装最新版本的 Az.Storage 模块和 Az.Synapse 模块，例如在以下脚本中：
```
Install-Module -Name Az.Storage -RequiredVersion 3.4.0
Install-Module -Name Az.Synapse -RequiredVersion 0.7.0
```
重要

确保使用的最低版本为 3.4.0。可以通过运行以下命令来检查 Az.Storage 版本：
```
Get-Module -ListAvailable -Name Az.Storage | Select Version
```

连接到 Azure 租户：

Connect-AzAccount -Environment AzureChinaCloud

在 PowerShell 中定义变量：
- 资源组名称 - 可以在 Azure 门户的存储帐户概述中找到此内容。
- 帐户名称 - 受防火墙规则保护的存储帐户的名称。
- 租户 ID - 在 Azure 门户的 Microsoft Entra ID 中，在“属性”下的“租户属性”中可找到此 ID。
- 工作区名称 - Azure Synapse 工作区的名称。
```
    $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
```
重要

PowerShell 脚本返回的 $resourceid 的值应与此模板匹配：/subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace} 以小写形式书写“resourcegroups”很重要。

添加 Azure 存储帐户网络规则：

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

    Add-AzStorageAccountNetworkRule @parameters

验证存储帐户防火墙中是否应用了存储帐户网络规则。以下 PowerShell 脚本将前面步骤中的 $resourceid 变量与存储帐户网络规则的输出进行比较。

    $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
        }
    }

凭据

若要查询 Azure 存储中的文件，无服务器 SQL 池终结点需要一个包含身份验证信息的凭据。使用两种类型的凭据：

服务器级凭据用于通过 OPENROWSET 函数执行的即席查询。凭据名称必须与存储 URL 匹配。
数据库范围的凭据用于外部表。外部表使用应当用来访问存储的凭据来引用 DATA SOURCE。

授予管理凭据的权限

若要授予管理凭据的能力，请执行以下操作：

若要允许用户创建或删除服务器级凭据，管理员必须在主数据库中授予登陆 ALTER ANY CREDENTIAL 权限。例如：
```
GRANT ALTER ANY CREDENTIAL TO [login_name];
```
若要允许用户创建或删除数据库范围的凭据，管理员必须向用户数据库中的数据库用户授予对数据库的 CONTROL 权限。例如：
```
GRANT CONTROL ON DATABASE::[database_name] TO [user_name];
```

授予使用凭据的权限

访问外部存储的数据库用户必须有权使用凭据。若要使用凭据，用户必须拥有对特定凭据的 REFERENCES 权限。

若要为登陆授予对服务器级凭据的 REFERENCES 权限，请在主数据库中使用以下 T-SQL 查询：

GRANT REFERENCES ON CREDENTIAL::[server-level_credential] TO [login_name];

若要为数据集用户授予对数据库范围凭据的 REFERENCES 权限，请在用户数据集使用以下 T-SQL 查询：

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[database-scoped_credential] TO [user_name];

服务器级别凭据

当 SQL 登录名在未指定 DATA_SOURCE 的情况下调用 OPENROWSET 函数来读取某个存储帐户上的文件时，将使用服务器级凭据。

服务器级凭据的名称必须与 Azure 存储的基 URL 相匹配（可以选择后跟容器名称）。可通过运行 CREATE CREDENTIAL 来添加凭据。必须提供 CREDENTIAL NAME 参数。

注意

不支持参数 FOR CRYPTOGRAPHIC PROVIDER。

服务器级凭据名称必须匹配以下格式：<prefix>://<storage_account_path>[/<container_name>]。下表描述了存储帐户路径：

外部数据源	前缀	存储帐户路径
Azure Blob 存储	`https`	`<storage_account>.blob.core.chinacloudapi.cn`
Azure Data Lake Storage Gen2	`https`	`<storage_account>.dfs.core.chinacloudapi.cn`

然后服务器级凭据就能使用以下身份验证类型访问 Azure 存储：

如果 Microsoft Entra 用户是存储 Blob 数据所有者、存储 Blob 数据参与者或存储 Blob 数据读取者角色的成员，则他们可以访问 Azure 存储上的任何文件。 Microsoft Entra 用户无需凭据即可访问存储。

SQL 经过身份验证的用户无法使用 Microsoft Entra 身份验证来访问存储。他们可以使用托管标识、SAS 密钥、服务主体通过数据库凭据访问存储，或者如果存在对该存储的公共访问权限，他们也可以访问该存储。

以下脚本将创建一个服务器级凭据，OPENROWSET 函数可以使用该凭据通过 SAS 令牌访问 Azure 存储上的任何文件。创建此凭据，使 SQL 主体能够使用 OPENROWSET 函数读取 Azure 存储上受 SAS 密钥保护的文件。凭据名称必须与 URL 匹配。

在以下示例查询中，将 <mystorageaccountname> 替换为实际的存储帐户名称，并将 <mystorageaccountcontainername> 替换为实际容器名称：

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

或者，可以只使用存储帐户的基 URL，而不使用容器名称。

以下脚本将创建服务器级凭据，该凭据可用于访问使用服务主体进行身份验证和授权的存储中的文件。可以通过访问 Azure 门户中的“应用注册”并选择请求存储访问的应用来找到 AppID。可以在应用注册过程中获取机密。 AuthorityUrl 是 Microsoft Entra ID Oauth2.0 颁发机构的 URL。

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

以下脚本创建了一个服务器级凭据，OPENROWSET 函数可以使用该凭据访问使用 Azure Synapse 工作区托管标识（一种特殊类型的托管服务标识）的 Azure 存储上的任何文件。

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

或者，可以只使用存储帐户的基 URL，而不使用容器名称。

数据库范围的凭据

当任何主体在使用 DATA_SOURCE 的情况下调用 OPENROWSET 函数时，或在不访问公共文件的外部表中选择数据时，将使用数据库范围的凭据。数据库范围的凭据不需要匹配存储帐户的名称，定义存储位置的 DATA SOURCE 中会引用该名称。

数据库范围的凭据允许使用以下身份验证类型来访问 Azure 存储：

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

下面的脚本创建一个凭据，该凭据用来通过其中指定的 SAS 令牌访问存储中的文件。该脚本可创建一个示例外部数据源，该数据源使用此 SAS 令牌访问存储。

-- 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
)

以下脚本将创建数据库范围的凭据，该凭据可用于访问使用服务主体进行身份验证和授权的存储中的文件。可以通过访问 Azure 门户中的“应用注册”并选择请求存储访问的应用来找到 AppID。可以在应用注册过程中获取机密。 AuthorityUrl 是 Microsoft Entra ID Oauth2.0 颁发机构的 URL。

-- 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
)

以下脚本创建一个数据库范围的凭据，该凭据可用于以服务托管标识的身份模拟当前的 Microsoft Entra 用户。该脚本可创建一个示例外部数据源，该数据源使用工作区标识访问存储。

-- 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
)

数据库范围的凭据不需要匹配存储帐户的名称，因为它将在定义存储位置的 DATA SOURCE 中显式使用。

若要允许访问公开可用的文件，不需要使用数据库范围的凭据。创建不带凭据的数据源，以访问 Azure 存储上公开可用的文件。

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

在外部数据源中使用数据库范围的凭据，以便指定将使用哪种身份验证方法来访问此存储：

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>',
          CREDENTIAL = <name of database scoped credential> 
)

示例

访问公开可用的数据源

使用以下脚本创建一个表，用以访问公开可用的数据源。

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
       WITH ( FORMAT_TYPE = PARQUET)
GO
CREATE EXTERNAL DATA SOURCE publicData
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<public_container>/<path>' )
GO

CREATE EXTERNAL TABLE dbo.userPublicData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [publicData],
       FILE_FORMAT = [SynapseParquetFormat] )

数据库用户可以使用外部表或使用引用数据源的 OPENROWSET 函数从数据源读取文件的内容：

SELECT TOP 10 * FROM dbo.userPublicData;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet',
                                DATA_SOURCE = 'mysample',
                                FORMAT='PARQUET') as rows;
GO

使用凭据访问数据源

修改以下脚本来创建一个外部表，用以使用 SAS 令牌、用户的 Microsoft Entra 标识或工作区的托管标识来访问 Azure 存储。

-- Create master key in databases with some password (one-off per database)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>'
GO

-- Create databases scoped credential that use Managed Identity, SAS token or service principal. User needs to create only database-scoped credentials that should be used to access data source:

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity'
GO
CREATE DATABASE SCOPED CREDENTIAL SasCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-1********ZVsTOL0ltEGhf54N8KhDCRfLRI%3D'
GO
CREATE DATABASE SCOPED CREDENTIAL SPNCredential WITH
IDENTITY = '**44e*****8f6-ag44-1890-34u4-22r23r771098@https://login.partner.microsoftonline.cn/**do99dd-87f3-33da-33gf-3d3rh133ee33/oauth2/token' 
, SECRET = '.7OaaU_454azar9WWzLL.Ea9ePPZWzQee~'
GO
-- Create data source that one of the credentials above, external file format, and external tables that reference this data source and file format:

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)
GO

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.chinacloudapi.cn/<container>/<path>'
-- Uncomment one of these options depending on authentication method that you want to use to access data source:
--,CREDENTIAL = WorkspaceIdentity 
--,CREDENTIAL = SasCredential 
--,CREDENTIAL = SPNCredential
)

CREATE EXTERNAL TABLE dbo.userData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [mysample],
       FILE_FORMAT = [SynapseParquetFormat] );

数据库用户可以使用外部表或使用引用数据源的 OPENROWSET 函数从数据源读取文件的内容：

SELECT TOP 10 * FROM dbo.userdata;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet', DATA_SOURCE = 'mysample', FORMAT='PARQUET') as rows;
GO

后续步骤

以下文章可帮助你了解如何查询不同的文件夹类型、文件类型，以及如何创建和使用视图：

通过

在 Azure Synapse Analytics 中控制无服务器 SQL 池对存储帐户的访问

存储权限

支持的存储授权类型

跨租户方案

支持用于数据库用户的授权类型

支持的存储和授权类型

跨租户方案

受防火墙保护的存储

通过 Azure 门户配置 Azure 存储防火墙

通过 PowerShell 配置 Azure 存储防火墙

凭据

授予管理凭据的权限

授予使用凭据的权限

服务器级别凭据

数据库范围的凭据

示例

访问公开可用的数据源

使用凭据访问数据源

后续步骤

其他资源