Charger des données de façon sécurisée à l’aide de SQL Synapse

Article
10/26/2023

Cet article met en évidence et fournit des exemples sur les mécanismes d’authentification sécurisée pour l’instruction COPY. L’instruction COPY est la manière la plus flexible et sécurisée de charger des données en bloc dans SQL Synapse.

Mécanismes d’authentification pris en charge

La matrice suivante décrit les méthodes d’authentification prises en charge pour chaque type de fichier et compte de stockage. Elle s’applique à l’emplacement de stockage source et à l’emplacement du fichier d’erreur.

	CSV	Parquet	ORC
Stockage Blob Azure	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 : Le point de terminaison .blob (.blob.core.windows.net) dans le chemin de votre emplacement externe est nécessaire pour cette méthode d’authentification.

2 : Le point de terminaison .dfs (.dfs.core.windows.net) dans le chemin de votre emplacement externe est nécessaire pour cette méthode d’authentification.

R. Clé de compte de stockage avec LF comme indicateur de fin de ligne (nouvelle ligne de style 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)
)

Important

Utilisez la valeur hexadécimale (0x0A) pour spécifier le caractère de saut de ligne/nouvelle ligne. Notez que l’instruction COPY interprète la chaîne \n comme \r\n (retour chariot avec nouvelle ligne).

B. Signatures d’accès partagé (SAP) avec CRLF comme indicateur de fin de ligne (nouvelle ligne de style 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.
)

Important

Ne spécifiez pas ROWTERMINATOR comme « \r\n », qui sera interprété comme « \r\r\n » et peut entraîner des problèmes d’analyse. La commande COPY ajoute automatiquement le caractère \r quand vous spécifiez le caractère \n (nouvelle ligne). Cela se traduit par un retour chariot avec nouvelle ligne (\r\n) pour les systèmes Windows.

C. Identité managée

L’authentification de l’identité managée est requise lorsque votre compte de stockage est joint à un réseau virtuel.

Prérequis

Installez Azure PowerShell. Reportez-vous à Installer PowerShell.
Si vous disposez d’un compte de stockage d’objets blob ou v1 universel, vous devez commencer par le mettre à niveau vers un compte v2 universel. Consultez Mettre à niveau vers un compte de stockage v2 universel.
Vous devez avoir activé Autoriser les services Microsoft approuvés à accéder à ce compte de stockage sous le menu de paramètres Pare-feux et réseaux virtuels du compte Stockage Azure. Consultez Configurer des pare-feu et des réseaux virtuels dans le stockage Azure.

Étapes

Si vous disposez d’un pool SQL dédié et autonome, inscrivez votre serveur SQL auprès de Microsoft Entra ID à l’aide de PowerShell :
```
Connect-AzAccount
Select-AzSubscription -SubscriptionId <subscriptionId>
Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
```
Cette étape n’est pas nécessaire pour les pools SQL dédiés qui se trouvent dans un espace de travail Synapse. L’identité managée affectée par le système (SA-MI) de l’espace de travail est membre du rôle Administrateur Synapse, et dispose donc de privilèges élevés sur les pools SQL dédiés de l’espace de travail.
Créez un compte de stockage v2 universel. Pour plus d’informations, consultez la rubrique Création d’un compte de stockage .
Notes
- Si vous disposez d’un compte de stockage d’objets blob ou v1 universel, vous devez commencer par le mettre à niveau vers v2. Pour obtenir plus d’informations, consultez Mettre à niveau vers un compte de stockage v2 à usage général.
- Pour en savoir plus sur les problèmes connus d’Azure Data Lake Storage Gen2, consultez Problèmes connus d’Azure Data Lake Storage Gen2.
Sous votre compte de stockage, sélectionnez Contrôle d’accès (IAM) .
Sélectionnez Ajouter>Ajouter une attribution de rôle pour ouvrir la page Ajouter une attribution de rôle.

Attribuez le rôle suivant. Pour connaître les étapes détaillées, consultez Attribuer des rôles Azure à l’aide du portail Azure.

Paramètre	Valeur
Role	Contributeur aux données Blob du stockage
Attribuer l’accès à	SERVICEPRINCIPAL
Membres	Serveur ou espace de travail hébergeant votre pool SQL dédié que vous avez inscrit auprès de Microsoft Entra ID

Page Ajouter une attribution de rôle dans le portail Azure.

Notes

Seuls les membres dotés du privilège Propriétaire peuvent effectuer cette étape. Pour découvrir différents rôles intégrés Azure, consultez Rôles intégrés Azure.

Important

Spécifiez le rôle Azure Propriétaire, Contributeur ou Lecteur des données Blob de stockage. Ces rôles sont différents des rôles Azure intégrés Propriétaire, Collaborateur et Lecteur.

Octroi d’une autorisation Azure RBAC pour le chargement

Vous pouvez maintenant exécuter l’instruction COPY en spécifiant « Identité managée » :

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

D. Authentification Microsoft Entra

Étapes

Sous votre compte de stockage, sélectionnez Contrôle d’accès (IAM) .
Sélectionnez Ajouter>Ajouter une attribution de rôle pour ouvrir la page Ajouter une attribution de rôle.
Attribuez le rôle suivant. Pour connaître les étapes détaillées, consultez Attribuer des rôles Azure à l’aide du portail Azure.

Paramètre Valeur

Role Propriétaire, contributeur ou lecteur des données blob du stockage

Attribuer l’accès à Utilisateur

Membres Utilisateur Microsoft Entra

Important

Spécifiez le rôle Azure Propriétaire, Contributeur ou Lecteur des données Blob de stockage. Ces rôles sont différents des rôles Azure intégrés Propriétaire, Collaborateur et Lecteur.
Configurez l’authentification Microsoft Entra. Consultez Configurer et gérer l’authentification Microsoft Entra avec Azure SQL.
Connectez-vous à votre pool SQL à l’aide d’Active Directory. Vous pouvez maintenant exécuter l’instruction COPY sans spécifier d’informations d’identification :
```
COPY INTO dbo.target_table
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.txt'
WITH (
    FILE_TYPE = 'CSV'
)
```

Paramètre	Valeur
Role	Propriétaire, contributeur ou lecteur des données blob du stockage
Attribuer l’accès à	Utilisateur
Membres	Utilisateur Microsoft Entra

E. Authentification d’un principal de service

Étapes

Créez une application Microsoft Entra.
Obtenez l’ID de l’application.
Obtenez la clé d’authentification.
Obtenez le point de terminaison de jeton OAuth 2.0 V1.
Attribuez des autorisations de lecture, d’écriture et d’exécution à votre application Microsoft Entra sur votre compte de stockage.

Vous pouvez maintenant exécuter l’instruction 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')
)

Important

Utilisez la version V1 du point de terminaison de jeton OAuth 2.0

Étapes suivantes

Consulter l’article sur l’instruction COPY pour connaître la syntaxe détaillée
Consulter l’article sur la vue d’ensemble du chargement des données pour connaître les bonnes pratiques de chargement

Partager via

Charger des données de façon sécurisée à l’aide de SQL Synapse

Mécanismes d’authentification pris en charge

R. Clé de compte de stockage avec LF comme indicateur de fin de ligne (nouvelle ligne de style UNIX)

B. Signatures d’accès partagé (SAP) avec CRLF comme indicateur de fin de ligne (nouvelle ligne de style Windows)

C. Identité managée

Prérequis

Étapes

D. Authentification Microsoft Entra

Étapes

E. Authentification d’un principal de service

Étapes

Étapes suivantes

Commentaires

Ressources supplémentaires