Teilen über

Sicheres Laden von Daten mithilfe von Synapse SQL

  • Artikel

In diesem Artikel werden sichere Authentifizierungsmechanismen für die COPY-Anweisung erläutert und Beispiele dazu bereitgestellt. Die COPY-Anweisung ist die flexibelste und sicherste Methode zum Massenladen von Daten in Synapse SQL.

Unterstützte Authentifizierungsmechanismen

In der nachstehenden Matrix werden die unterstützten Authentifizierungsmethoden für jeden Dateityp und jedes Speicherkonto beschrieben. Dies gilt für den Quellspeicherort und den Speicherort der Fehlerdatei.

CSV Parquet ORC
Azure Blob Storage 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: Der .blob-Endpunkt (.blob.core.windows.net) unter Ihrem externen Speicherortpfad ist für diese Authentifizierungsmethode erforderlich.

2: Der .dfs-Endpunkt (.dfs.core.windows.net) unter Ihrem externen Speicherortpfad ist für diese Authentifizierungsmethode erforderlich.

A. Speicherkontoschlüssel mit LF als Zeilenabschlusszeichen (Neue-Zeile-Zeichen im Unix-Stil)

--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)
)

Wichtig

  • Verwenden Sie den Hexadezimalwert (0x0A) zur Angabe des Zeilenvorschub-/Neue-Zeile-Zeichens. Beachten Sie, dass die COPY-Anweisung die Zeichenfolge „\n“ als „\r\n“ (Wagenrücklaufzeichen, Zeilenumbruchzeichen) interpretiert.

B. Shared Access Signatures (SAS) mit CRLF als Zeilenabschlusszeichen (Neue-Zeile-Zeichen im Windows-Stil)

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

Wichtig

Geben Sie ROWTERMINATOR nicht als „\r\n“ an. Dies wird nämlich als „\r\r\n“ interpretiert und kann zu Analyseproblemen führen. Der COPY-Befehl stellt beim Angeben von „\n“ (Zeilenumbruch) automatisch das „\r“-Zeichen voran. Dies führt zu einem Wagenrücklauf-Zeilenumbruch (\r\n) auf Windows-basierten Systemen.

C. Verwaltete Identität

Eine Authentifizierung der verwalteten Identität ist erforderlich, wenn Ihr Speicherkonto an ein VNET angefügt ist.

Voraussetzungen

  1. Installieren Sie Azure PowerShell. Siehe Installieren von PowerShell.
  2. Falls Sie über ein universelles Speicherkonto V1 oder ein Blobspeicherkonto verfügen, müssen Sie zuerst ein Upgrade auf Version 2 des universellen Speicherkontos durchführen. Weitere Informationen finden Sie im Artikel zum Durchführen eines Upgrades auf ein Speicherkonto vom Typ „Universell V2“.
  3. Im Einstellungsmenü Firewalls und virtuelle Netzwerke des Azure Storage-Kontos muss die Option Vertrauenswürdigen Microsoft-Diensten den Zugriff auf dieses Speicherkonto erlauben aktiviert sein. Weitere Informationen finden Sie unter Konfigurieren von Azure Storage-Firewalls und virtuellen Netzwerken.

Schritte

  1. Wenn Sie über einen eigenständigen dedizierten SQL-Pool verfügen, registrieren Sie Ihren SQL-Server mithilfe von PowerShell bei Microsoft Entra ID:

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

    Dieser Schritt ist für dedizierte SQL-Pools in einem Synapse-Arbeitsbereich nicht erforderlich. Die vom System zugewiesene verwaltete Identität (SA-MI) des Arbeitsbereichs ist ein Mitglied der Synapse-Administratorrolle und verfügt daher über erhöhte Rechte für die dedizierten SQL-Pools des Arbeitsbereichs.

  2. Erstellen Sie ein universelles v2-Speicherkonto. Weitere Informationen finden Sie unter Erstellen eines Speicherkontos.

    Hinweis

  3. Wählen Sie unter Ihrem Speicherkonto die Option Zugriffssteuerung (IAM) aus.

  4. Wählen Sie Hinzufügen>Rollenzuweisung hinzufügen aus, um den Bereich „Rollenzuweisung hinzufügen“ zu öffnen.

  5. Weisen Sie die folgende Rolle zu. Ausführliche Informationen finden Sie unter Zuweisen von Azure-Rollen über das Azure-Portal.

    Einstellung Wert
    Role Mitwirkender an Storage-Blobdaten
    Zugriff zuweisen zu SERVICEPRINCIPAL
    Member Server oder Arbeitsbereich, auf bzw. in dem Ihr bei Microsoft Entra ID registrierter dedizierter SQL-Pool gehostet wird

    Seite „Rollenzuweisung hinzufügen“ im Azure-Portal

    Hinweis

    Nur Mitglieder mit der Berechtigung „Besitzer“ können diesen Schritt ausführen. Informationen zu verschiedenen integrierten Azure-Rollen finden Sie unter Integrierte Azure-Rollen.

    Wichtig

    Geben Sie die Azure-Rolle „Besitzer von“, „Mitwirkender an“ oder „Leser von“ Speicherblobdaten an. Diese Rollen sind anders als die integrierten Azure-Rollen „Besitzer“, „Mitwirkender“ und „Leser“.

    Erteilen der Azure RBAC-Berechtigung zum Laden

  6. Jetzt können Sie die COPY-Anweisung zur Angabe von „verwaltete Identität“ ausführen:

    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-Authentifizierung

Schritte

  1. Wählen Sie unter Ihrem Speicherkonto die Option Zugriffssteuerung (IAM) aus.

  2. Wählen Sie Hinzufügen>Rollenzuweisung hinzufügen aus, um den Bereich „Rollenzuweisung hinzufügen“ zu öffnen.

  3. Weisen Sie die folgende Rolle zu. Ausführliche Informationen finden Sie unter Zuweisen von Azure-Rollen über das Azure-Portal.

    Einstellung Wert
    Role „Besitzer von Speicherblobdaten“, „Mitwirkender“ oder „Leser“
    Zugriff zuweisen zu USER
    Member Microsoft Entra-Benutzer*in

    Seite „Rollenzuweisung hinzufügen“ im Azure-Portal

    Wichtig

    Geben Sie die Azure-Rolle „Besitzer von“, „Mitwirkender an“ oder „Leser von“ Speicherblobdaten an. Diese Rollen sind anders als die integrierten Azure-Rollen „Besitzer“, „Mitwirkender“ und „Leser“.

    Erteilen der Azure RBAC-Berechtigung zum Laden

  4. Konfigurieren Sie die Microsoft Entra-Authentifizierung. Weitere Informationen finden Sie unter Konfigurieren und Verwalten der Microsoft Entra-Authentifizierung mit Azure SQL.

  5. Stellen Sie mithilfe von Active Directory eine Verbindung zu Ihrem SQL-Pool her. Dort können Sie jetzt die COPY-Anweisung ohne Angabe von Anmeldeinformationen ausführen:

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

E. Dienstprinzipalauthentifizierung

Schritte

  1. Erstellen einer Microsoft Entra-Anwendung

  2. Abrufen der Anwendungs-ID.

  3. Abrufen des Authentifizierungsschlüssels.

  4. Abrufen des V1 OAuth 2.0-Token-Endpunkts.

  5. Zuweisen von Lese-, Schreib- und Ausführungsberechtigungen zu Ihrer Microsoft Entra-Anwendung in Ihrem Speicherkonto

  6. Jetzt können Sie die COPY-Anweisung ausführen:

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

Wichtig

Verwenden Sie dazu die Version V1 des OAuth 2.0-Token-Endpunkts.

Nächste Schritte