Tutorial: Começar com o Always Encrypted

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

Este tutorial ensina-te como começar com o Always Encrypted. Irá mostrar-lhe:

• Como encriptar colunas selecionadas na sua base de dados.
• Como consultar colunas encriptadas.

Observação

Se está à procura de informações sobre Always Encrypted com enclaves seguros, consulte os tutoriais seguintes:

• Introdução ao uso do Always Encrypted com enclaves seguros
• Tutorial: Começar a usar o Always Encrypted com enclaves seguros no SQL Server

Pré-requisitos

Para este tutorial, precisas de:

• Uma base de dados vazia no Azure SQL Database, Azure SQL Managed Instance ou SQL Server. As instruções abaixo assumem que o nome da base de dados é ContosoHR. Tens de ser proprietário da base de dados (membro da função db_owner ). Para informações sobre como criar uma base de dados, veja Quickstart: Criar uma base de dados única - Azure SQL Database ou Criar uma base de dados no SQL Server.
• Opcional, mas recomendado, especialmente se a tua base de dados estiver no Azure: um cofre de chaves no Azure Key Vault. Para obter informações sobre como criar um cofre de chaves, consulte Guia de início rápido: criar um cofre de chaves usando o portal do Azure.
  • Se o cofre de chaves usar o modelo de permissões de política de acesso, verifique se você tem as seguintes permissões de chave no cofre de chaves: get, list, create, unwrap key, wrap key, verify, sign. Consulte Atribuir uma política de acesso ao Azure Key Vault.
  • Se estiver a utilizar o modelo de permissões RBAC (controlo de acesso baseado em funções) do Azure, certifique-se de que é membro da função Key Vault Crypto Officer para o seu cofre de chaves. Veja Fornecer acesso a chaves, certificados e segredos do Key Vault com um controle de acesso baseado em função do Azure.
• A versão mais recente do SQL Server Management Studio (SSMS) ou a versão mais recente dos módulos SqlServer e Az PowerShell. O módulo PowerShell do Arizona é obrigatório apenas se estiveres a usar o Azure Key Vault.

Passo 1: Criar e preencher o esquema da base de dados

Neste passo, irá criar o esquema de RH e a tabela de Colaboradores . Depois, vais preencher a tabela com alguns dados.

SSMS

1. Liga-te à tua base de dados. Para instruções sobre como se ligar a uma base de dados a partir do SSMS, consulte Quickstart: Conecte e consulte uma Base de Dados SQL Azure ou uma Instância Gerida Azure SQL usando SQL Server Management Studio (SSMS) ou Quickstart: Conecte e consulte uma instância SQL Server usando SQL Server Management Studio (SSMS).

2. Abra uma nova janela de consulta para a base de dados ContosoHR .

3. Cole e execute as instruções abaixo para criar uma nova tabela, chamada Employees.

   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];
   

4. Cole e execute as instruções abaixo para adicionar alguns registos de funcionários à tabela de Colaboradores .

   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
   );
   

PowerShell

Numa sessão PowerShell, execute os seguintes comandos. Certifique-se de atualizar a cadeia de ligação com o endereço do seu servidor e as definições de autenticação válidas para a sua base 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

Passo 2: Encriptar colunas

Neste passo, irá provisionar uma chave principal de coluna e uma chave de criptografia de coluna para Always Encrypted. Depois, vais encriptar as colunas SSN e Salário na tabela de Empregados .

SSMS

O SSMS fornece um assistente que o ajuda a configurar facilmente o Always Encrypted, configurando uma chave mestra de coluna, uma chave de encriptação de colunas, e a encriptação de colunas selecionadas.

1. No Explorador de Objetos, expanda Bases de Dados>ContosoHR>Tabelas.

2. Clique com o botão direito na tabela Funcionários e selecione Encriptar Colunas para abrir o assistente Sempre Encriptado.

   

3. Selecione Próximo na página de Introdução do assistente.

4. Na página Seleção de Colunas.

   1. Selecione as colunas SSN e Salário . Escolha encriptação determinística para a coluna SSN e encriptação aleatória para a coluna Salário . A encriptação determinística suporta consultas, tais como pesquisas de pesquisa por ponto que envolvem comparações de igualdade em colunas encriptadas. A encriptação aleatória não suporta quaisquer cálculos em colunas encriptadas.
   2. Deixe CEK-Auto1 (Novo) como chave de encriptação da coluna para ambas as colunas. Esta chave ainda não existe e será gerada pelo mago.
   3. Selecione Avançar.

   

5. Na página de Configuração da Chave Mestra , configure uma nova chave mestra de coluna que será gerada pelo assistente. Primeiro, tens de escolher onde queres guardar a tua chave mestra de coluna. O assistente suporta dois tipos principais de repositório de chaves:

   • Azure Key Vault - recomendado se a tua base de dados estiver em Azure
   • Armazenamento de certificados do Windows

   Em geral, o Azure Key Vault é a opção recomendada, especialmente se a sua base de dados estiver no Azure.

   • Para usar o Azure Key Vault:

     1. Selecione Azure Key Vault.
     2. Selecione Iniciar Sessão e conclua o início de sessão no Azure.
     3. Depois de iniciar sessão, a página mostrará a lista de subscrições e cofres de chaves a que tem acesso. Selecione uma subscrição Azure que contenha o cofre de chaves que quer usar.
     4. Selecione o cofre das chaves.
     5. Selecione Avançar.

     

   • Para usar a loja de certificados do Windows:

     1. Selecione a loja de certificados do Windows.

     2. Mantenha a seleção padrão de Utilizador Atual – isto instruirá o assistente a gerar um certificado (a sua nova chave mestra de coluna) na loja Utilizador Atual.

        

     3. Selecione Avançar.

6. Na página Definições de Encriptação In-Place não é necessária qualquer configuração adicional porque a base de dados não tem um enclave ativado. Selecione Avançar.

7. Na página de Definições de Execução , perguntam-lhe se quer avançar com a encriptação ou gerar um script PowerShell para ser executado mais tarde. Saia das definições predefinidas e selecione Próximo.

8. Na página Resumo, o assistente informa o usuário sobre as ações que irá realizar. Verifique se toda a informação está correta e selecione Terminar.

9. Na página de Resultados, pode seguir o progresso das operações do assistente. Espere até que todas as operações sejam concluídas com sucesso e selecione Fechar.

   

10. (Opcional) Explore as alterações que o assistente fez na sua base de dados.

    1. Expanda ContosoHR>Segurança>Chaves Sempre Encriptadas para explorar os objetos de metadados da chave mestra da coluna e da cifra de coluna que o assistente criou.

    2. Também pode executar as consultas abaixo contra as vistas do catálogo do sistema que contêm metadados chave.

       SELECT * FROM sys.column_master_keys;
       SELECT * FROM sys.column_encryption_keys
       SELECT * FROM sys.column_encryption_key_values
       

    3. No Explorador de Objetos, clique com o botão direito na tabela Empregados e selecione Tabela de Scripts como>CRIAR para>a Janela do Editor de Novas Consultas. Isto abrirá uma nova janela de consulta com a instrução CREATE TABLE para a tabela Employees . Note a cláusula ENCRYPTED WITH que aparece nas definições das colunas SSN e Salário .

    4. Também pode executar a consulta abaixo contra sys.columns para recuperar metadados de encriptação ao nível das colunas para as duas colunas encriptadas.

       SELECT
       [name]
       , [encryption_type]
       , [encryption_type_desc]
       , [encryption_algorithm_name]
       , [column_encryption_key_id]
       FROM sys.columns
       WHERE [encryption_type] IS NOT NULL;
       

PowerShell

1. Crie uma chave mestra de coluna no seu cofre de chaves.

   • Se estiver a usar o Azure Key Vault, execute os comandos abaixo para criar uma chave assimétrica no seu cofre de chaves. Certifique-se de que fornece o ID correto da sua subscrição, o nome do grupo de recursos que contém o seu 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 estiver a utilizar o armazenamento de certificados do Windows, execute os seguintes comandos para criar um certificado na sua store de Usuário Atual.

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

2. Liga-te à tua base de dados, usando o módulo PowerShell do SqlServer. Certifique-se de que fornece uma cadeia de ligação válida para a sua base de dados.

   $database = Get-SqlDatabase -ConnectionString $connectionString
   $database
   

3. Disponibilize um objeto de metadados de chave mestra de coluna (que faça referência à chave mestra física da coluna que criou no seu armazenamento de chaves) na sua base de dados.

   • Se estiveres a usar o Azure Key Vault, executa 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 estiveres a usar a loja de certificados do Windows, executa 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
     

4. Gera uma chave de encriptação de coluna, encripte-a com a chave mestra de coluna que criou e crie um objeto de metadados de chave de encriptação de coluna na base de dados.

   $cekName = "CEK1"
   New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
   

5. Encripte as colunas SSN e Salário na Tabela dos Empregados . Escolha encriptação determinística para a coluna SSN e encriptação aleatória para a coluna Salário . A encriptação determinística suporta consultas, tais como pesquisas de pesquisa por ponto que envolvem comparações de igualdade em colunas encriptadas. A encriptação aleatória não suporta quaisquer cálculos em colunas encriptadas.

   # 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 .
   
   

6. (Opcional) Explore as alterações que fez na sua base de dados.

   • Execute os comandos abaixo para consultar as vistas do catálogo do sistema que contenham metadados sobre a chave mestra da coluna e a chave de encriptação da coluna que 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 e obter metadados de encriptação ao nível das colunas para as duas colunas encriptadas.

     $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
     

Passo 3: Consultar colunas encriptadas

SSMS

1. Liga-te à tua base de dados com o modo Sempre Encriptado desativado para a tua ligação.

   1. Abra uma nova janela de consulta.
   2. Clique com o botão direito em qualquer lugar da janela de consulta e selecione Ligação>Alterar Ligação. Isto abrirá o diálogo Ligar ao Motor de Base de Dados .
   3. Selecione Opções <<. Isto mostrará separadores adicionais no diálogo Ligar ao Motor de Base de Dados.
   4. Selecione o separador Sempre Encriptado.
   5. Certifique-se de que Ativar Sempre Encriptado (encriptação por coluna) não está selecionado.
   6. Selecione Conectar.

   

2. Insira e execute a seguinte consulta. A consulta deve devolver dados binários encriptados.

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

   

3. Liga-te à tua base de dados com o modo Sempre Encriptado ativado para a tua ligação.

   1. Clique com o botão direito em qualquer lugar da janela de consulta e selecione Ligação>Alterar Ligação. Isto abrirá o diálogo Ligar ao Motor de Base de Dados .
   2. Selecione Opções <<. Isto mostrará separadores adicionais no diálogo Ligar ao Motor de Base de Dados.
   3. Selecione o separador Sempre Encriptado.
   4. Selecione Ativar Sempre Encriptado (encriptação de colunas).
   5. Selecione Conectar.

   

4. Repete a mesma consulta. Como está conectado com o Always Encrypted ativado para a sua ligação à base de dados, o driver cliente no SSMS tentará decifrar os dados armazenados em ambas as colunas cifradas. Se usar o Azure Key Vault, pode ser solicitado a iniciar sessão no Azure.

   

5. Ativar a parametrização para Always Encrypted. Esta funcionalidade permite executar consultas que filtram dados por colunas encriptadas (ou inserir dados em colunas encriptadas).

   1. Selecione Consultar no menu principal do SSMS.
   2. Selecionar Opções de Consulta....
   3. Navegar para Execução>Avançada.
   4. Certifica-te de que a opção Enable Parameterization for Always Encrypted está assinalada.
   5. Selecione OK.

   

6. Cole e execute a consulta abaixo, que filtra os dados pela coluna SSN encriptada. A consulta deve devolver uma linha contendo valores de texto simples.

   DECLARE @SSN [char](11) = '795-73-9838'
   SELECT [SSN], [Salary] FROM [HR].[Employees]
   WHERE [SSN] = @SSN
   

7. Opcionalmente, se estiver a usar o Azure Key Vault configurado com o modelo de permissões da política de acesso, siga os passos abaixo para ver o que acontece quando um utilizador tenta recuperar dados de texto simples de colunas encriptadas sem ter acesso à chave mestra da coluna que protege os dados.

   1. Remova a permissão de chave unwrap para você mesmo na política de acesso do seu cofre de chaves. Para obter mais informações, consulte Atribuir uma política de acesso ao Key Vault.
   2. Como o controlador do cliente no SQL Server Management Studio (SSMS) armazena em cache as chaves de encriptação das colunas adquiridas de um cofre de chaves por 2 horas, é recomendável fechar o SSMS e abri-lo novamente. Isto garante que a cache de chaves está vazia.
   3. Liga-te à tua base de dados com o modo Sempre Encriptado ativado para a tua ligação.
   4. Insira e execute a seguinte consulta. A consulta deve falhar com a mensagem de erro a indicar que não tem a permissão necessária unwrap .

   SELECT [SSN], [Salary] FROM [HR].[Employees]
   

PowerShell

1. Ligue-se à sua base de dados com o Always Encrypted desativado e execute uma consulta para ler dados de colunas encriptadas. A consulta deve devolver dados encriptados na forma de matrizes binárias.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString $connectionString -Query $query
   

2. Ligue-se à sua base de dados com o Always Encrypted ativado e execute uma consulta para ler dados de colunas encriptadas. Como tem acesso à chave mestra da coluna que protege as suas colunas encriptadas, a consulta deverá devolver dados em texto simples.

   $query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
   Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
   

Observação

O Invoke-SqlCmd não suporta consultas que possam filtrar ou inserir dados em colunas encriptadas. Estas consultas precisam de ser parametrizadas, e o Invoke-SqlCmd não suporta consultas parametrizadas.

Próximos passos

• Desenvolva aplicativos usando Always Encrypted

Consulte também

• Documentação sempre encriptada
• Sempre encriptado com documentação de enclaves seguros
• Provisionar chaves sempre encriptadas usando SQL Server Management Studio
• Configurar Sempre Encriptado usando PowerShell
• Wizard Always Encrypted
• Colunas de consulta usando Always Encrypted com o SQL Server Management Studio