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

  • Article

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 (blob1)/MSI (dfs2)/SERVICE PRINCIPAL/KEY/AAD SAS (blob1)/MSI (dfs2)/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

  1. Installez Azure PowerShell. Reportez-vous à Installer PowerShell.
  2. 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.
  3. 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

  1. 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.

  2. Créez un compte de stockage v2 universel. Pour plus d’informations, consultez la rubrique Création d’un compte de stockage .

    Notes

  3. Sous votre compte de stockage, sélectionnez Contrôle d’accès (IAM) .

  4. Sélectionnez Ajouter>Ajouter une attribution de rôle pour ouvrir la page Ajouter une attribution de rôle.

  5. 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

    Add role assignment page in Azure portal.

    Remarque

    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.

    Granting Azure RBAC permission to load

  6. 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

  1. Sous votre compte de stockage, sélectionnez Contrôle d’accès (IAM) .

  2. Sélectionnez Ajouter>Ajouter une attribution de rôle pour ouvrir la page Ajouter une attribution de rôle.

  3. 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

    Add role assignment page in Azure portal.

    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.

    Granting Azure RBAC permission to load

  4. Configurez l’authentification Microsoft Entra. Consultez Configurer et gérer l’authentification Microsoft Entra avec Azure SQL.

  5. 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'
)

E. Authentification d’un principal de service

Étapes

  1. Créez une application Microsoft Entra.

  2. Obtenez l’ID de l’application.

  3. Obtenez la clé d’authentification.

  4. Obtenez le point de terminaison de jeton OAuth 2.0 V1.

  5. Attribuez des autorisations de lecture, d’écriture et d’exécution à votre application Microsoft Entra sur votre compte de stockage.

  6. 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