Tutorial: Introdução ao Always Encrypted

Artigo
09/04/2023

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

Este tutorial ensina como começar a usar o Always Encrypted. Ela mostrará a você:

Como criptografar colunas selecionadas em seu banco de dados.
Como consultar colunas criptografadas.

Observação

Se você estiver procurando informações sobre o Always Encrypted com enclaves seguros, confira os seguintes tutoriais:

Pré-requisitos

Para este tutorial, é necessário:

Um banco de dados vazio no Banco de Dados SQL do Azure, na Instância Gerenciada de SQL do Azure ou no SQL Server. As instruções abaixo pressupõem que o nome do banco de dados é ContosoHR. Você precisa ser um proprietário do banco de dados (um membro da função db_owner). Para obter informações sobre como criar um banco de dados, confira Guia de início rápido: criar um banco de dados individual – Banco de dados SQL do Azure ou Criar um banco de dados no SQL Server.
Opcional, mas recomendado, especialmente se seu banco de dados estiver no Azure: um cofre de chaves no Azure Key Vault. Para saber mais sobre como criar um cofre de chaves, confira Início Rápido: criar um cofre de chaves usando o portal do Azure.
- Se o cofre de chaves usa o modelo de permissões de política de acesso, verifique se você tem as seguintes permissões de chave no cofre: get, list, create, unwrap key, wrap key, verify, sign. Confira Atribuir uma política de acesso ao Key Vault.
- Se usar o modelo de permissão de RBAC (controle de acesso baseado em função) do Azure, verifique se você é membro da função Diretor de Criptografia do Key Vault no cofre de chaves. Confira Fornecer acesso a chaves, certificados e segredos do Key Vault com um controle de acesso baseado em função do Azure.
A última versão do SQL Server Management Studio (SSMS) ou a versão mais recente dos módulos SqlServer e Az do PowerShell. O módulo Az PowerShell será necessário apenas se você estiver usando o Azure Key Vault.

Etapa 1: Criar e preencher o esquema de banco de dados

Nesta etapa, você vai criar o esquema de RH e a tabela Funcionários. Então, preencherá a tabela com alguns dados.

SSMS
PowerShell

Conecte-se ao banco de dados. Para obter instruções sobre como se conectar a um banco de dados do SSMS, confira Guia de início rápido: conectar e consultar um Banco de Dados SQL do Azure ou uma Instância Gerenciada de SQL do Azure usando o SQL Server Management Studio (SSMS) ou Guia de início rápido: conectar e consultar uma instância do SQL Server usando o SQL Server Management Studio (SSMS).
Abra uma nova janela de consulta para o banco de dados ContosoHR.

Cole e execute as instruções abaixo para criar uma tabela chamada Funcionários.

CREATE SCHEMA [HR];
GO

CREATE TABLE [HR].[Employees]
(
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL
    , [SSN] [char](11) NOT NULL
    , [FirstName] [nvarchar](50) NOT NULL
    , [LastName] [nvarchar](50) NOT NULL
    , [Salary] [money] NOT NULL
) ON [PRIMARY];

Cole e execute as instruções abaixo para adicionar alguns registros de funcionários à tabela Funcionários.

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '795-73-9838'
    , N'Catherine'
    , N'Abel'
    , $31692
);

INSERT INTO [HR].[Employees]
(
    [SSN]
    , [FirstName]
    , [LastName]
    , [Salary]
)
VALUES
(
    '990-00-6818'
    , N'Kim'
    , N'Abercrombie'
    , $55415
);

Em uma sessão do PowerShell, execute os seguintes comandos. Atualize a cadeia de conexão com o endereço do servidor e as configurações de autenticação válidas para o banco de dados.

Import-Module "SqlServer"

# Set your database connection string
$connectionString = "Server = myServerAddress; Database = ContosoHR; ..."

# Create a new table, named Employees.
$query = @'
    CREATE SCHEMA [HR];
    GO
    
    CREATE TABLE [HR].[Employees]
    (
        [EmployeeID] [int] IDENTITY(1,1) NOT NULL
        , [SSN] [char](11) NOT NULL
        , [FirstName] [nvarchar](50) NOT NULL
        , [LastName] [nvarchar](50) NOT NULL
        , [Salary] [money] NOT NULL
    ) ON [PRIMARY];
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

# Add a few rows to the Employees table.
$query = @'
    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '795-73-9838'
        , N'Catherine'
        , N'Abel'
        , $31692
    );

    INSERT INTO [HR].[Employees]
    (
        [SSN]
        , [FirstName]
        , [LastName]
        , [Salary]
    )
    VALUES
    (
        '990-00-6818'
        , N'Kim'
        , N'Abercrombie'
        , $55415
    );
'@
Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Etapa 2: criptografar colunas

Nesta etapa, você provisionará uma chave mestra de coluna e uma chave de criptografia de coluna para Always Encrypted. Em seguida, você vai criptografar as colunas SSN e Salário na tabela Funcionários.

SSMS
PowerShell

O SSMS fornece um assistente que ajuda você a configurar facilmente o Always Encrypted ao configurar a chave mestra da coluna e a chave de criptografia de coluna e criptografar colunas selecionadas.

No Pesquisador de Objetos, expanda Bancos de Dados>ContosoHR>Tabelas.
Clique com o botão direito do mouse na tabela Funcionários e selecione Criptografar Colunas para abrir o Assistente Always Encrypted.
Selecione Avançar na página Introdução do assistente.
Na página Seleção de coluna.
1. Selecione as colunas SSN e Salário. Escolha criptografia determinística para a coluna SSN e criptografia aleatória para a coluna Salário. A criptografia determinística oferece suporte a consultas, como pesquisas de busca de ponto que envolvem comparações de igualdade em colunas criptografadas. A criptografia aleatória não dá suporte a cálculos em colunas criptografadas.
2. Deixe CEK-Auto1 (Novo) como a chave de criptografia de coluna para as duas colunas. Essa chave ainda não existe e o assistente vai gerá-la.
3. Selecione Avançar.
Na página Configuração da Chave Mestra, configure uma nova chave mestra de coluna que será gerada pelo assistente. Primeiro, você precisa selecionar onde deseja armazenar a chave mestra de coluna. O assistente dá suporte a dois tipos de repositório de chaves:
- Azure Key Vault – recomendado se o banco de dados está no Azure
- Repositório de certificados do Windows
Em geral, o Azure Key Vault é a opção recomendada, especialmente se o banco de dados está no Azure.
- Para usar o Azure Key Vault:
  1. Selecione Cofre de Chaves do Azure.
  2. Selecione Entrar e conclua a entrada no Azure.
  3. Depois de entrar, a página exibirá a lista de assinaturas e cofres de chaves aos quais você tem acesso. Selecione a assinatura do Azure que contém o cofre de chaves que você quer usar.
  4. Selecione seu cofre de chaves.
  5. Selecione Avançar.
- Para usar o repositório de certificados do Windows:
  1. Selecione Repositório de certificados do Windows.
  2. Deixe a seleção padrão de Usuário Atual ~ isso instruirá o assistente a gerar um certificado (sua nova chave mestra de coluna) no repositório Usuário Atual.
  3. Selecione Avançar.
Na página Configurações de Criptografia no Local, nenhuma configuração adicional é necessária porque o banco de dados não tem um enclave habilitado. Selecione Avançar.
Na página Configurações de Execução, você será perguntado se deseja continuar com a criptografia ou gerar um script do PowerShell para ser executado depois. Deixe as configurações padrão e selecione Avançar.
Na página Resumo, o assistente informa sobre as ações que ele vai executar. Verifique se todas as informações estão corretas e selecione Concluir.
Na página Resultados, você pode monitorar o progresso das operações do assistente. Aguarde até todas as operações serem concluídas com êxito e selecione Fechar.
(Opcional) Explore as alterações feitas pelo assistente ao banco de dados.
1. Expanda ContosoHR>Segurança>Chaves Always Encrypted para explorar os objetos de metadados para a chave mestra de coluna e a criptografia de coluna que o assistente criou.
2. Também é possível executar as consultas abaixo nas exibições do catálogo do sistema que contêm metadados importantes.
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. No Pesquisador de Objetos, clique com o botão direito do mouse na tabela Funcionários e selecione Tabela de Scripts como>CRIAR para>Nova Janela do Editor de Consultas. Isso abrirá uma nova janela de consulta com a instrução CREATE TABLE para a tabela Employees. Observe a cláusula ENCRYPTED WITH que aparece nas definições das colunas SSN e Salário.
4. Você também pode executar a consulta abaixo em sys.columns para recuperar metadados de criptografia em nível de coluna para as duas colunas criptografadas.
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

Crie uma chave mestra de coluna no repositório de chaves.

Se você estiver usando o Azure Key Vault, execute os comandos abaixo para criar uma chave assimétrica no cofre de chaves. Forneça o ID correto da sua assinatura, o nome do grupo de recursos que contém o cofre de chaves e o nome do cofre de chaves.

Import-Module "Az"
Connect-AzAccount
$subscriptionId = "<your Azure subscription ID"
$resourceGroup = "your resource group name containing your key vault"
$keyVaultName = "your vault name"
$keyVaultKeyName = "your key name"

# Switch to your subscription.
Set-AzConteXt -SubscriptionId $subscriptionId

# To validate the above key vault settings, get the key vault properties.
Get-AzKeyVault -VaultName $keyVaultName -ResourceGroupName $resourceGroup 

# Create a key in the key vault.
$keyVaultKey = Add-AzKeyVaultKey -VaultName $keyVaultName -Name $keyVaultKeyName -Destination "Software"
$keyVaultKey

Se você estiver usando o repositório de certificados do Windows, execute os comandos abaixo para criar um certificado no repositório Usuário Atual.

$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

Conecte-se ao banco de dados usando o módulo PowerShell do SqlServer. Forneça uma cadeia de conexão válida para o banco de dados.
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Provisione um objeto de metadados de chave mestra de coluna (que faz referência à chave mestra de coluna física criada no armazenamento de chaves) no banco de dados.

Se você estiver usando o Azure Key Vault, execute os comandos abaixo.

# Sign in to Azure for the SqlServer PowerShell module
Add-SqlAzureAuthenticationContext -Interactive

# Create a SqlColumnMasterKeySettings in-memory object referencing the key you've created in your key vault. 
$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid

# Create column master key metadata object (referencing your certificate), named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Se você estiver usando o repositório de certificados do Windows, execute os comandos abaixo.

# Create a SqlColumnMasterKeySettings in-memory object referencing your certificate.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

# Create column master key metadata object, named CMK1, in the database.
$cmkName = "CMK1"
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

Gere uma chave de criptografia de coluna, criptografe-a com a chave mestra de coluna criada e crie um objeto de metadados de chave de criptografia de coluna no banco de dados.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```

Criptografe as colunas SSN e Salário na tabela Funcionários. Escolha criptografia determinística para a coluna SSN e criptografia aleatória para a coluna Salário. A criptografia determinística oferece suporte a consultas, como pesquisas de busca de ponto que envolvem comparações de igualdade em colunas criptografadas. A criptografia aleatória não dá suporte a cálculos em colunas criptografadas.

# Encrypt the SSN and Salary columns 
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" -EncryptionType "Deterministic" -EncryptionKey $cekName
$ces += New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" -EncryptionType "Randomized" -EncryptionKey $cekName
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .

(Opcional) Explore as alterações feitas ao seu banco de dados.

Execute os comandos abaixo para consultar as exibições de catálogo do sistema que contêm metadados sobre a chave mestra de coluna e a chave de criptografia de coluna que você criou.

$query = @'
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Execute os comandos abaixo para consultar sys.columns para recuperar metadados de criptografia em nível de coluna para as duas colunas criptografadas.

$query = @'
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
'@

Invoke-SqlCmd -ConnectionString $connectionString -Query $query

Etapa 3: Consultar colunas criptografadas

SSMS
PowerShell

Conecte-se ao banco de dados com o Always Encrypted desabilitado para sua conexão.
1. Abra uma nova janela de consulta.
2. Clique com o botão direito do mouse em qualquer lugar na janela de consulta e selecione Conexão>Alterar conexão. Isso abrirá a caixa de diálogo Conectar-se ao Mecanismo de Banco de Dados.
3. Selecione Opções<<. Isso mostrará guias adicionais na caixa de diálogo Conectar-se ao Mecanismo de Banco de Dados.
4. Selecione a guia Always Encrypted.
5. A opção Habilitar o Always Encrypted (criptografia de coluna) não deve estar selecionada.
6. Selecione Conectar.
Cole e execute a consulta a seguir. A consulta deve retornar dados criptografados binários.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
Conectar-se ao banco de dados com o Always Encrypted habilitado para a conexão.
1. Clique com o botão direito do mouse em qualquer lugar na janela de consulta e selecione Conexão>Alterar conexão. Isso abrirá a caixa de diálogo Conectar-se ao Mecanismo de Banco de Dados.
2. Selecione Opções<<. Isso mostrará guias adicionais na caixa de diálogo Conectar-se ao Mecanismo de Banco de Dados.
3. Selecione a guia Always Encrypted.
4. Selecione Habilitar Always Encrypted (criptografia de coluna).
5. Selecione Conectar.
Execute a mesma consulta novamente. Como você está conectado ao Always Encrypted habilitado para sua conexão de banco de dados, o driver cliente no SSMS tentará descriptografar os dados armazenados em ambas as colunas criptografadas. Se você usar o Azure Key Vault, poderá ser solicitado que você entre no Azure.
Habilitar Parametrização de Always Encrypted. Esse recurso permite que você execute consultas que filtram dados por colunas criptografadas (ou inserir dados em colunas criptografadas).
1. Selecione Consultar no menu principal do SSMS.
2. Selecione Opções de Consulta....
3. Navegue para Execução>Avançado.
4. Marque a opção Habilitar parametrização para Always Encrypted.
5. Selecione OK.
Cole e execute a consulta abaixo, que filtra os dados pela coluna SSN criptografada. A consulta deve retornar uma linha contendo valores de texto não criptografado.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
Opcionalmente, se você estiver usando o Azure Key Vault configurado com o modelo de permissões de política de acesso, siga as etapas abaixo para ver o que acontece quando um usuário tenta recuperar dados de texto não criptografado de colunas criptografadas sem ter acesso à chave mestra de coluna que protege os dados.
1. Remova a permissão de chave unwrap para si mesmo na política de acesso do seu cofre de chaves. Para obter mais informações, confira Atribuir uma política de acesso do Key Vault.
2. Como o driver do cliente no SSMS armazena em cache as chaves de criptografia de coluna adquiridas de um cofre de chaves por duas horas, feche o SSMS e abra-o novamente. Isso vai garantir que o cache de chaves esteja vazio.
3. Conectar-se ao banco de dados com o Always Encrypted habilitado para a conexão.
4. Cole e execute a consulta a seguir. A consulta deve falhar com a mensagem de erro indicando que você não tem a permissão unwrap necessária.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

Conecte-se ao banco de dados com o Always Encrypted desabilitado e execute uma consulta para ler dados de colunas criptografadas. A consulta deve retornar dados criptografados como matrizes binárias.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
Conecte-se ao banco de dados com o Always Encrypted habilitado e execute uma consulta para ler dados de colunas criptografadas. Como você tem acesso à chave mestra de coluna que protege suas colunas criptografadas, a consulta deve retornar dados de texto não criptografado.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

Observação

Invoke-SqlCmd não dá suporte a consultas que podem filtrar ou inserir dados em colunas criptografadas. Essas consultas precisam ser parametrizadas, e Invoke-SqlCmd não dá suporte a consultas parametrizadas.

Próximas etapas

Desenvolver aplicativos usando o Always Encrypted