你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

使用 Synapse SQL 安全地加载数据

  • 项目

本文重点介绍 COPY 语句的安全身份验证机制，并提供示例。 COPY 语句是在 Synapse SQL 中批量加载数据的最灵活且安全的方法。

支持的身份验证机制

下表介绍了每种文件类型和存储帐户所支持的身份验证方法。 这适用于源存储位置和错误文件位置。

CSV Parquet ORC
Azure blob 存储 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS/KEY SAS/KEY
Azure Data Lake Gen2 SAS/MSI/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD

1：此身份验证方法需要在外部位置路径使用 .blob 终结点 (.blob.core.windows.net)。

2：此身份验证方法需要在外部位置路径使用 .dfs 终结点 (.dfs.core.windows.net)。

A. 以 LF 作为行终止符的存储帐户密钥（Unix 样式的新行）

--Note when specifying the column list, input field numbers start from 1
COPY INTO target_table (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<secret>'),
    ,ROWTERMINATOR='0x0A' --0x0A specifies to use the Line Feed character (Unix based systems)
)

重要

  • 使用十六进制值 (0x0A) 指定换行符。 请注意，COPY 语句会将 \n 字符串解释为 \r\n（回车换行符）。

B. 使用 CRLF 作为行终止符的共享访问签名 (SAS)（Windows 样式新行）

COPY INTO target_table
FROM 'https://adlsgen2account.dfs.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSFSYsz4AkN'),
    ,ROWTERMINATOR='\n'-- COPY command automatically prefixes the \r character when \n (newline) is specified. This results in carriage return newline (\r\n) for Windows based systems.
)

重要

请勿将 ROWTERMINATOR 指定为“\r\n”，它将被解释为“\r\r\n”，并可能导致解析问题。 当指定了 \n（换行符）时，COPY 命令会自动加前缀 \r 字符。 这会导致基于 Windows 的系统显示回车换行符 (\r\n)。

C. 托管标识

将存储帐户附加到 VNet 时，需要进行托管标识身份验证。

先决条件

  1. 安装 Azure PowerShell 中的说明进行操作。 请参阅安装 PowerShell。
  2. 如果有常规用途 v1 或 Blob 存储帐户，则必须先升级到常规用途 v2 帐户。 请参阅升级到常规用途 v2 存储帐户。
  3. 必须在 Azure 存储帐户的“防火墙和虚拟网络”设置菜单下启用“允许受信任的 Microsoft 服务访问此存储帐户”。 请参阅配置 Azure 存储防火墙和虚拟网络。

步骤

  1. 如果你有独立的专用 SQL 池，请使用 PowerShell 向 Microsoft Entra ID 注册 SQL Server：

    Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity

    Synapse 工作区中的专用 SQL 池不需要此步骤。 工作区的系统分配的托管标识 (SA-MI) 是 Synapse 管理员角色的成员，因此对工作区的专用 SQL 池具有提升的特权。

  2. 创建常规用途 v2 存储帐户。 有关详细信息，请参阅创建存储帐户。

    注意

  3. 在存储帐户下，选择“访问控制(IAM)”。

  4. 选择“添加”>“添加角色分配”，打开“添加角色分配”页面 。

  5. 分配以下角色。 有关详细步骤，请参阅使用 Azure 门户分配 Azure 角色。

    设置 值
    角色 存储 Blob 数据参与者
    将访问权限分配到 SERVICEPRINCIPAL
    成员 托管已注册到 Microsoft Entra ID 的专用 SQL 池的服务器或工作区

    Add role assignment page in Azure portal.

    注意

    只有具有“所有者”特权的成员能够执行此步骤。 有关各种 Azure 内置角色，请参阅 Azure 内置角色。

    重要

    指定存储 Blob 数据“所有者”、“参与者”或“读取者”Azure 角色。 这些角色不同于所有者、参与者和读取者 Azure 内置角色。

    Granting Azure RBAC permission to load

  6. 现在可以运行指定“托管标识”的 COPY 语句：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
)

D. Microsoft Entra 身份验证

步骤

  1. 在存储帐户下，选择“访问控制(IAM)”。

  2. 选择“添加”>“添加角色分配”，打开“添加角色分配”页面 。

  3. 分配以下角色。 有关详细步骤，请参阅使用 Azure 门户分配 Azure 角色。

    设置 值
    角色 存储 Blob 数据所有者、参与者或读取者
    将访问权限分配到 USER
    成员 Microsoft Entra 用户

    Add role assignment page in Azure portal.

    重要

    指定存储 Blob 数据“所有者”、“参与者”或“读取者”Azure 角色。 这些角色不同于所有者、参与者和读取者 Azure 内置角色。

    Granting Azure RBAC permission to load

  4. 配置 Microsoft Entra 身份验证。 请参阅如何使用 Azure SQL 配置和管理 Microsoft Entra 身份验证。

  5. 使用 Active Directory 连接到 SQL 池，现在可以在其中运行 COPY 语句，而无需指定任何凭据：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV'
)

E. 服务主体身份验证

步骤

  1. 创建 Microsoft Entra 应用程序。

  2. 获取应用程序 ID。

  3. 获取身份验证密钥。

  4. 获取 V1 OAuth 2.0 令牌终结点。

  5. 在存储帐户上为 Microsoft Entra 应用程序分配读取、写入和执行权限。

  6. 现在可以运行 COPY 语句：

    COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
WITH (
    FILE_TYPE = 'CSV'
    ,CREDENTIAL=(IDENTITY= '<application_ID>@<OAuth_2.0_Token_EndPoint>' , SECRET= '<authentication_key>')
    --CREDENTIAL should look something like this:
    --,CREDENTIAL=(IDENTITY= '92761aac-12a9-4ec3-89b8-7149aef4c35b@https://login.microsoftonline.com/72f714bf-86f1-41af-91ab-2d7cd011db47/oauth2/token', SECRET='juXi12sZ6gse]woKQNgqwSywYv]7A.M')
)

重要

使用 OAuth 2.0 令牌终结点 V1 版本

后续步骤