Consultar colunas usando Always Encrypted com o SQL Server Management 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 SQL Server Management Studio (SSMS). Com o SSMS, 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).

Observação

O uso de chaves mestras de coluna armazenadas em um HSM gerenciado pelo no Cofre de Chaves do Azure requer o SSMS 18.9 ou uma versão posterior.

Recuperando valores de texto cifrado armazenados em colunas criptografadas

A execução de consultas SELECT que recuperam texto cifrado de dados armazenados em colunas criptografadas (sem descriptografar os dados) não exige que você tenha acesso às chaves mestras de coluna que protegem os dados. Para recuperar valores de uma coluna criptografada como texto cifrado no SSMS:

1. Certifique-se de que consegue aceder aos metadados sobre as chaves que protegem as colunas, contra as quais está a executar a consulta. Embora não seja necessário acessar as chaves mestras de coluna reais, você precisa de permissões no nível do banco de dados para exibir a chave mestra de coluna e os objetos de metadados da chave de criptografia de coluna no banco de dados. Para obter detalhes, consulte Permissões para consultar colunas criptografadas abaixo.
2. Certifique-se de ter desativado Sempre Criptografado para a conexão de banco de dados para a janela do Editor de Consultas, 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.
3. Execute a consulta SELECT. Todos os dados recuperados de colunas criptografadas serão retornados como valores binários (criptografados).

Exemplo de recuperação de texto cifrado

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

Para recuperar valores de uma coluna criptografada como texto sem formatação (para descriptografar os valores):

1. Certifique-se de que consegue aceder às chaves mestras de coluna e aos metadados sobre as chaves que protegem as colunas contra as quais está a executar a consulta. Para obter detalhes, consulte Permissões para consultar colunas criptografadas abaixo.
2. Certifique-se de ter ativado o Always Encrypted para a conexão de banco de dados para a janela do Editor de Consultas, a partir da qual você executará uma consulta SELECT recuperando e descriptografando seus dados. Isso instruirá o Provedor de Dados do .NET Framework para SQL Server (usado pelo SSMS) 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.
3. Execute a 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 de recuperação de texto sem formatação

Supondo que o SSN seja uma coluna de char(11) 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

Para executar uma consulta que envia um valor direcionado a uma coluna criptografada, por exemplo, uma consulta que insere, atualiza ou filtra por um valor armazenado em uma coluna criptografada:

1. Certifique-se de que consegue aceder às chaves mestras de coluna e aos metadados das chaves que protegem as colunas contra as quais a sua consulta é executada. Para mais informações, consulte abaixo Permissões para interrogar colunas encriptadas.

2. Certifique-se de ter ativado o Always Encrypted para a conexão de banco de dados para a janela do Editor de Consultas, a partir da qual você executará uma consulta SELECT recuperando e descriptografando seus dados. Isso instruirá o Provedor de Dados do .NET Framework para SQL Server (usado pelo SSMS) 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.

3. Verifique se a Parametrização para Sempre Criptografado está habilitada para a janela do Editor de Consultas. (Requer pelo menos o SSMS versão 17.0.) 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. Consulte abaixo Parametrização para Always Encrypted para obter detalhes.

4. Execute sua consulta enviando o valor da variável Transact-SQL para o banco de dados. O SSMS 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 e retornará o valor da coluna LastName, desde que Always Encrypted esteja habilitado para a conexão de banco de dados, a Parametrização para Always Encrypted esteja habilitada para a janela do Editor de Consultas e você tenha acesso à chave mestra da coluna configurada para a coluna SSN.

Permissões para consultar colunas criptografadas

Para executar consultas em colunas criptografadas, incluindo consultas que recuperam dados em texto cifrado, você precisa das permissões VIEW ANY COLUMN MASTER KEY DEFINITION e VIEW ANY COLUMN ENCRYPTION KEY DEFINITION no banco 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 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 SSMS, pode habilitar ou desabilitar o Always Encrypted para a conexão de banco de dados. Por padrão, Sempre criptografado está desativado.

Habilitar Always Encrypted para uma conexão de banco de dados instrui o Provedor de Dados do .NET Framework para SQL Server, usado pelo SQL Server Management 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 Provedor de Dados do .NET Framework para SQL Server, usado pelo SSMS, não tentará criptografar parâmetros de consulta ou descriptografar resultados.

Você pode habilitar ou desabilitar o Always Encrypted ao criar uma nova conexão ou alterar uma conexão existente usando a caixa de diálogo Conectar ao Servidor.

Para ativar (desativar) Sempre Encriptado:

1. Abra a caixa de diálogo Conectar-se ao servidor (consulte Conectar-se a uma instância do SQL Server para obter detalhes).
2. Selecione Opções.
3. Selecione a guia Sempre criptografado. Para ativar o Always Encrypted, selecione Enable Always Encrypted (criptografia de coluna). Para desativar o Sempre Encriptado, certifique-se de que Ativar Sempre Encriptado (encriptação de coluna) não esteja selecionado.
4. Selecione Conectar.

Dica

Para alternar entre Sempre Encriptado ativo e inativo para uma janela existente do Editor de Consultas:

1. Clique com o botão direito do mouse em qualquer lugar na janela do Editor de Consultas.
2. Selecione Conexão>Alterar Conexão .... Isto abrirá a caixa de diálogo Conectar ao Servidor para a conexão atual na janela do Editor de Consultas.
3. Habilite ou desative o Always Encrypted, seguindo as etapas acima e clique em Connect.

Observação

Para executar instruções que aproveitam um enclave seguro do lado do servidor quando você estiver usando Always Encrypted com enclaves seguros, consulte instruções Run Transact-SQL using secure enclaves.

Parametrização para Always Encrypted

A parametrização para Always Encrypted é um recurso do SQL Server Management Studio que converte automaticamente Transact-SQL variáveis em parâmetros de consulta (instâncias de SqlParameter Class). (Requer pelo menos o SSMS versão 17.0.) Isto permite que o Provedor de Dados do .NET Framework subjacente para SQL Server detete dados direcionados a colunas cifradas e cifre esses dados antes de enviá-los para a base de dados.

Sem parametrização, o Provedor de Dados do .NET Framework passa cada instrução que cria no Editor de Consultas 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 NCHAR(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 para a janela atual do Editor de Consultas:

1. Selecione de consulta no menu principal.
2. Selecione Opções de Consulta....
3. Navegue até Execução Avançado>.
4. Selecione ou desmarque Ativar parametrização paraAlways Encrypted .
5. Selecione OK.

Para ativar/desativar a parametrização para Always Encrypted para futuras janelas do Editor de Consultas:

1. Selecione Ferramentas no menu principal.
2. Selecione Opções....
3. Navegue até Execução de Consulta>SQL Server>Advanced.
4. Selecione ou desmarque Ativar parametrização paraAlways Encrypted .
5. Selecione OK.

Se você executar uma consulta em uma janela do Editor de Consultas que usa uma conexão de banco de dados com o Always Encrypted habilitado, mas a parametrização não estiver habilitada para a janela do Editor de Consultas, você será solicitado a habilitá-la.

Observação

A parametrização para Always Encrypted funciona apenas em janelas do Editor de Consultas que usam conexões de banco de dados com Always Encrypted habilitado (consulte Habilitando e desabilitando a parametrização para Always Encrypted). Nenhuma variável Transact-SQL será parametrizada se a janela do Editor de Consultas 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 Sempre Criptografado e o comportamento Sempre Criptografado na conexão de banco de dados estiverem habilitados para uma janela do Editor de Consultas, o SQL Server Management Studio tentará parametrizar Transact-SQL variáveis que atendam às seguintes condições de pré-requisito:

• 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, o SQL Server Management Studio irá parametrizar.

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

E, aqui estão alguns exemplos de variáveis que o SQL Server Management 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 deverá ser inicializada usando uma cadeia de caracteres usando um dos seguintes formatos compatíveis com ISO 8601.

Aqui estão os exemplos de Transact-SQL declarações de variáveis 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 SQL Server Management 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 aviso no Editor de Consultas. Se você passar o mouse sobre uma instrução de declaração que foi marcada com um sublinhado de aviso, verá os resultados do processo de parametrização, incluindo os valores das principais propriedades do objeto SqlParameter resultante (a variável é mapeada para): SqlDbType, Size, Precision, ScaleSqlValue. Você também pode ver a lista completa de todas as variáveis que foram parametrizadas com êxito na guia de Aviso da exibição Lista de Erros. Para abrir a vista Lista de Erros, selecione Ver no menu principal e, em seguida, selecione Lista de Erros.

Se o SQL Server Management 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 aba Erro da exibição de Lista de Erros . Para abrir a vista Lista de Erros, selecione Ver no menu principal e, em seguida, selecione Lista de Erros.

A captura de tela abaixo mostra um exemplo de seis declarações de variáveis. O SQL Server Management Studio parametrizou com êxito as três primeiras variáveis. As três últimas variáveis não atendiam às condições de pré-requisito para parametrização e, portanto, o SQL Server Management Studio não tentou parametrizá-las (suas declarações não são marcadas de forma alguma).

Outro exemplo abaixo, mostra duas variáveis que atendem às condições de pré-requisito para parametrização, mas a tentativa de parametrização falhou porque as variáveis foram inicializadas incorretamente.

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, ela tem como destino. 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 executadas pelo .NET Framework dentro do SQL Server Management Studio. Devido a diferenças entre o sistema de tipo .NET Framework 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