Caricare i dati in modo sicuro tramite Synapse SQL

Articolo
04/23/2024

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 (blob¹)/IDENTITÀ DEL SERVIZIO GESTITA (dfs²)/ENTITÀ SERVIZIO/CHIAVE/AAD	FIRMA DI ACCESSO CONDIVISO (blob¹)/IDENTITÀ DEL SERVIZIO GESTITA (dfs²)/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

Installare Azure PowerShell. Fare riferimento a Installare PowerShell.
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.
È 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

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.
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.
Nell'account di archiviazione selezionare Controllo di accesso (IAM).
Selezionare Aggiungi>Aggiungi assegnazione di ruolo per aprire la pagina Aggiungi assegnazione di ruolo.

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

Pagina Aggiungi assegnazione di ruolo nel portale di Azure.

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 BLOB di archiviazione. Questi ruoli sono diversi da quelli predefiniti di Azure di proprietario, collaboratore e lettore.

Concessione dell'autorizzazione del controllo degli accessi in base al ruolo di Azure per il caricamento

È 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

Nell'account di archiviazione selezionare Controllo di accesso (IAM).
Selezionare Aggiungi>Aggiungi assegnazione di ruolo per aprire la pagina Aggiungi assegnazione di ruolo.
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 BLOB di archiviazione. Questi ruoli sono diversi da quelli predefiniti di Azure di proprietario, collaboratore e lettore.
Configurare l'autenticazione di Microsoft Entra. Fare riferimento a Configurare e gestire l'autenticazione di Microsoft Entra con Azure SQL.
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'
)
```

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

E. Autenticazione tramite entità servizio

Passaggi

Creare un'applicazione Microsoft Entra.
Ottenere l'ID applicazione.
Ottenere la chiave di autenticazione.
Ottenere l'endpoint di token OAuth 2.0 V1.
Assegnare le autorizzazioni di lettura, scrittura ed esecuzione all'applicazione Microsoft Entra nell'account di archiviazione.

È 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

Condividi tramite

Caricare i dati in modo sicuro tramite Synapse SQL

Meccanismi di autenticazione supportati

R. Chiave dell'account di archiviazione con LF come carattere di terminazione della riga (nuova riga in stile Unix)

B. Firme di accesso condiviso (SAS) con CRLF come carattere di terminazione della riga (nuova riga in stile Windows)

C. Identità gestita

Prerequisiti

Passaggi

D. Autenticazione Microsoft Entra

Passaggi

E. Autenticazione tramite entità servizio

Passaggi

Passaggi successivi

Commenti e suggerimenti

Risorse aggiuntive