使用 Synapse SQL 安全地載入資料

發行項
06/23/2024

本文主要說明 COPY 陳述式的安全驗證機制，並提供相關範例。在 Synapse SQL 中大量載入資料時，COPY 陳述式是最具彈性且安全的方式。

支援的驗證機制

下列矩陣說明每個檔案類型和儲存體帳戶支援的驗證方法。這適用於來源儲存位置和錯誤檔案位置。

	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 (blob¹)/MSI (dfs²)/SERVICE PRINCIPAL/KEY/AAD	SAS (blob¹)/MSI (dfs²)/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 時，將需要受控識別驗證。

必要條件

安裝 Azure PowerShell。請參閱安裝 PowerShell。
如果您有一般用途 v1 或 Blob 儲存體帳戶，則必須先升級至一般用途 v2。請參閱升級至一般用途 v2 儲存體帳戶。
您必須開啟 Azure 儲存體帳戶 [防火牆與虛擬網路] 設定功能表下方的 [允許信任的 Microsoft 服務存取此儲存體帳戶]。請參閱設定 Azure 儲存體防火牆和虛擬網路。

步驟

如果您有獨立的專用 SQL 集區，請使用 PowerShell 向 Microsoft Entra ID 註冊您的 SQL 伺服器：
```
Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
```
Synapse 工作區中的專用 SQL 集區不需要此步驟。系統指派的工作區受控識別 (SA-MI) 是 Synapse 管理員角色的成員，因此在工作區的專用 SQL 集區上有較高的權限。
建立「一般用途 v2 儲存體帳戶」。如需詳細資訊，請參閱建立儲存體帳戶。
注意
- 如果您有一般用途 v1 或 Blob 儲存體帳戶，則必須「先升級至 v2」。如需詳細資訊，請參閱升級至一般用途 v2 儲存體帳戶。
- 如需 Azure Data Lake Storage Gen2 的已知問題，請參閱 Azure Data Lake Storage Gen2 的已知問題。
在您的儲存體帳戶下，選取 [存取控制 (IAM)]。
選取 [新增 > 角色指派 ] 以開啟 [新增角色指派] 頁面。

指派下列角色。如需詳細步驟，請參閱使用 Azure 入口網站指派 Azure 角色。

設定	值
角色	儲存體 Blob 資料參與者
存取權指派對象	SERVICEPRINCIPAL
成員	裝載您專用 SQL 集區的伺服器或工作區，您已將其向 Microsoft Entra ID 註冊

Azure 入口網站中的 [新增角色指派] 頁面。

注意

僅有具備「擁有者」權限的成員才能執行此步驟。如需了解各種 Azure 內建角色，請參閱 Azure 內建角色。

重要

指定儲存體 Blob 資料的擁有者、參與者或讀取者 Azure 角色。這些角色與 Azure 內建的擁有者、參與者和讀取者角色不同。

授與 Azure RBAC 授權以載入

現在，您可以執行指定「受控識別」的 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 驗證

步驟

在您的儲存體帳戶下，選取 [存取控制 (IAM)]。
選取 [新增 > 角色指派 ] 以開啟 [新增角色指派] 頁面。
指派下列角色。如需詳細步驟，請參閱使用 Azure 入口網站指派 Azure 角色。

設定值

角色儲存體 Blob 資料的擁有者、參與者或讀取者

存取權指派對象 USER

成員 Microsoft Entra 使用者

重要

指定儲存體 Blob 資料的擁有者、參與者或讀取者 Azure 角色。這些角色與 Azure 內建的擁有者、參與者和讀取者角色不同。
設定 Microsoft Entra 驗證。請參閱使用 Azure SQL 設定和管理 Microsoft Entra 驗證。

設定	值
角色	儲存體 Blob 資料的擁有者、參與者或讀取者
存取權指派對象	USER
成員	Microsoft Entra 使用者

使用 Active Directory 連線至您的 SQL 集區，您現在可以在其中執行 COPY 陳述式，而無須指定任何認證：

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

E. 服務主體驗證

步驟

建立 Microsoft Entra 應用程式。
取得應用程式識別碼。
取得驗證金鑰。
取得 V1 OAuth 2.0 權杖端點。
將讀取、寫入和執行權限指派給您的儲存體帳戶上的 Microsoft Entra 應用程式。

現在，您可以執行 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 版

下一步

參閱 COPY 陳述式文章以了解詳細語法
參閱資料載入概觀一文以了解載入的最佳做法

共用方式為