Controllare l'accesso agli account di archiviazione per il pool SQL serverless in Azure Synapse Analytics

  • Articolo

Una query del pool SQL serverless legge i file direttamente da Archiviazione di Azure. Le autorizzazioni per accedere ai file in Archiviazione di Azure sono controllate a due livelli:

  • Livello di archiviazione: l'utente deve disporre dell'autorizzazione per accedere ai file di archiviazione sottostanti. L'amministratore della risorsa di archiviazione deve consentire all'entità di sicurezza di Microsoft Entra di leggere/scrivere file o generare una chiave di firma di accesso condiviso che verrà usata per accedere alla risorsa di archiviazione.
  • Livello di servizio SQL: l'utente deve avere l'autorizzazione per leggere i dati con una tabella esterna o per eseguire la funzione OPENROWSET. Vedere altre informazioni sulle autorizzazioni necessarie in questa sezione.

Questo articolo descrive i tipi di credenziali che è possibile usare e il modo in cui viene eseguita la ricerca di credenziali per gli utenti di SQL e Microsoft Entra.

Autorizzazioni di archiviazione

Un pool SQL serverless nell'area di lavoro di Synapse Analytics può leggere il contenuto dei file archiviati in Azure Data Lake Storage. È necessario configurare le autorizzazioni per la risorsa di archiviazione per consentire a un utente che esegue una query SQL di leggere i file. Esistono tre metodi per abilitare l'accesso ai file:

  • Il controllo degli accessi in base al ruolo consente di assegnare un ruolo a un utente di Microsoft Entra nel tenant in cui risiede la risorsa di archiviazione. Un lettore deve essere membro del ruolo Lettore dei dati dei BLOB di archiviazione, Collaboratore ai dati dei BLOB di archiviazione o Proprietario dei dati dei BLOB di archiviazione nell'account di archiviazione. Un utente che scrive i dati nella risorsa di archiviazione di Azure deve essere membro del ruolo Collaboratore ai dati dei BLOB di archiviazione o Proprietario dei dati dei BLOB di archiviazione. Il ruolo Proprietario della risorsa di archiviazione non implica che un utente sia anche Proprietario dei dati di archiviazione.
  • Gli elenchi di controllo di accesso (ACL) consentono di definire autorizzazioni Read(R), Write(W) ed Execute(X) con granularità fine per i file e le directory nelle risorse di archiviazione di Azure. L'ACL può essere assegnato agli utenti di Microsoft Entra. Se i lettori vogliono leggere un file in un percorso in Archiviazione di Azure, devono disporre dell'ACL Execute(X) in ogni cartella nel percorso del file e dell'ACL Read(R) nel file. Altre informazioni su come impostare le autorizzazioni ACL nel livello di archiviazione.
  • La firma di accesso condiviso consente a un lettore di accedere ai file in Azure Data Lake Storage usando il token con limite di tempo. Il lettore non deve nemmeno essere autenticato come utente di Microsoft Entra. Il token di firma di accesso condiviso contiene le autorizzazioni concesse al lettore e il periodo di validità del token. Il token di firma di accesso condiviso è una scelta ottimale per l'accesso con vincoli di tempo per qualsiasi utente che non deve necessariamente risiedere nello stesso tenant Microsoft Entra. Il token di firma di accesso condiviso può essere definito nell'account di archiviazione o in directory specifiche. Altre informazioni sulla concessione di accesso limitato alle risorse di Archiviazione di Azure tramite le firme di accesso condiviso.

In alternativa, è possibile rendere i file disponibili pubblicamente consentendo l'accesso anonimo. Questo approccio NON deve essere usato se si dispone di dati non pubblici.

Tipi di autorizzazione supportati per l'archiviazione

Un utente connesso a un pool SQL serverless deve essere autorizzato ad accedere e a eseguire query sui file di Archiviazione di Azure se non sono disponibili pubblicamente. È possibile usare quattro tipi di autorizzazione per accedere alle risorse di archiviazione non pubbliche: Identità utente, Firma di accesso condiviso, Entità servizio e Identità gestita.

Nota

Pass-through di Microsoft Entra è il comportamento predefinito quando si crea un'area di lavoro.

Identità utente: nota anche come "pass-through di Microsoft Entra", è un tipo di autorizzazione in cui per concedere l'accesso ai dati viene usata l'identità dell'utente di Microsoft Entra che ha eseguito l'accesso al pool SQL serverless. Prima di accedere ai dati, l'amministratore di Archiviazione di Azure deve concedere le apposite autorizzazioni all'utente di Microsoft Entra. Come indicato nella tabella Tipi di autorizzazione supportati per gli utenti di database, non è supportata per il tipo di utente SQL.

Importante

Un token di autenticazione di Microsoft Entra potrebbe essere memorizzato nella cache dalle applicazioni client. Ad esempio, Power BI memorizza nella cache il token di Microsoft Entra e lo riutilizza per un'ora. Le query a esecuzione prolungata potrebbero avere esito negativo se il token scade nel corso dell'esecuzione della query. Se si verificano errori di query causati dal token di accesso Microsoft Entra che scade nel corso dell'esecuzione della query, provare a passare a un'entità servizio, un'identità gestita o una firma di accesso condiviso.

L'utente deve essere membro del ruolo Proprietario dei dati dei BLOB di archiviazione, Collaboratore ai dati dei BLOB di archiviazione o Lettore dei dati dei BLOB di archiviazione per usare l'identità per accedere ai dati. In alternativa, è possibile specificare regole di controllo di accesso (ACL) con granularità fine per accedere a file e cartelle. Anche se si è proprietari di un account di archiviazione, è comunque necessario aggiungere se stessi in uno dei ruoli dei dati dei BLOB di archiviazione. Per altre informazioni sul controllo di accesso in Azure Data Lake Store Gen2, vedere l'articolo Controllo di accesso in Azure Data Lake Storage Gen2.

Scenari tra tenant

Nei casi in cui Archiviazione di Azure risiede in un tenant diverso dal pool SQL serverless di Synapse, l'autorizzazione tramite l'entità servizio è il metodo consigliato. È anche possibile usare l'autorizzazione tramite firma di accesso condiviso, mentre l'identità gestita non è supportata.

Tipo di autorizzazione Archiviazione protetta da firewall Archiviazione non protetta da firewall
Firma di accesso condiviso Supportata Supportata
Entità servizio Non supportato Supportato

Nota

Se l'account di archiviazione di Azure è protetto da un firewall di Archiviazione di Azure, l'entità servizio non sarà supportata.

Tipi di autorizzazione supportati per gli utenti di database

La tabella seguente fornisce i tipi di autorizzazione di Archiviazione di Azure disponibili per metodi di accesso diversi in un endpoint SQL serverless di Azure Synapse Analytics:

Tipo di autorizzazione Utente SQL Utente di Microsoft Entra Entità servizio
Identità utente Non supportato Supportata Supportata
Firma di accesso condiviso Supportata Supportato Supportata
Entità servizio Supportata Supportato Supportata
Identità gestita Supportata Supportato Supportata

Tipi di autorizzazione e archiviazione supportati

È possibile usare le combinazioni seguenti di tipi di autorizzazione e tipi di archiviazione di Azure:

Tipo di autorizzazione Archiviazione BLOB ADLS Gen1 ADLS Gen2
Firma di accesso condiviso Supportata Non supportato Supportato
Entità servizio Supportata Supportato Supportata
Identità gestita Supportata Supportato Supportata
Identità utente Supportata Supportato Supportata

Scenari tra tenant

Nei casi in cui Archiviazione di Azure risiede in un tenant diverso dal pool SQL serverless di Azure Synapse Analytics, l'autorizzazione tramite l'entità servizio è il metodo consigliato. È anche possibile usare l'autorizzazione con firma di accesso condiviso. L'identità del servizio gestita non è supportata.

Tipo di autorizzazione Archiviazione protetta da firewall Archiviazione non protetta da firewall
Firma di accesso condiviso Supportata Supportata
Entità servizio Non supportato Supportato

Nota

Se l'account di archiviazione di Azure è protetto da un firewall di Archiviazione di Azure e risiede in un altro tenant, l'entità servizio non sarà supportata. Provare invece a usare un token di firma di accesso condiviso.

Archiviazione protetta da firewall

È possibile configurare gli account di archiviazione per consentire l'accesso a un pool SQL serverless specifico creando una regola di istanza della risorsa. Per accedere a un account di archiviazione protetto da firewall, usare l'identità utente o l'identità gestita.

Nota

La funzionalità firewall in Archiviazione di Azure è disponibile in anteprima pubblica ed è disponibile in tutte le aree del cloud pubblico.

La tabella seguente fornisce i tipi di autorizzazione di Archiviazione di Azure protetta da firewall disponibili per metodi di accesso diversi in un endpoint SQL serverless di Azure Synapse Analytics:

Tipo di autorizzazione Utente SQL Utente di Microsoft Entra Entità servizio
Identità utente Non supportato Supportata Supportata
Firma di accesso condiviso Non supportato Non supportato Non supportato
Entità servizio Non supportato Non supportato Non supportato
Identità gestita Supportata Supportato Supportata

Per accedere all'account di archiviazione protetto da firewall tramite l'identità utente, è possibile usare il portale di Azure o il modulo Az.Storage di PowerShell.

Configurazione del firewall di Archiviazione di Azure tramite il portale di Azure

  1. Cercare l'account di archiviazione nel portale di Azure.
  2. Nel menu di spostamento principale passare a Rete in Impostazioni.
  3. Nella sezione Istanze della risorsa aggiungere un'eccezione per l'area di lavoro di Azure Synapse.
  4. Selezionare Microsoft.Synapse/workspaces come Tipo di risorsa.
  5. Selezionare il nome dell'area di lavoro come Nome istanza.
  6. Seleziona Salva.

Configurazione del firewall di Archiviazione di Azure tramite PowerShell

Seguire questa procedura per configurare l'account di archiviazione e aggiungere un'eccezione per l'area di lavoro di Azure Synapse.

  1. Aprire o installare PowerShell.

  2. Installare le versioni più recenti del modulo Az.Storage e del modulo Az.Synapse, ad esempio nello script seguente:

    Install-Module -Name Az.Storage -RequiredVersion 3.4.0
Install-Module -Name Az.Synapse -RequiredVersion 0.7.0

    Importante

    Assicurarsi di usare almeno la versione 3.4.0. È possibile verificare la versione di Az.Storage eseguendo questo comando:

    Get-Module -ListAvailable -Name Az.Storage | Select Version

  3. Connettersi al tenant di Azure:

    Connect-AzAccount

  4. Definire le variabili in PowerShell:

    • Nome del gruppo di risorse: è possibile trovarlo nel portale di Azure nella Panoramica dell'account di archiviazione.
    • Nome account: nome dell'account di archiviazione protetto dalle regole del firewall.
    • ID tenant: è possibile trovarlo nel portale di Azure in Microsoft Entra ID, in Proprietà, Proprietà tenant.
    • Nome area di lavoro: nome dell'area di lavoro di Azure Synapse.
        $resourceGroupName = "<resource group name>"
    $accountName = "<storage account name>"
    $tenantId = "<tenant id>"
    $workspaceName = "<Azure Synapse workspace name>"

    $workspace = Get-AzSynapseWorkspace -Name $workspaceName
    $resourceId = $workspace.Id
    $index = $resourceId.IndexOf("/resourceGroups/", 0)
    # Replace G with g - /resourceGroups/ to /resourcegroups/
    $resourceId = $resourceId.Substring(0,$index) + "/resourcegroups/" ` 
        + $resourceId.Substring($index + "/resourceGroups/".Length)

    $resourceId

    Importante

    Il valore di $resourceid restituito dallo script di PowerShell deve corrispondere a questo modello: /subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace} È importante scrivere resourcegroups in lettere minuscole.

  5. Aggiungere una regola di rete dell'account di archiviazione di Azure:

        $parameters = @{
        ResourceGroupName = $resourceGroupName
        Name = $accountName
        TenantId = $tenantId 
        ResourceId = $resourceId
    }

    Add-AzStorageAccountNetworkRule @parameters

  6. Verificare che la regola di rete dell'account di archiviazione sia stata applicata nel firewall dell'account di archiviazione. Lo script di PowerShell seguente confronta la variabile $resourceid dei passaggi precedenti con l'output della regola di rete dell'account di archiviazione.

        $parameters = @{
        ResourceGroupName = $resourceGroupName
        Name = $accountName
    }

    $rule = Get-AzStorageAccountNetworkRuleSet @parameters
    $rule.ResourceAccessRules | ForEach-Object { 
        if ($_.ResourceId -cmatch "\/subscriptions\/(\w\-*)+\/resourcegroups\/(.)+") { 
            Write-Host "Storage account network rule is successfully configured." -ForegroundColor Green
            $rule.ResourceAccessRules
        } else {
            Write-Host "Storage account network rule is not configured correctly. Remove this rule and follow the steps in detail." -ForegroundColor Red
            $rule.ResourceAccessRules
        }
    }

Titolo

Per eseguire query su un file in Archiviazione di Azure, l'endpoint del pool SQL serverless deve disporre di credenziali contenenti le informazioni di autenticazione. Vengono usati due tipi di credenziali:

  • Le credenziali a livello di server vengono usate per le query ad hoc eseguite con la funzione OPENROWSET. Il nome delle credenziali deve corrispondere all'URL di archiviazione.
  • Le credenziali con ambito database vengono usate per le tabelle esterne. La tabella esterna fa riferimento a DATA SOURCE con le credenziali che devono essere usate per accedere all'archiviazione.

Concedere le autorizzazioni per gestire le credenziali

Per concedere la possibilità di gestire le credenziali:

  • Per consentire a un utente di creare o eliminare credenziali a livello di server, un amministratore deve concedere l'autorizzazione ALTER ANY CREDENTIAL al relativo account di accesso nel database master. Ad esempio:

    GRANT ALTER ANY CREDENTIAL TO [login_name];

  • Per consentire a un utente di creare o eliminare credenziali con ambito database, un amministratore deve concedere l'autorizzazione CONTROL sul database all'utente di database nel database utente. Ad esempio:

    GRANT CONTROL ON DATABASE::[database_name] TO [user_name];

Concedere le autorizzazioni per l'uso delle credenziali

Gli utenti del database che accedono all'archiviazione esterna devono disporre delle autorizzazioni per usare le credenziali. Per usare le credenziali, l'utente deve disporre dell'autorizzazione REFERENCES per una credenziale specifica.

Per concedere l'autorizzazione REFERENCES per credenziali a livello di server per un account di accesso, usare la query T-SQL seguente nel database master:

GRANT REFERENCES ON CREDENTIAL::[server-level_credential] TO [login_name];

Per concedere l'autorizzazione REFERENCES per credenziali con ambito database per un utente di database, usare la query T-SQL seguente nel database utente:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[database-scoped_credential] TO [user_name];

Credenziali a livello di server

Le credenziali con ambito server vengono usate quando un account di accesso SQL chiama la funzione OPENROWSET senza DATA_SOURCE per leggere i file in un account di archiviazione.

Il nome delle credenziali a livello di server deve corrispondere all'URL di base dell'archiviazione di Azure, seguito facoltativamente da un nome di contenitore. Per aggiungere una credenziale, eseguire CREATE CREDENTIAL. È necessario specificare l'argomento CREDENTIAL NAME.

Nota

L'argomento FOR CRYPTOGRAPHIC PROVIDER non è supportato.

Il nome delle credenziali a livello di server deve seguire il formato seguente: <prefix>://<storage_account_path>[/<container_name>]. I percorsi degli account di archiviazione sono descritti nella tabella seguente:

Origine dati esterna Prefisso Percorso dell'account di archiviazione
Archiviazione BLOB di Azure https <storage_account>.blob.core.windows.net
Azure Data Lake Storage Gen1 https <storage_account>.azuredatalakestore.net/webhdfs/v1
Azure Data Lake Storage Gen2 https <storage_account>.dfs.core.windows.net

Le credenziali a livello di server possono quindi accedere ad Archiviazione di Azure usando i tipi di autenticazione seguenti:

Gli utenti di Microsoft Entra possono accedere a qualsiasi file nell'archiviazione di Azure se sono membri del ruolo Proprietario dei dati dei BLOB di archiviazione, Collaboratore ai dati dei BLOB di archiviazione o Lettore dei dati dei BLOB di archiviazione. Gli utenti di Microsoft Entra non necessitano di credenziali per accedere alle risorse di archiviazione.

Gli utenti che hanno eseguito l'autenticazione di SQL non possono usare l'autenticazione di Microsoft Entra per accedere all'archiviazione. Possono accedere all'archiviazione tramite credenziali del database usando un'identità gestita, una chiave di firma di accesso condiviso, un'entità servizio o se è disponibile l'accesso pubblico alla risorsa di archiviazione.

Credenziali con ambito database

Le credenziali con ambito database vengono usate quando un'entità chiama la funzione OPENROWSET con DATA_SOURCE o seleziona i dati da una tabella esterna che non ha accesso ai file pubblici. Le credenziali con ambito database non devono corrispondere al nome dell'account di archiviazione perché vi è fatto riferimento nell'origine dati che definisce il percorso di archiviazione.

Le credenziali con ambito database consentono l'accesso ad Archiviazione di Azure usando i tipi di autenticazione seguenti:

Gli utenti di Microsoft Entra possono accedere a qualsiasi file nell'archiviazione di Azure se sono membri dei ruoli Proprietario dei dati dei BLOB di archiviazione, Collaboratore ai dati dei BLOB di archiviazione o Lettore dei dati dei BLOB di archiviazione. Gli utenti di Microsoft Entra non necessitano di credenziali per accedere alle risorse di archiviazione.

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
)

Gli utenti che hanno eseguito l'autenticazione di SQL non possono usare l'autenticazione di Microsoft Entra per accedere all'archiviazione. Possono accedere all'archiviazione tramite credenziali del database usando un'identità gestita, una chiave di firma di accesso condiviso, un'entità servizio o se è disponibile l'accesso pubblico alla risorsa di archiviazione.

Le credenziali con ambito database vengono usate nelle origini dati esterne per specificare il metodo di autenticazione che verrà usato per accedere a questa risorsa di archiviazione:

CREATE EXTERNAL DATA SOURCE mysample
WITH (    LOCATION   = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>',
          CREDENTIAL = <name of database scoped credential> 
)

Esempi

Accedere a un'origine dati disponibile pubblicamente

Usare lo script seguente per creare una tabella che accede all'origine dati disponibile pubblicamente.

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat]
       WITH ( FORMAT_TYPE = PARQUET)
GO
CREATE EXTERNAL DATA SOURCE publicData
WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<public_container>/<path>' )
GO

CREATE EXTERNAL TABLE dbo.userPublicData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [publicData],
       FILE_FORMAT = [SynapseParquetFormat] )

L'utente del database può leggere il contenuto dei file dall'origine dati usando una tabella esterna o la funzione OPENROWSET che fa riferimento all'origine dati:

SELECT TOP 10 * FROM dbo.userPublicData;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet',
                                DATA_SOURCE = 'mysample',
                                FORMAT='PARQUET') as rows;
GO

Accedere a un'origine dati tramite credenziali

Modificare lo script seguente per creare una tabella esterna che accede ad Archiviazione di Azure tramite il token di firma di accesso condiviso, l'identità di Microsoft Entra dell'utente o l'identità gestita dell'area di lavoro.

-- Create master key in databases with some password (one-off per database)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>'
GO

-- Create databases scoped credential that use Managed Identity, SAS token or service principal. User needs to create only database-scoped credentials that should be used to access data source:

CREATE DATABASE SCOPED CREDENTIAL WorkspaceIdentity
WITH IDENTITY = 'Managed Identity'
GO
CREATE DATABASE SCOPED CREDENTIAL SasCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-1********ZVsTOL0ltEGhf54N8KhDCRfLRI%3D'
GO
CREATE DATABASE SCOPED CREDENTIAL SPNCredential WITH
IDENTITY = '**44e*****8f6-ag44-1890-34u4-22r23r771098@https://login.microsoftonline.com/**do99dd-87f3-33da-33gf-3d3rh133ee33/oauth2/token' 
, SECRET = '.7OaaU_454azar9WWzLL.Ea9ePPZWzQee~'
GO
-- Create data source that one of the credentials above, external file format, and external tables that reference this data source and file format:

CREATE EXTERNAL FILE FORMAT [SynapseParquetFormat] WITH ( FORMAT_TYPE = PARQUET)
GO

CREATE EXTERNAL DATA SOURCE mysample
WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>'
-- Uncomment one of these options depending on authentication method that you want to use to access data source:
--,CREDENTIAL = WorkspaceIdentity 
--,CREDENTIAL = SasCredential 
--,CREDENTIAL = SPNCredential
)

CREATE EXTERNAL TABLE dbo.userData ( [id] int, [first_name] varchar(8000), [last_name] varchar(8000) )
WITH ( LOCATION = 'parquet/user-data/*.parquet',
       DATA_SOURCE = [mysample],
       FILE_FORMAT = [SynapseParquetFormat] );

L'utente del database può leggere il contenuto dei file dall'origine dati usando una tabella esterna o la funzione OPENROWSET che fa riferimento all'origine dati:

SELECT TOP 10 * FROM dbo.userdata;
GO
SELECT TOP 10 * FROM OPENROWSET(BULK 'parquet/user-data/*.parquet', DATA_SOURCE = 'mysample', FORMAT='PARQUET') as rows;
GO

Passaggi successivi

Questi articoli illustrano come eseguire query su diversi tipi di file e cartelle e su come creare e usare le viste: