Consultar colunas usando o Always Encrypted com o SQL Server Management Studio

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo descreve como consultar colunas criptografadas com o Always Encrypted usando o SSMS (SQL Server Management Studio). Com o SSMS, você pode:

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

Recuperando valores de texto cifrado armazenados em colunas criptografadas

A execução de consultas SELECT que recuperam o 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. Verifique se você consegue acessar os metadados sobre as chaves que protegem as colunas nas quais você está executando a consulta. Embora não precise conseguir acessar as chaves mestras de coluna reais, você precisa ter permissões no nível de banco de dados para exibir os objetos de metadados da chave mestra de coluna e da chave de criptografia de coluna no banco de dados. Para obter detalhes, confira Permissões para consultar colunas criptografadas abaixo.
2. Verifique se você desabilitou o Always Encrypted para a conexão de banco de dados na janela do Editor de Consultas, na qual você executará uma consulta SELECT recuperando valores de texto cifrado. Confira Como habilitar e desabilitar o Always Encrypted para uma conexão de banco de dados abaixo.
3. Execute a consulta SELECT. Os dados recuperados de colunas criptografadas serão retornados como valores binários (criptografados).

Exemplo de como recuperar texto cifrado

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

Recuperar 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. Verifique se você consegue acessar as chaves mestras de coluna e os metadados sobre as chaves que protegem as colunas nas quais você está executando a consulta. Para obter detalhes, confira Permissões para consultar colunas criptografadas abaixo.
2. Verifique se você habilitou o Always Encrypted para a conexão de banco de dados na janela do Editor de Consultas, na qual você vai executar uma consulta SELECT recuperando e descriptografando os dados. Isso instruirá o provedor de Dados .NET Framework para SQL Server (usado pelo SSMS) a descriptografar as colunas criptografadas no conjunto de resultados da consulta. Confira Como habilitar e desabilitar o Always Encrypted para uma conexão de banco de dados abaixo.
3. Execute a consulta SELECT. Os dados recuperados de colunas criptografadas serão retornados como valores de texto não criptografado dos tipos de dados originais.

Exemplo de recuperação de texto não criptografado

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

Enviar valores de texto sem formatação destinados a colunas criptografadas

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

1. Verifique se você consegue acessar as chaves mestras de coluna e os metadados das chaves que protegem as colunas nas quais a consulta é executada. Para obter mais informações, confira Permissões para consultar colunas criptografadas abaixo.

2. Verifique se você habilitou o Always Encrypted para a conexão de banco de dados na janela do Editor de Consultas, na qual você vai executar uma consulta SELECT recuperando e descriptografando os dados. Isso instruirá o provedor de Dados .NET Framework para SQL Server (usado pelo SSMS) a descriptografar as colunas criptografadas no conjunto de resultados da consulta. Confira Como habilitar e desabilitar o Always Encrypted para uma conexão de banco de dados abaixo.

3. Certifique-se de que a Parametrização de Always Encrypted esteja habilitada para a janela do Editor de Consultas. (Requer pelo menos o SSMS versão 17.0) Declare uma variável Transact-SQL e inicie-a com um valor que você deseja enviar (inserir, atualizar ou filtrar por) para o banco de dados. Confira Parametrização de Always Encrypted abaixo para obter detalhes.

4. Execute a 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 ele criptografará seu valor antes de enviá-la para o banco de dados.

Exemplo

Supondo que SSN seja uma coluna char(11) criptografada na tabela Patients , o script abaixo tentará localizar uma linha que contenha '795-73-9838' na coluna SSN e retornará o valor da coluna LastName , desde que o Always Encrypted esteja habilitado para a conexão de banco de dados, a Parametrização de Always Encrypted esteja habilitada para a janela do Editor de Consultas e você tenha acesso à chave mestra de coluna configurada para a coluna SSN.

Permissões para consultar colunas criptografadas

Para executar as consultas em colunas criptografadas, incluindo consultas que recuperam dados em texto cifrado, são necessárias as 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 qualquer resultado de consulta ou criptografar os parâmetros de consulta (produzidos por parametrização de variáveis Transact-SQL), você também precisa de permissões do repositório de chaves para acessar e usar a chave mestra de coluna protegendo as colunas de destino. Saiba mais sobre as permissões do repositório de chaves, vá até Criar e armazenar chaves mestras de coluna para Always Encrypted e encontre uma seção relevante para o seu repositório de chaves.

Habilitando e desabilitando o Always Encrypted para uma conexão de banco de dados

Ao se conectar a um banco de dados no SSMS, habilite ou desabilite o Always Encrypted para a conexão de banco de dados. Por padrão, o Always Encrypted está desabilitado.

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

• Descriptografar todos os valores que são recuperados de colunas criptografadas e retornados nos resultados da consulta.
• Criptografar os valores das variáveis Transact-SQL com parâmetros que se destinam a colunas de banco de dados criptografadas.

Se você não habilitar o Always Encrypted para uma conexão, o provedor de dados .NET Framework para SQL Server usado pelo SSMS não tentará criptografar os parâmetros de consulta nem descriptografar os resultados.

Habilite ou desabilite o Always Encrypted ao criar uma conexão ou alterar uma conexão existente usando a caixa de diálogo Conectar ao Servidor.

Para habilitar (desabilitar) o Always Encrypted:

1. Abra a caixa de diálogo Conectar ao Servidor (confira Conectar-se a uma Instância do SQL Server para obter detalhes).
2. Selecione Opções.
3. Selecione a guia Always Encrypted. Para habilitar o Always Encrypted, selecione Habilitar Always Encrypted (criptografia de coluna). Para desabilitar o Always Encrypted, verifique se a opção Habilitar o Always Encrypted (criptografia de coluna) não está selecionada.
4. Selecione Conectar.

Parametrização do Always Encrypted

A Parametrização de Always Encrypted é um recurso do SQL Server Management Studio que converte automaticamente as variáveis Transact-SQL em parâmetros de consulta (instâncias da Classe SqlParameter). (Requer pelo menos o SSMS versão 17.0) Isso permite que o .NET Framework Data Provider para SQL Server subjacente detecte dados destinados a colunas criptografadas e criptografe esses dados antes de enviá-los para o banco de dados.

Sem parametrização, o .NET Framework Data Provider passa cada instrução, que você cria no Editor de Consultas, como uma consulta sem parâmetros. Se a consulta contiver literais ou variáveis Transact-SQL destinados a colunas criptografadas, o Provedor de Dados do .NET Framework para SQL Server não conseguirá detectá-los e criptografá-los antes de enviar a consulta ao banco de dados. Como resultado, a consulta falhará devido à incompatibilidade de tipo (entre a variável Transact-SQL literal de texto sem formatação e a coluna criptografada). Por exemplo, a consulta a seguir falhará sem parametrização, supondo que a coluna SSN seja criptografada.

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

Como habilitar e desabilitar a Parametrização do Always Encrypted

A Parametrização de Always Encrypted é desabilitada por padrão.

Para habilitar/desabilitar a Parametrização de Always Encrypted na janela atual do Editor de Consultas:

1. Selecione Consulta no menu principal.
2. Selecione Opções de Consulta....
3. Navegue para Execução>Avançado.
4. Selecione ou desmarque Habilitar Parametrização de Always Encrypted.
5. Selecione OK.

Para habilitar/desabilitar a Parametrização de Always Encrypted nas janelas futuras do Editor de Consultas:

1. Selecione Ferramentas no menu principal.
2. Selecione Opções....
3. Navegue para Execução da Consulta>SQL Server>Avançado.
4. Selecione ou desmarque Habilitar Parametrização de Always 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ê receberá uma solicitação para habilitá-la.

Funcionamento da Parametrização do Always Encrypted

Se a Parametrização de Always Encrypted e o comportamento do Always Encrypted na conexão de banco de dados estiverem habilitados em uma janela do Editor de Consultas, o SQL Server Management Studio sempre tentará parametrizar as variáveis Transact-SQL que atendem às seguintes condições de pré-requisito:

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

Veja abaixo exemplos de variáveis que serão parametrizadas pelo SQL Server Management Studio.

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 uma parametrização tentada ser 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 hora, a variável deverá ser inicializada usando uma cadeia de caracteres que usa um dos formatos em conformidade com ISO 8601 a seguir.

Estes sã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 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. Caso passe o mouse sobre uma instrução de declaração que foi marcada com um sublinhado de aviso, você verá os resultados do processo de parametrização, incluindo os valores das propriedades de chave do objeto SqlParameter resultante (a variável é mapeada para): SqlDbType, Tamanho, Precisão, Escala, SqlValue. Você também pode ver a lista completa de todas as variáveis que foram parametrizadas com êxito na guia Aviso do modo de exibição Lista de Erros. Para abrir o modo de exibição Lista de Erros , selecione Exibição no menu principal e escolha Lista de Erros.

Se o SQL Server Management Studio tentou parametrizar uma variável, mas a parametrização falhou, a declaração da variável será marcada com um sublinhado de erro. Caso passe o mouse sobre a instrução de declaração que foi marcada com um sublinhado de erro, você obterá resultados sobre o erro. Você também pode ver a lista completa de erros de parametrização de todas as variáveis na guia Erro do modo de exibição Lista de Erros. Para abrir o modo de exibição Lista de Erros , selecione Exibição no menu principal e escolha Lista de Erros.

A captura de tela abaixo mostra um exemplo de seis declarações de variável. O SQL Server Management Studio parametrizou com êxito as primeiras três variáveis. As últimas três variáveis não atenderam às condições de pré-requisito de 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 de parametrização, mas a tentativa de parametrização falhou porque as variáveis foram inicializadas incorretamente.

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.

Próximas etapas

• Desenvolver aplicativos usando o Always Encrypted

Confira também

• Always Encrypted