Caricare i dati in modo sicuro tramite Synapse SQL

Questo articolo descrive i meccanismi di autenticazione sicura per l'istruzione COPY e ne fornisce esempi. L'istruzione COPY rappresenta il modo più flessibile e sicuro per il caricamento bulk dei dati in Synapse SQL.

Meccanismi di autenticazione supportati

La matrice seguente descrive i metodi di autenticazione supportati per ogni tipo di file e di account di archiviazione. Si applica alla posizione di archiviazione di origine e al percorso del file di errore.

	CSV	Parquet	ORC
Archiviazione BLOB di Azure	FIRMA DI ACCESSO CONDIVISO/IDENTITÀ DEL SERVIZIO GESTITA/ENTITÀ SERVIZIO/CHIAVE/AAD	FIRMA DI ACCESSO CONDIVISO/CHIAVE	FIRMA DI ACCESSO CONDIVISO/CHIAVE
Azure Data Lake Gen2	FIRMA DI ACCESSO CONDIVISO/IDENTITÀ DEL SERVIZIO GESTITA/ENTITÀ SERVIZIO/CHIAVE/AAD	FIRMA DI ACCESSO CONDIVISO (blob1)/IDENTITÀ DEL SERVIZIO GESTITA (dfs2)/ENTITÀ SERVIZIO/CHIAVE/AAD	FIRMA DI ACCESSO CONDIVISO (blob1)/IDENTITÀ DEL SERVIZIO GESTITA (dfs2)/ENTITÀ SERVIZIO/CHIAVE/AAD

1: per questo metodo di autenticazione è necessario l'endpoint .blob (.blob.core.windows.net) nel percorso esterno.

2: per questo metodo di autenticazione è necessario l'endpoint .dfs (.dfs.core.windows.net) nel percorso esterno.

R. Chiave dell'account di archiviazione con LF come carattere di terminazione della riga (nuova riga in stile 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)
)

Importante

• Usare il valore esadecimale (0x0A) per specificare il carattere di nuova riga/avanzamento riga. Si noti che l'istruzione COPY interpreta la stringa \n come \r\n (nuova riga di ritorno a capo).

B. Firme di accesso condiviso (SAS) con CRLF come carattere di terminazione della riga (nuova riga in stile 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.
)

Importante

Non specificare il ROWTERMINATOR come '\r\n' perché viene interpretato come '\r\r\n' e può provocare problemi di analisi. Il comando COPY antepone automaticamente il carattere \r quando viene specificato \n (nuova riga). Ciò comporta la nuova riga di ritorno a capo (\r\n) per i sistemi basati su Windows.

C. Identità gestita

L'autenticazione dell'identità gestita è obbligatoria quando l'account di archiviazione è collegato a una rete virtuale.

Prerequisiti

1. Installare Azure PowerShell. Fare riferimento a Installare PowerShell.
2. Se si dispone di un account di archiviazione BLOB o per utilizzo generico v1, prima è necessario eseguire l'aggiornamento all'utilizzo generico v2. Fare riferimento a Eseguire l'aggiornamento all'account di archiviazione per utilizzo generico v2.
3. È necessario avere attivato l'opzione Consenti ai servizi Microsoft attendibili di accedere a questo account di archiviazione nel menu delle impostazioni Firewall e reti virtuali di tale account. Fare riferimento a Configurare le reti virtuali e i firewall di Archiviazione di Azure.

Passaggi

1. Se è disponibile un pool SQL dedicato autonomo, registrare il server SQL con Microsoft Entra ID usando PowerShell:

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

   Questo passaggio non è necessario per i pool SQL dedicati situati all'interno di un'area di lavoro di Synapse. L'identità gestita assegnata dal sistema (SA-MI) dell'area di lavoro è membro del ruolo Amministratore di Synapse e dispone quindi di privilegi elevati nei pool SQL dedicati dell'area di lavoro.

2. Creare un account di archiviazione per utilizzo generico v2. Per altre informazioni, vedere Creare un account di archiviazione.

   Nota

   • Se si dispone di un account di archiviazione BLOB o per utilizzo generico v1, è necessario prima eseguire l'aggiornamento a v2. Per altre informazioni, Eseguire l'aggiornamento alla versione 2 di un account di archiviazione per uso generico.
   • Per i problemi noti relativi ad Azure Data Lake Storage Gen2, vedere Problemi noti con Azure Data Lake Storage Gen2.

3. Nell'account di archiviazione selezionare Controllo di accesso (IAM).

4. Selezionare Aggiungi>Aggiungi assegnazione di ruolo per aprire la pagina Aggiungi assegnazione di ruolo.

5. Assegnare il ruolo seguente. Per la procedura dettagliata, vedere Assegnare ruoli di Azure usando il portale di Azure.

   Impostazione	Valore
   Ruolo	Collaboratore dati BLOB di archiviazione
   Assegna accesso a	SERVICEPRINCIPAL
   Membri	server o area di lavoro che ospita il pool SQL dedicato registrato con Microsoft Entra ID

   

   Nota

   Solo i membri con il privilegio di proprietario possono eseguire questo passaggio. Per informazioni sui diversi ruoli predefiniti di Azure, fare riferimento a Ruoli predefiniti di Azure.

   Importante

   Specificare il ruolo di Azure Proprietario, collaboratore o lettore dei dati dei BLOBdi archiviazione. Questi ruoli sono diversi da quelli predefiniti di Azure di proprietario, collaboratore e lettore.

   

6. È ora possibile eseguire l'istruzione COPY specificando "Identità gestita":

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

D. Autenticazione Microsoft Entra

Passaggi

1. Nell'account di archiviazione selezionare Controllo di accesso (IAM).

2. Selezionare Aggiungi>Aggiungi assegnazione di ruolo per aprire la pagina Aggiungi assegnazione di ruolo.

3. Assegnare il ruolo seguente. Per la procedura dettagliata, vedere Assegnare ruoli di Azure usando il portale di Azure.

   Impostazione	Valore
   Ruolo	Proprietario, collaboratore o lettore dei dati dei BLOB di archiviazione
   Assegna accesso a	USER
   Membri	Utente di Microsoft Entra

   

   Importante

   Specificare il ruolo di Azure Proprietario, collaboratore o lettore dei dati dei BLOBdi archiviazione. Questi ruoli sono diversi da quelli predefiniti di Azure di proprietario, collaboratore e lettore.

   

4. Configurare l'autenticazione di Microsoft Entra. Fare riferimento a Configurare e gestire l'autenticazione di Microsoft Entra con Azure SQL.

5. Connettersi al pool SQL tramite Active Directory in cui è ora possibile eseguire l'istruzione COPY senza specificare alcuna credenziale:

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

E. Autenticazione tramite entità servizio

Passaggi

1. Creare un'applicazione Microsoft Entra.

2. Ottenere l'ID applicazione.

3. Ottenere la chiave di autenticazione.

4. Ottenere l'endpoint di token OAuth 2.0 V1.

5. Assegnare le autorizzazioni di lettura, scrittura ed esecuzione all'applicazione Microsoft Entra nell'account di archiviazione.

6. È ora possibile eseguire l'istruzione 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')
   )
   

Importante

Usare la versione V1 dell'endpoint di token OAuth 2.0

Passaggi successivi

• Per la sintassi dettagliata, vedere l'articolo sull'istruzione COPY
• Per le procedure consigliate per il caricamento, vedere l'articolo Panoramica sul caricamento dei dati