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

  • Artigo

As consultas de conjunto de SQL sem servidor leem os ficheiros diretamente a partir do 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 arquivos de armazenamento subjacentes. O administrador de armazenamento deve permitir que a entidade de segurança do Microsoft Entra leia/grave arquivos ou gere a chave de assinatura de acesso compartilhado (SAS) que será usada para acessar o armazenamento.
  • Nível de serviço SQL - O usuário deve ter concedido permissão para ler dados usando tabela externa ou para executar a OPENROWSET função. Saiba mais sobre as permissões necessárias nesta secção.

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

Permissões de armazenamento

Um pool SQL sem servidor no espaço de trabalho Synapse Analytics pode ler o conteúdo de arquivos armazenados no armazenamento do Azure Data Lake. Você precisa configurar permissões no armazenamento para permitir que um usuário que executa uma consulta SQL leia os arquivos. Existem três métodos para permitir o acesso aos ficheiros:

  • O RBAC (controle de acesso baseado em função) permite atribuir uma função a algum usuário do Microsoft Entra no locatário onde o armazenamento está colocado. Um leitor deve ser membro da função Leitor de Dados de 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 de Colaborador de Dados de Blob de Armazenamento ou Proprietário de Dados de Blob de Armazenamento. A função Proprietário do Armazenamento não implica que um usuário também seja Proprietário dos Dados de Armazenamento.
  • As Listas de Controle de Acesso (ACL) permitem que você defina permissões de Leitura(R), Gravação(W) e Execução(X) refinadas nos arquivos e diretórios no armazenamento do Azure. As ACL podem ser atribuídas aos utilizadores do Microsoft Entra. Se os leitores quiserem ler um ficheiro num caminho no Armazenamento do Microsoft Azure, terão de ter a ACL de Execução(X) em todas as pastas no caminho do ficheiro e a ACL de Leitura(R) no ficheiro. Saiba mais sobre como definir permissões de ACL na camada de armazenamento.
  • A assinatura de acesso compartilhado (SAS) permite que um leitor acesse os arquivos no armazenamento do Azure Data Lake usando o token de tempo limitado. O leitor nem precisa ser autenticado como usuário do Microsoft Entra. O token de SAS inclui as permissões concedidas ao leitor, bem como o período durante o qual 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 de SAS pode ser definido na conta de armazenamento ou em diretórios específicos. Saiba mais sobre como conceder acesso limitado aos recursos do Armazenamento do Microsoft Azure com assinaturas de acesso partilhado.

Como alternativa, pode disponibilizar os ficheiros publicamente através de acesso anónimo. Esta abordagem NÃO deverá ser utilizada se tiver dados não públicos.

Tipos de autorização de armazenamento suportados

Um usuário que fez logon em um pool SQL sem servidor deve ser 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 o armazenamento não público: identidade do usuário, assinatura de acesso compartilhado, entidade de serviço e identidade gerenciada.

Nota

A 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 login no pool 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 reutiliza o mesmo token por uma hora. Consultas de longa execução podem 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 de 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 que você seja um proprietário de uma conta de armazenamento, ainda precisará se adicionar a uma das funções de dados de Blob de armazenamento. Para saber mais sobre o controle de acesso no Azure Data Lake Store Gen2, consulte o artigo Controle de acesso no Azure Data Lake Storage Gen2 .

Cenários entre locatários

Nos casos em que o Armazenamento do Azure está em um locatário diferente do pool SQL sem servidor Synapse, a autorização via Service Principal é o método recomendado. A autorização SAS também é possível, enquanto a Identidade Gerenciada não é suportada.

Tipo de Autorização Armazenamento protegido por firewall armazenamento não protegido por firewall
SAS Suportado Suportado
Principal de Serviço Não suportado Suportado

Nota

Se o Armazenamento do Azure estiver protegido por um firewall do Armazenamento do Azure, a Entidade de Serviço não terá suporte.

Tipos de autorização suportados para usuários de bancos de dados

A tabela a seguir fornece tipos de autorização de Armazenamento do 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 SQL Usuário do Microsoft Entra Principal de serviço
Identidade do usuário Não suportado Suportado Suportado
SAS Suportado Suportado Suportado
Principal de serviço Suportado Suportado Suportado
Identidade Gerida Suportado Suportado Suportado

Tipos de autorização e armazenamentos suportados

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

Tipo de autorização Armazenamento de Blobs ADLS Gen1 ADLS Gen2
SAS Suportado Não suportado Suportado
Principal de serviço Suportado Suportado Suportado
Identidade Gerida Suportado Suportado Suportado
Identidade do usuário Suportado Suportado Suportado

Cenários entre locatários

Nos casos em que o Armazenamento do Azure está em um locatário diferente do pool SQL sem servidor do Azure Synapse Analytics, a autorização via 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 identidade de serviço gerenciado.

Tipo de Autorização Armazenamento protegido por firewall armazenamento não protegido por firewall
SAS Suportado Suportado
Principal de serviço Não suportado Suportado

Nota

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

Armazenamento protegido por firewall

Você pode configurar contas de armazenamento para permitir o acesso a um pool SQL sem servidor específico criando uma regra de instância de recurso. Ao acessar o armazenamento protegido pelo firewall, use a Identidade do Usuário ou a Identidade Gerenciada.

Nota

O recurso de firewall no Armazenamento do Azure está em visualização pública e está disponível em todas as regiões de nuvem pública.

A tabela a seguir fornece tipos de autorização de Armazenamento do Azure protegidos por firewall 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 SQL Usuário do Microsoft Entra Principal de serviço
Identidade do usuário Não suportado Suportado Suportado
SAS Não suportado Não suportado Não suportado
Principal de serviço Não suportado Não suportado Não suportado
Identidade Gerida Suportado Suportado Suportado

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

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

  1. Procure a 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 seu espaço de trabalho do Azure Synapse.
  4. Selecione Microsoft.Synapse/workspaces como um tipo de recurso.
  5. Selecione o nome do seu espaço de trabalho como um nome de instância.
  6. Selecione Guardar.

Configuração do firewall do Armazenamento do Azure via PowerShell

Siga estas etapas para configurar sua conta de armazenamento e adicionar uma exceção para o espaço de trabalho Synapse do Azure.

  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 pelo menos a versão 3.4.0. Você pode verificar sua versão 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 variáveis no PowerShell:

    • Nome do grupo de recursos - você pode encontrá-lo no portal do Azure em Visão geral da sua conta de armazenamento.
    • Nome da conta - nome da conta de armazenamento protegida por regras de firewall.
    • ID do Locatário - você pode encontrá-lo no portal do Azure no Microsoft Entra ID, em Propriedades, em Propriedades do locatário.
    • Nome do espaço de trabalho - Nome do espaço de trabalho 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 grupos de recursos em 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 PowerShell a seguir compara a variável das etapas anteriores com a $resourceid 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 Azure, seu ponto de extremidade do pool SQL sem servidor precisa de uma credencial que contenha as informações de autenticação. Dois tipos de credenciais são usados:

  • A credencial no nível do servidor é usada para consultas ad-hoc executadas usando OPENROWSET a função. O nome da credencial deve corresponder à URL de armazenamento.
  • Uma credencial com escopo de banco de dados é usada para tabelas externas. Referências DATA SOURCE de tabela externa 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 solte uma credencial no nível do servidor, um administrador deve conceder a ALTER ANY CREDENTIAL permissão para seu login no banco de dados mestre. Por exemplo:

    GRANT ALTER ANY CREDENTIAL TO [login_name];

  • Para permitir que um usuário crie ou solte uma credencial com escopo de banco de dados, um administrador deve conceder a CONTROL permissão 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 credenciais

Os usuários do 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 REFERENCES permissão em uma credencial específica.

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

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

Para conceder uma permissão em uma REFERENCES credencial com escopo de banco de dados para um usuário de banco de dados, use 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 um logon SQL chama OPENROWSET a função sem um DATA_SOURCE para ler arquivos em uma conta de armazenamento.

O nome da credencial no nível do servidor deve corresponder à URL base do armazenamento do Azure, opcionalmente seguida por um nome de contêiner. Uma credencial é adicionada executando CREATE CREDENTIAL. Tem de apresentar o CREDENTIAL NAME argumento.

Nota

O FOR CRYPTOGRAPHIC PROVIDER argumento não é suportado.

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

Fonte de dados externa Prefixo Caminho da conta de armazenamento
Armazenamento de Blobs do Azure https <storage_account>.blob.core.windows.net
Armazenamento do Azure Data Lake Ger1 https <storage_account>.azuredatalakestore.net/webhdfs/v1
Armazenamento do Azure Data Lake Ger2 https <storage_account>.dfs.core.windows.net

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

Os usuários do Microsoft Entra podem acessar qualquer arquivo no armazenamento do Azure se forem membros da função 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.

Os usuários autenticados pelo 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 com escopo de banco de dados

As credenciais com escopo de banco de dados são usadas quando qualquer função de chamada OPENROWSET principal funciona com DATA_SOURCE ou seleciona dados de uma tabela externa que não acessam arquivos públicos. A credencial com escopo do banco de dados não precisa corresponder ao nome da conta de armazenamento, ela é referenciada em FONTE DE DADOS que define o local do armazenamento.

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

Os usuários do Microsoft Entra podem acessar qualquer arquivo no armazenamento do Azure se forem membros das funções 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 pelo 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 com escopo de banco de dados são usadas em fontes de dados 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

Aceder a uma fonte de dados publicamente disponível

Use o script a seguir para criar uma tabela que acesse a 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óximos passos

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