Consulta colunas usando Always Encrypted com o Azure Data Studio

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada SQL do Azure

Este artigo descreve como consultar colunas, criptografadas com Always Encrypted usando Azure Data Studio. Com o Azure Data Studio, você pode:

• Recupere valores de texto cifrado armazenados em colunas criptografadas.
• Recupere valores de texto sem formatação armazenados em colunas criptografadas.
• Envie valores de texto simples direcionados a colunas criptografadas (por exemplo, em instruções INSERT ou UPDATE e como um parâmetro de pesquisa das cláusulas WHERE em instruções SELECT).

Recuperando valores de texto cifrado armazenados em colunas criptografadas

Esta seção descreve como recuperar dados armazenados em colunas criptografadas como texto cifrado.

Passos

1. Certifique-se de ter desabilitado Always Encrypted para a conexão de banco de dados para a janela de consulta, a partir da qual você executará uma consulta SELECT recuperando valores de texto cifrado. Consulte Habilitando e desabilitando o Always Encrypted para uma conexão de banco de dados abaixo.
2. Execute a consulta SELECT. Todos os dados recuperados de colunas criptografadas serão retornados como valores binários (criptografados).

Exemplo

Supondo que SSN seja uma coluna criptografada na tabela Patients, a consulta mostrada abaixo recuperará valores de texto cifrado binário, se Always Encrypted estiver desabilitado para a conexão de banco de dados.

Recuperando valores de texto sem formatação armazenados em colunas criptografadas

Esta seção descreve como recuperar dados armazenados em colunas criptografadas como texto cifrado.

Pré-requisitos

• Azure Data Studio versão 17.1 ou posterior.
• Você precisa ter acesso à(s) chave(s) mestra(s) de coluna e aos metadados sobre a(s) chave(s) que protege(m) as colunas contra as quais você está executando sua consulta. Para obter detalhes, consulte Permissões para consultar colunas criptografadas abaixo.
• A(s) chave(s) mestra(s) de coluna deve(m) ser armazenada(s) num cofre de chaves no Cofre de Chaves do Azure ou no Repositório de Certificados do Windows. O Azure Data Studio não dá suporte a outros armazenamentos de chaves e não dá suporte a chaves mestras de coluna armazenadas em HSMs gerenciados no Cofre de Chaves do Azure.

Passos

1. Habilite Sempre Encriptado para a conexão de base de dados para a janela de consultas, a partir da qual se executará uma consulta SELECT, recuperando e desencriptando os seus dados. Isso instruirá o Microsoft .NET Data Provider for SQL Server (usado pelo Azure Data Studio) a descriptografar as colunas criptografadas no conjunto de resultados da consulta. Consulte Habilitando e desabilitando o Always Encrypted para uma conexão de banco de dados abaixo.
2. Execute a sua consulta SELECT. Todos os dados recuperados de colunas criptografadas serão retornados como valores de texto sem formatação dos tipos de dados originais.

Exemplo

Supondo que o SSN seja uma coluna criptografada na tabela Patients, a consulta mostrada abaixo retornará valores de texto sem formatação se Sempre Criptografado estiver habilitado para a conexão de banco de dados e se você tiver acesso à chave mestra de coluna configurada para a coluna SSN.

Envio de valores de texto simples direcionados a colunas criptografadas

Esta seção descreve como executar uma consulta que envia valores destinados a uma coluna criptografada. Por exemplo, uma consulta que insere, atualiza ou filtra por um valor armazenado em uma coluna criptografada:

Pré-requisitos

• Azure Data Studio versão 18.1 ou posterior.
• Você precisa ter acesso à(s) chave(s) mestra(s) de coluna e aos metadados sobre a(s) chave(s) que protege(m) as colunas contra as quais você está executando sua consulta. Para obter detalhes, consulte Permissões para consultar colunas criptografadas abaixo.
• A(s) chave(s) mestra(s) de coluna deve(m) ser armazenada(s) num cofre de chaves no Cofre de Chaves do Azure ou no Repositório de Certificados do Windows. O Azure Data Studio não dá suporte a outros armazenamentos de chaves e não dá suporte a chaves mestras de coluna armazenadas em HSMs gerenciados no Cofre de Chaves do Azure.

Passos

1. Habilite Sempre Criptografado para a conexão de banco de dados para a janela de consulta, a partir da qual você executará uma consulta SELECT recuperando e descriptografando seus dados. Isso instruirá o Microsoft .NET Data Provider for SQL Server (usado pelo Azure Data Studio) a criptografar parâmetros de consulta direcionados a colunas criptografadas e descriptografar os resultados recuperados de colunas criptografadas. Consulte Habilitando e desabilitando o Always Encrypted para uma conexão de banco de dados abaixo.
2. Habilite a parametrização para Always Encrypted para a janela de consulta. Veja Parametrização do Always Encrypted abaixo para detalhes.
3. Declare uma variável Transact-SQL e inicialize-a com um valor que você deseja enviar (inserir, atualizar ou filtrar por) para o banco de dados.
4. Execute sua consulta enviando o valor da variável Transact-SQL para o banco de dados. O Azure Data Studio converterá a variável em um parâmetro de consulta e criptografará seu valor antes de enviá-la para o banco de dados.

Exemplo

Supondo que SSN seja uma coluna de char(11) criptografada na tabela Patients, o script abaixo tentará encontrar uma linha contendo '795-73-9838' na coluna SSN. Os resultados serão retornados se Always Encrypted estiver habilitado para a conexão de banco de dados, a Parametrização para Always Encrypted estiver habilitada para a janela de consulta e você tiver acesso à chave mestra da coluna configurada para a coluna SSN.

@SSN char(11) = '795-73-9838' SELECT * FROM [dbo]. [Doentes] WHERE [SSN] = @SSN consulta e os resultados da consulta." />

Permissões para consultar colunas criptografadas

Para executar consultas em colunas criptografadas, incluindo consultas que recuperam dados em texto cifrado, precisa das permissões VER QUALQUER DEFINIÇÃO DE CHAVE MESTRA DE COLUNA e VER QUALQUER DEFINIÇÃO DE CHAVE DE CRIPTOGRAFIA DE COLUNA na base de dados.

Além das permissões acima, para descriptografar quaisquer resultados de consulta ou criptografar quaisquer parâmetros de consulta (produzidos parametrizando variáveis Transact-SQL), você também precisa de permissões de armazenamento de chaves para acessar e usar a chave mestra da coluna que protege as colunas de destino. Para obter informações detalhadas sobre permissões de armazenamento de chaves, vá para Criar e armazenar chaves mestras de coluna para Always Encrypted e encontre uma seção relevante para o seu armazenamento de chaves.

Ativando e desativando o Always Encrypted para uma conexão de banco de dados

Quando você se conecta a um banco de dados no Azure Data Studio, pode habilitar ou desabilitar o Always Encrypted para a conexão de banco de dados. Por padrão, Sempre criptografado está desativado.

Habilitar o Always Encrypted para uma conexão de banco de dados instrui o Microsoft .NET Data Provider for SQL Server, usado pelo Azure Data Studio, a tentar de forma transparente:

• Descriptografe todos os valores recuperados de colunas criptografadas e retornados nos resultados da consulta.
• Criptografe os valores das variáveis Transact-SQL parametrizadas que visam colunas de banco de dados criptografadas.

Se você não habilitar o Always Encrypted para uma conexão, o Microsoft .NET Data Provider for SQL Server não tentará criptografar parâmetros de consulta ou descriptografar resultados.

Você pode habilitar ou desabilitar o Always Encrypted quando se conectar a um banco de dados. Para obter informações gerais sobre como se conectar a um banco de dados, consulte:

• Guia de início rápido: conectar e consultar o SQL Server usando o Azure Data Studio
• Guia de início rápido: usar o Azure Data Studio para conectar e consultar o banco de dados SQL do Azure

Para ativar (desativar) Sempre Encriptado:

1. Na caixa de diálogo Conexão , clique em Avançado....
2. Para habilitar o Sempre Criptografado para a conexão, defina o campo Sempre Criptografado como Habilitado. Para desativar Sempre Criptografado, deixe o valor do campo Sempre Criptografado em branco ou defina-o como Desabilitado.
3. Clique OK para fechar Propriedades avançadas .

Para executar instruções que aproveitam um enclave seguro do lado do servidor quando você estiver usando Always Encrypted com enclaves seguros, você precisa especificar um protocolo de atestado de enclave e uma URL de atestado de enclave, além de habilitar Always Encrypted para a conexão. Para obter informações detalhadas, consulte Pré-requisitos para executar instruções T-SQL usando enclaves no Azure Data Studio.

Dica

Para alternar entre a opção Sempre Criptografado habilitada e desabilitada para uma janela de consulta existente, clique em Desconectar e, em seguida, clique em Conectar e conclua as etapas acima para se reconectar ao banco de dados com os valores desejados do campo Sempre Criptografado do.

Observação

Atualmente, o botão Alterar Conexão numa janela de consulta não oferece suporte para a funcionalidade de alternar entre o Sempre Criptografado ativado e desativado.

Parametrização para Always Encrypted

A parametrização para Always Encrypted é um recurso do Azure Data Studio 18.1 e posterior que converte automaticamente Transact-SQL variáveis em parâmetros de consulta (instâncias de SqlParameter Class). Isso permite que o Microsoft .NET Data Provider for SQL Server detete dados destinados a colunas cifradas e cifre esses dados antes de enviá-los para o banco de dados.

Sem parametrização, o Microsoft .NET Data Provider for SQL Server passa cada instrução criada na janela de consulta como uma consulta não parametrizada. Se a consulta contiver literais ou variáveis Transact-SQL que visam colunas criptografadas, o Provedor de Dados do .NET Framework para SQL Server não poderá detetá-las e criptografá-las antes de enviar a consulta para o banco de dados. Como resultado, a consulta falhará devido à incompatibilidade de tipo (entre a variável literal Transact-SQL de texto simples e a coluna criptografada). Por exemplo, a consulta a seguir falhará sem parametrização, supondo que a coluna SSN esteja criptografada.

DECLARE @SSN CHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN

Ativando e desativando a parametrização para Always Encrypted

A parametrização para Always Encrypted está desativada por padrão.

Para ativar/desativar a parametrização para Always Encrypted:

1. Selecione Arquivo>Preferências>Configurações (Código>Preferências>Configurações no Mac).
2. Navegue até Data>Microsoft SQL Server.
3. Selecione ou desmarque Ativar parametrização paraAlways Encrypted .
4. Feche a janela Configurações do.

Observação

A parametrização para Always Encrypted funciona apenas em uma consulta que usa conexões de banco de dados com Always Encrypted habilitado (consulte Habilitando e desabilitando Always Encrypted para uma conexão de banco de dados). Nenhuma variável Transact-SQL será parametrizada se a janela de consulta usar uma conexão de banco de dados sem Always Encrypted habilitado.

Como funciona a parametrização para Always Encrypted

Se a Parametrização para Always Encrypted e Always Encrypted estiverem ativadas para uma janela de consulta, o Azure Data Studio tentará parametrizar variáveis Transact-SQL que atendam aos seguintes pré-requisitos:

• São declarados e inicializados na mesma instrução (inicialização embutida). As variáveis declaradas usando instruções SET separadas não serão parametrizadas.
• São inicializados usando um único literal. As variáveis inicializadas usando expressões, incluindo quaisquer operadores ou funções, não serão parametrizadas.

Abaixo estão exemplos de variáveis que o Azure Data Studio irá parametrizar.

DECLARE @SSN char(11) = '795-73-9838';
   
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;

Aqui estão alguns exemplos de variáveis que o Azure Data Studio não tentará parametrizar:

DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';

DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal

DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal

Para que uma tentativa de parametrização seja bem-sucedida:

• O tipo do literal usado para a inicialização da variável a ser parametrizada deve corresponder ao tipo na declaração da variável.
• Se o tipo declarado da variável for um tipo de data ou um tipo de hora, a variável deve ser inicializada usando uma cadeia de caracteres usando um dos seguintes formatos compatíveis com ISO 8601.

Aqui estão exemplos de declarações de variáveis Transact-SQL que resultarão em erros de parametrização:

DECLARE @BirthDate date = '01/04/1999' -- unsupported date format   
   
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable   

O Azure Data Studio usa o Intellisense para informar quais variáveis podem ser parametrizadas com êxito e quais tentativas de parametrização falham (e por quê).

Uma declaração de uma variável que pode ser parametrizada com êxito é marcada com um sublinhado de mensagem informativa na janela de consulta. Se você passar o mouse sobre uma instrução de declaração que foi marcada com um sublinhado de mensagem informativa, verá a mensagem contendo os resultados do processo de parametrização, incluindo os valores das principais propriedades do objeto resultante SqlParameter Class (a variável é mapeada para: SqlDbType, Size, Precision, Scale, SqlValue). Você também pode ver a lista completa de todas as variáveis que foram parametrizadas com êxito na vista Problemas. Para abrir a vista de Problemas , selecione Ver>Problemas.

Se o Azure Data Studio tiver tentado parametrizar uma variável, mas a parametrização tiver falhado, a declaração da variável será marcada com um sublinhado de erro. Se você passar o mouse sobre a declaração que foi marcada com um sublinhado de erro, obterá os resultados sobre o erro. Você também pode ver a lista completa de erros de parametrização para todas as variáveis na vista Problemas.

Observação

Como o Always Encrypted suporta um subconjunto limitado de conversões de tipo, em muitos casos é necessário que o tipo de dados de uma variável Transact-SQL seja o mesmo que o tipo da coluna do banco de dados de destino a que se destina. Por exemplo, supondo que o tipo da coluna SSN na tabela Patients seja char(11), a consulta abaixo falhará, pois o tipo da variável @SSN, que é nchar(11), não corresponde ao tipo da coluna.

DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;

Msg 402, Level 16, State 2, Line 5   
The data types char(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') collation_name = 'Latin1_General_BIN2' 
and nchar(11) encrypted with (encryption_type = 'DETERMINISTIC', 
encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', 
column_encryption_key_database_name = 'Clinic') are incompatible in the equal to operator.

Observação

Sem parametrização, toda a consulta, incluindo conversões de tipo, é processada dentro do Banco de Dados SQL do SQL Server/Azure. Com a parametrização habilitada, algumas conversões de tipo são realizadas pelo Microsoft .NET Data Provider for SQL Server dentro do Azure Data Studio. Devido a diferenças entre o sistema de tipo Microsoft .NET e o sistema de tipo SQL Server (por exemplo, precisão diferente de alguns tipos, como float), uma consulta executada com parametrização habilitada pode produzir resultados diferentes da consulta executada sem parametrização habilitada.

Próximos passos

• Desenvolver aplicações utilizando Always Encrypted

Ver também

• Sempre criptografado