您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

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

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

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

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

存储权限

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

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

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

支持的存储授权类型

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

备注

Azure AD 直通 是创建工作区时的默认行为。

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

重要

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

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

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

在下表中,可以找到可用的授权类型:

授权类型 SQL 用户 Azure AD 用户
用户标识 不支持 支持
SAS 支持 支持
托管标识 支持 支持

支持的存储和授权类型

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

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

受防火墙保护的存储

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

备注

存储上的防火墙功能现为公共预览版,它在所有公共云区域中都可用。

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

通过 Azure 门户配置

  1. 在 Azure 门户中搜索你的存储帐户。
  2. 转到“设置”部分下的“网络”。
  3. 在“资源实例”部分中,为 Synapse 工作区添加例外情况。
  4. 选择 Microsoft.Synapse/workspaces 作为资源类型。
  5. 选择工作区名称作为实例名称。
  6. 单击“保存”。

通过 PowerShell 进行配置

按照以下步骤配置存储帐户防火墙,并为 Synapse 工作区添加例外。

  1. 打开 PowerShell 或安装 PowerShell

  2. 安装 Az.Storage 3.4.0 模块和 Az.Synapse 0.7.0:

    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
    
  3. 连接到 Azure 租户:

    Connect-AzAccount
    
  4. 在 PowerShell 中定义变量:

    • 资源组名称 - 可以在 Azure 门户中的存储帐户概述中找到此内容。
    • 帐户名称 - 受防火墙规则保护的存储帐户的名称。
    • 租户 ID - 可在 Azure 门户中的“租户中的 Azure Active Directory 信息”中找到此内容。
    • 工作区名称 - Synapse 工作区的名称。
        $resourceGroupName = "<resource group name>"
        $accountName = "<storage account name>"
        $tenantId = "<tenant id>"
        $workspaceName = "<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
    

    重要

    确保资源 ID 在 resourceId 变量的输出中与此模板匹配。

    以小写形式书写“resourcegroups”很重要。 一个资源 id 的示例:

    /subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace}
    
  5. 添加存储网络规则:

        Add-AzStorageAccountNetworkRule -ResourceGroupName $resourceGroupName -Name $accountName -TenantId $tenantId -ResourceId $resourceId
    
  6. 验证是否已在存储帐户中应用规则:

        $rule = Get-AzStorageAccountNetworkRuleSet -ResourceGroupName $resourceGroupName -Name $accountName
        $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

若要允许用户创建或删除凭据,管理员可对用户执行 GRANT/DENY ALTER ANY CREDENTIAL 权限操作:

GRANT ALTER ANY CREDENTIAL TO [user_name];

访问外部存储的数据库用户必须有权使用凭据。

授予使用凭据的权限

若要使用凭据,用户必须拥有对特定凭据的 REFERENCES 权限。 若要向 specific_user 授予对 storage_credential 的 REFERENCES 权限,请执行:

GRANT REFERENCES ON CREDENTIAL::[storage_credential] TO [specific_user];

服务器范围的凭据

当 SQL 登录名在未指定 DATA_SOURCE 的情况下调用 OPENROWSET 函数来读取某个存储帐户上的文件时,将使用服务器范围的凭据。 服务器范围的凭据的名称必须与 Azure 存储的基 URL 相匹配(可以选择后跟容器名称)。 可通过运行 CREATE CREDENTIAL 来添加凭据。 需要提供 CREDENTIAL NAME 参数。

备注

不支持参数 FOR CRYPTOGRAPHIC PROVIDER

服务器级 CREDENTIAL 名称必须与存储帐户(以及可选容器)的完整路径匹配,格式如下:<prefix>://<storage_account_path>[/<container_name>]。 下表描述了存储帐户路径:

外部数据源 前缀 存储帐户路径
Azure Blob 存储 https <storage_account>.blob.core.windows.net
Azure Data Lake Storage Gen1 https <storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Storage Gen2 https <storage_account>.dfs.core.windows.net

服务器范围的凭据允许使用以下身份验证类型来访问 Azure 存储:

如果 Azure AD 用户具有 Storage Blob Data OwnerStorage Blob Data ContributorStorage Blob Data Reader 角色,则可访问 Azure 存储中的任意文件。 Azure AD 用户无需凭据即可访问存储。

SQL 用户无法使用 Azure AD 身份验证来访问存储。

数据库范围的凭据

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

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

如果 Azure AD 用户至少具有 Storage Blob Data OwnerStorage Blob Data ContributorStorage Blob Data Reader 角色,则可访问 Azure 存储中的任意文件。 Azure AD 用户无需凭据即可访问存储。

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

SQL 用户无法使用 Azure AD 身份验证来访问存储。

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

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<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.windows.net/<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 令牌、用户的 Azure AD 标识或工作区的托管标识来访问 Azure 存储。

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

-- Create databases scoped credential that use Managed Identity or SAS token. 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'

-- 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.windows.net/<container>/<path>'
-- Uncomment one of these options depending on authentication method that you want to use to access data source:
--,CREDENTIAL = WorkspaceIdentity 
--,CREDENTIAL = SasCredential 
)

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

后续步骤

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