Controlar o acesso à conta de armazenamento para o pool de SQL sem servidor no Azure Synapse Analytics

  • Artigo

Uma consulta do pool de SQL sem servidor lê arquivos diretamente no Armazenamento do Azure. As permissões para acessar os arquivos no armazenamento do Azure são controladas em dois níveis:

  • Nível de armazenamento – o usuário deve ter permissão para acessar os arquivos de armazenamento subjacentes. Seu administrador de armazenamento deve permitir que a entidade principal do Microsoft Entra leia/grave arquivos ou gere uma chave assinatura de acesso compartilhado (SAS) que será usada para acessar o armazenamento.
  • Nível de serviço do SQL – o usuário deve ter concedido uma permissão para ler dados usando a tabela externa ou para executar a função OPENROWSET. Leia mais sobre as permissões obrigatórias nesta seção.

Este artigo descreve os tipos de credenciais que você pode usar e como a pesquisa de credenciais é aplicada nos usuários do SQL e do Microsoft Entra.

Permissões de armazenamento

Um pool de SQL sem servidor no workspace do Synapse Analytics pode ler o conteúdo dos arquivos armazenados no armazenamento Azure Data Lake. Você precisa configurar as permissões de armazenamento para habilitar um usuário a executar uma consulta SQL para ler os arquivos. Há três métodos para habilitar o acesso aos arquivos:

  • O Controle de acesso baseado em função (RBAC) permite que você atribua uma função a algum usuário do Microsoft Entra no locatário em que o armazenamento é colocado. Um leitor deve ser membro da função Leitor de Dados do Blob de Armazenamento, Colaborador de Dados de Blob de Armazenamento ou Proprietário de Dados de Blob de Armazenamento na conta de armazenamento. Um usuário que grava dados no armazenamento do Azure deve ser membro da função Colaborador de Dados de Blob de Armazenamento ou Proprietário de Dados de Blob de Armazenamento. A função de proprietário do armazenamento não implica que um usuário também seja proprietário dos dados do armazenamento.
  • As ACL (Listas de Controle de Acesso) permitem que você defina permissões refinadas Ler(R), Gravar(W) e Executar(X) nos arquivos e nos diretórios do armazenamento do Azure. A ACL pode ser atribuída a usuários do Microsoft Entra. Se os leitores quiserem ler um arquivo em um caminho no Armazenamento do Azure, eles precisarão ter a ACL Executar(X) em cada pasta no caminho do arquivo e a ACL Ler(R) no arquivo. Saiba mais sobre como definir permissões de ACL na camada de armazenamento.
  • A SAS (assinatura de acesso compartilhado) habilita um leitor a acessar os arquivos no armazenamento do Azure Data Lake usando o token limitado por tempo. O leitor nem precisa ser autenticado como um usuário do Microsoft Entra. O token SAS contém as permissões concedidas ao leitor e o período em que o token é válido. O token SAS é uma boa opção para acesso com restrição de tempo a qualquer usuário que nem precisa estar no mesmo locatário do Microsoft Entra. O token SAS pode ser definido na conta de armazenamento ou em diretórios específicos. Saiba mais sobre como permitir acesso limitado aos recursos de Armazenamento do Azure usando assinaturas de acesso compartilhado.

Como alternativa, você pode disponibilizar seus arquivos publicamente, permitindo o acesso anônimo. Essa abordagem NÃO deve ser usada se você tem dados não públicos.

Tipos de autorização de armazenamento compatíveis

Um usuário que fez logon em um pool de SQL sem servidor precisará estar autorizado a acessar e consultar os arquivos no Armazenamento do Azure se os arquivos não estiverem disponíveis publicamente. Você pode usar quatro tipos de autorização para acessar um armazenamento não público: identidade do usuário, assinatura de acesso compartilhado, entidade de serviço e identidade gerenciada.

Observação

O Passagem do Microsoft Entra é o comportamento padrão quando você cria um espaço de trabalho.

A Identidade do Usuário, também conhecida como "passagem do Microsoft Entra", é um tipo de autorização em que a identidade do usuário do Microsoft Entra que fez logon no pool de SQL sem servidor é usada para autorizar o acesso aos dados. Antes de acessar os dados, o administrador do Armazenamento do Azure deve conceder permissões ao usuário do Microsoft Entra. Conforme indicado na tabela Tipos de autorização com suporte para usuários de banco de dados, não há suporte para o tipo de usuário SQL.

Importante

Um token de autenticação do Microsoft Entra pode ser armazenado em cache pelos aplicativos cliente. Por exemplo, o Power BI armazena tokens do Microsoft Entra em cache e reusa o mesmo token por uma hora. As consultas de execução prolongada poderão falhar se o token expirar no meio da execução da consulta. Se você estiver enfrentando falhas de consulta causadas pelo token de acesso do Microsoft Entra que expira no meio da consulta, considere alternar para uma entidade de serviço, identidade gerenciada ou assinatura de acesso compartilhado.

Você precisa ser membro da função Proprietário de Dados do Blob de Armazenamento, Colaborador de Dados de Blob de Armazenamento ou Leitor de Dados de Blob de Armazenamento para usar sua identidade para acessar os dados. Como alternativa, você pode especificar regras de ACL refinadas para acessar arquivos e pastas. Mesmo se você for um Proprietário de uma Conta de Armazenamento, ainda precisará se adicionar a uma das funções de dados do blob de armazenamento. Para saber mais sobre o controle de acesso no Azure Data Lake Storage Gen2, examine o artigo Controle de acesso no Azure Data Lake Storage Gen2.

Cenários entre locatários

Nos casos em que o Azure Armazenamento está em um locatário diferente do pool de SQL sem servidor do Azure Synapse, a autorização por meio da Entidade de Serviço é o método recomendado. A autorização SAS também é possível, enquanto a Identidade Gerenciada não é compatível.

Tipo de autorização Armazenamento protegido por firewall armazenamento protegido não por firewall
SAS Com suporte Com suporte
Entidade de Serviço Sem suporte Com suporte

Observação

Caso o Armazenamento do Microsoft Azure esteja protegido com um Firewall do Armazenamento do Microsoft Azure, não haverá suporte para a entidade de serviço.

Tipos de autorização compatíveis com usuários de bancos de dados

A tabela a seguir fornece tipos de autorização do Armazenamento do Microsoft Azure disponíveis para diferentes métodos de entrada em um ponto de extremidade SQL sem servidor do Azure Synapse Analytics:

Tipo de autorização Usuário do SQL Usuário do Microsoft Entra Entidade de serviço
Identidade do Usuário Sem suporte Com suporte Com suporte
SAS Com suporte Com suporte Com suporte
Entidade de serviço Com suporte Com suporte Com suporte
Identidade gerenciada Com suporte Com suporte Com suporte

Tipos de autorização e armazenamentos compatíveis

Você pode usar as seguintes combinações de tipos de autorização e tipos de Armazenamento do Microsoft Azure:

Tipo de autorização Armazenamento de Blobs ADLS Gen1 ADLS Gen2
SAS Com suporte Sem suporte Com suporte
Entidade de serviço Com suporte Com suporte Com suporte
Identidade gerenciada Com suporte Com suporte Com suporte
Identidade do Usuário Com suporte Com suporte Com suporte

Cenários entre locatários

Nos casos em que o Armazenamento do Microsoft Azure está em um locatário diferente do pool de SQL sem servidor do Azure Synapse Analytics, a autorização por meio da entidade de serviço é o método recomendado. A autorização de assinatura de acesso compartilhado também é possível. Não há suporte para a identidade de serviço gerenciado.

Tipo de autorização Armazenamento protegido por firewall armazenamento protegido não por firewall
SAS Com suporte Com suporte
Entidade de serviço Sem suporte Com suporte

Observação

Se o Armazenamento do Microsoft Azure estiver protegido por um firewall do Armazenamento do Microsoft Azure e estiver em outro locatário, não haverá suporte para a entidade de serviço. Em vez disso, use uma assinatura de acesso compartilhado (SAS).

Armazenamento protegido por firewall

Para permitir que as contas de armazenamento tenham acesso a um pool de SQL sem servidor específico, crie uma regra de instância de recurso. Ao acessar armazenamentos protegidos pelo firewall, é possível usar a Identidade do usuário ou a Identidade gerenciada.

Observação

O recurso de firewall no Armazenamento do Microsoft Azure está em versão prévia pública e disponível em todas as regiões de nuvem pública.

A tabela a seguir fornece tipos de autorização do Armazenamento do Microsoft Azure protegidos por firewall disponíveis para diferentes métodos de entrada em um ponto de extremidade de SQL sem servidor do Azure Synapse Analytics:

Tipo de autorização Usuário do SQL Usuário do Microsoft Entra Entidade de serviço
Identidade do Usuário Sem suporte Com suporte Com suporte
SAS Sem suporte Sem suporte Sem suporte
Entidade de serviço Sem suporte Sem suporte Sem suporte
Identidade gerenciada Com suporte Com suporte Com suporte

Para acessar armazenamento protegido com o firewall por meio de uma identidade de usuário, você pode usar o portal do Azure ou o módulo do PowerShell Az.Storage.

Configuração do firewall do Armazenamento do Microsoft Azure por meio de portal do Azure

  1. Pesquise pela sua conta de armazenamento no portal do Azure.
  2. No menu de navegação principal, vá para Rede em Configurações.
  3. Na seção Instâncias de recurso, adicione uma exceção para o workspace do Azure Synapse.
  4. Selecione Microsoft.Synapse/workspaces como um Tipo de recurso.
  5. Selecione o nome do seu workspace como um Nome da instância.
  6. Selecione Salvar.

Configuração do firewall do Armazenamento do Microsoft Azure por meio do PowerShell

Siga estas etapas para configurar a conta de armazenamento e adicionar uma exceção para o workspace do Azure Synapse.

  1. Abra o PowerShell ou instale o PowerShell.

  2. Instale as versões mais recentes do módulo Az.Storage e do módulo Az.Synapse, por exemplo no seguinte script:

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

    Importante

    Certifique-se de usar no minimo a versão 3.4.0. Você pode verificar sua versão do Az.Storage executando este comando:

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

  3. Conecte-se ao seu locatário do Azure:

    Connect-AzAccount

  4. Defina as variáveis no PowerShell:

    • Nome do grupo de recursos – você pode encontrá-lo no portal do Azure, na Visão geral da conta de armazenamento.
    • Nome da conta – nome da conta de armazenamento que é protegida por regras de firewall.
    • ID do locatário – você pode encontrá-la no Portal do Azure na ID do Microsoft Entra, em Propriedades, em Propriedades de locatário.
    • Nome do workspace – o nome do workspace do 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

    O valor do $resourceid retornado pelo script do PowerShell deve corresponder a este modelo: /subscriptions/{subscription-id}/resourcegroups/{resource-group}/providers/Microsoft.Synapse/workspaces/{name-of-workspace} É importante escrever resourcegroups em letras minúsculas.

  5. Adicione uma regra de rede da conta de armazenamento do Azure:

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

    Add-AzStorageAccountNetworkRule @parameters

  6. Verifique se a regra de rede da conta de armazenamento foi aplicada no firewall da conta de armazenamento. O script do PowerShell a seguir compara a variável $resourceid das etapas anteriores com a saída da regra de rede da conta de armazenamento.

        $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
        }
    }

Credenciais

Para consultar um arquivo localizado no Armazenamento do Microsoft Azure, o seu ponto de extremidade do pool de SQL sem servidor precisa de uma credencial que contenha as informações de autenticação. São usados dois tipos de credenciais:

  • A credencial no nível do servidor é usada para consultas ad hoc executadas usando a função OPENROWSET. O nome da credencial deve corresponder à URL de armazenamento.
  • Uma credencial no escopo do banco de dados é usada para tabelas externas. A tabela externa faz referência a DATA SOURCE com a credencial que deve ser usada para acessar o armazenamento.

Conceder permissões para gerenciar credenciais

Para conceder a capacidade de gerenciar credenciais:

  • Para permitir que um usuário crie ou elimine uma credencial no nível do servidor, o administrador deve conceder a permissão ALTER ANY CREDENTIAL ao seu logon no banco de dados mestre. Por exemplo:

    GRANT ALTER ANY CREDENTIAL TO [login_name];

  • Para permitir que um usuário crie ou elimine uma credencial com escopo de banco de dados, um administrador deve conceder a permissão CONTROL no banco de dados ao usuário do banco de dados no banco de dados do usuário. Por exemplo:

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

Conceder permissões para usar credencial

Os usuários de banco de dados que acessam o armazenamento externo devem ter permissão para usar credenciais. Para usar a credencial, um usuário deve ter a permissão REFERENCES em uma credencial específica.

Para conceder a permissão REFERENCES em uma credencial no nível do servidor para um logon, utilize a seguinte consulta T-SQL no banco de dados mestre:

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

Para conceder uma permissão REFERENCES em uma credencial com escopo de banco de dados para um usuário de banco de dados, utilize a seguinte consulta T-SQL no banco de dados do usuário:

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

Credencial no nível do servidor

As credenciais no nível do servidor são usadas quando o logon do SQL chama a função OPENROWSET sem DATA_SOURCE para ler arquivos em uma conta de armazenamento.

O nome da credencial no nível do servidor precisa corresponder à URL de base do armazenamento do Azure, opcionalmente seguido do nome do contêiner. Uma credencial é adicionada executando CREATE CREDENTIAL. Você precisa fornecer o argumento CREDENTIAL NAME.

Observação

Não há suporte para o argumento FOR CRYPTOGRAPHIC PROVIDER.

O nome da CREDENCIAL no nível do servidor deve corresponder ao seguinte formato: <prefix>://<storage_account_path>[/<container_name>]. Os caminhos de contas de armazenamento são descritos na tabela a seguir:

Fonte de dados externa Prefixo Caminho da conta de armazenamento
Armazenamento do Blobs do 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

As credenciais no nível do servidor permitem o acesso ao armazenamento do Azure usando os seguintes tipos de autenticação:

Os usuários do Microsoft Entra poderão acessar qualquer arquivo no armazenamento do Azure se forem membros da função de Proprietário de Dados do Blob de Armazenamento, Colaborador de Dados de Blob de Armazenamento ou Leitor de Dados de Blob de Armazenamento. Os usuários do Microsoft Entra não precisam de credenciais para acessar o armazenamento.

Os usuários autenticados do SQL não podem usar a autenticação do Microsoft Entra para acessar o armazenamento. Eles podem acessar o armazenamento por meio de uma credencial de banco de dados usando identidade gerenciada, chave SAS, entidade de serviço ou se houver acesso público ao armazenamento.

Credencial no escopo do banco de dados

As credenciais no escopo do banco de dados são usadas quando qualquer entidade de segurança chama a função OPENROWSET com DATA_SOURCE ou seleciona dados de tabela externa que não acessam arquivos públicos. A credencial no escopo do banco de dados não precisa corresponder ao nome da conta de armazenamento, ela é referenciada no DATA SOURCE que define o local do armazenamento.

As credenciais no escopo do banco de dados permitem o acesso ao Armazenamento do Azure usando os seguintes tipos de autenticação:

Os usuários do Microsoft Entra poderão acessar qualquer arquivo no armazenamento do Azure se forem membros das funções de Proprietário de Dados de Blob de Armazenamento, Colaborador de Dados de Blob de Armazenamento ou Leitor de Dados de Blob de Armazenamento. Os usuários do Microsoft Entra não precisam de credenciais para acessar o armazenamento.

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

Os usuários autenticados do SQL não podem usar a autenticação do Microsoft Entra para acessar o armazenamento. Eles podem acessar o armazenamento por meio de uma credencial de banco de dados usando identidade gerenciada, chave SAS, entidade de serviço ou se houver acesso público ao armazenamento.

As credenciais no escopo do banco de dados são usadas em fontes externas para especificar qual método de autenticação será usado para acessar esse armazenamento:

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

Exemplos

Acessar uma fonte de dados disponível publicamente

Use o script a seguir para criar uma tabela que acessa uma fonte de dados disponível publicamente.

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

O usuário do banco de dados pode ler o conteúdo dos arquivos da fonte de dados usando a tabela externa ou a função OPENROWSET que faz referência à fonte de dados:

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

Acessar uma fonte de dados usando credenciais

Modifique o script a seguir para criar uma tabela externa que acesse o armazenamento do Azure usando o token SAS, a identidade do usuário do Microsoft Entra ou a identidade gerenciada do espaço de trabalho.

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

O usuário do banco de dados pode ler o conteúdo dos arquivos da fonte de dados usando a tabela externa ou a função OPENROWSET que faz referência à fonte de dados:

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

Próximas etapas

Estes artigos ajudam você a aprender a consultar diferentes tipos de pasta, tipos de arquivo e criar e usar modos de exibição: