Compartilhar via

Configurar a criptografia de coluna usando Always Encrypted com PowerShell

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

Este artigo fornece as etapas para definir a configuração Always Encrypted de destino para colunas de banco de dados usando o cmdlet Set-SqlColumnEncryption (no módulo SqlServer PowerShell). O cmdlet Set-SqlColumnEncryption modifica o esquema do banco de dados de destino e os dados armazenados nas colunas selecionadas. Os dados armazenados em uma coluna podem ser criptografados, recriptografados ou descriptografados, dependendo das configurações de criptografia de destino especificadas para as colunas e da configuração de criptografia atual.

Observação

Se você estiver usando o SQL Server 2019 (15.x) e sua instância do SQL Server estiver configurada com um enclave seguro, poderá executar operações criptográficas in-loco, sem mover dados para fora do banco de dados. Consulte Configurar a criptografia de coluna diretamente utilizando Always Encrypted com enclaves seguros.

Para obter mais informações sobre o suporte Always Encrypted no módulo SqlServer PowerShell, consulte Configure Always Encrypted using PowerShell.

Pré-requisitos

Para definir a configuração de criptografia de destino, você precisa ter certeza:

  • Uma chave de criptografia de coluna é configurada no banco de dados (se você estiver criptografando ou criptografando novamente uma coluna). Para obter detalhes, consulte Configurar chaves sempre criptografadas usando o PowerShell.
  • você pode acessar a chave mestra de coluna para cada coluna que deseja criptografar, criptografar novamente ou descriptografar a partir do computador que executa os cmdlets do PowerShell.
  • você usa o módulo SqlServer PowerShell versão 22.0.50 ou posterior.

Considerações sobre desempenho e disponibilidade

Para aplicar as configurações de criptografia de destino especificadas para o banco de dados, o cmdlet Set-SqlColumnEncryption baixa de forma transparente todos os dados das colunas que contêm as tabelas de destino, carrega os dados de volta para um conjunto de tabelas temporárias (com as configurações criptografadas de destino) e, finalmente, substitui as tabelas originais pelas novas versões das tabelas. O Provedor de Dados do .NET Framework subjacente para SQL Server criptografa e/ou descriptografa dados ao baixar e/ou carregar, dependendo da configuração de criptografia atual da coluna de destino e das configurações de criptografia de destino especificadas para as colunas de destino. A operação para mover os dados pode levar muito tempo, dependendo do tamanho dos dados nas tabelas afetadas e da largura de banda da rede.

O cmdlet Set-SqlColumnEncryption oferece suporte a duas abordagens para configurar a configuração de criptografia de destino: online e offline.

Com a abordagem offline, as tabelas de destino (e quaisquer tabelas relacionadas às tabelas de destino, por exemplo, quaisquer tabelas com as quais uma tabela de destino tenha relações de chave estrangeira) não estão disponíveis para gravar transações durante toda a duração da operação. A semântica das restrições de chave estrangeira (CHECK ou NOCHECK) é sempre preservada ao usar a abordagem offline.

Com a abordagem online (requer o módulo SqlServer PowerShell versão 21.x ou posterior), a operação de copiar e criptografar, descriptografar ou criptografar novamente os dados é executada incrementalmente. Os aplicativos podem ler e gravar dados de e para as tabelas de destino durante toda a operação de movimentação de dados, exceto a última iteração, cuja duração é limitada pelo parâmetro MaxDownTimeInSeconds (que você pode definir). Para detetar e processar as alterações que as aplicações podem fazer enquanto os dados estão a ser copiados, o cmdlet ativa Rastreamento de Alterações no banco de dados de destino. Por isso, é provável que a abordagem online consuma mais recursos no lado do servidor do que a abordagem offline. A operação também pode levar muito mais tempo com a abordagem online, especialmente se uma carga de trabalho intensiva de escrita estiver a ser executada no banco de dados. A abordagem online pode ser usada para criptografar uma tabela de cada vez e a tabela deve ter uma chave primária. Por padrão, as restrições de chave estrangeira são recriadas com a opção NOCHECK para minimizar o impacto nos aplicativos. Você pode impor a preservação da semântica de restrições de chave estrangeira especificando a opção KeepCheckForeignKeyConstraints.

Aqui estão as diretrizes para escolher entre as abordagens offline e online:

Use a abordagem offline:

  • Para minimizar a duração da operação.
  • Para encriptar/desencriptar/voltar a encriptar colunas em várias tabelas ao mesmo tempo.
  • Se a tabela de destino não tiver uma chave primária.

Utilize a abordagem em linha:

  • Para minimizar o tempo de inatividade/indisponibilidade do banco de dados para seus aplicativos.

Considerações de segurança

O cmdlet Set-SqlColumnEncryption, usado para configurar a criptografia para colunas de banco de dados, lida com chaves Always Encrypted e os dados armazenados em colunas de banco de dados. Portanto, é importante executar o cmdlet em um computador seguro. Se o banco de dados estiver no SQL Server, execute o cmdlet de um computador diferente do computador que hospeda a instância do SQL Server. Como o objetivo principal do Always Encrypted é garantir que os dados confidenciais criptografados estejam seguros mesmo se o sistema de banco de dados for comprometido, a execução de um script do PowerShell que processa chaves e/ou dados confidenciais no computador do SQL Server pode reduzir ou anular os benefícios do recurso.

Tarefa Artigo Acede a chaves em texto simples ou ao armazenamento de chaves Acessa a base de dados
Passo 1. Inicie um ambiente PowerShell e importe o módulo SqlServer. Importar o módulo SqlServer Não Não
Passo 2. Conecte-se ao seu servidor e banco de dados Conectando-se a um banco de dados Não Sim
Passo 3. Autentique-se no Azure, se a sua chave mestra de coluna (protegendo a chave de criptografia de coluna, a ser alternada) estiver armazenada no Cofre de Chaves do Azure Connect-AzAccount Sim Não
Passo 4. Obtenha um token de acesso para os Cofres de Chaves do Azure, se a sua chave mestra de coluna estiver armazenada no Cofre de Chaves do Azure. Get-AzAccessToken Não Não
Passo 5. Construa uma matriz de objetos SqlColumnEncryptionSettings - um para cada coluna de banco de dados, você deseja criptografar, criptografar novamente ou descriptografar. SqlColumnMasterKeySettings é um objeto que existe na memória (no PowerShell). Ele especifica o esquema de criptografia de destino para uma coluna. New-SqlColumnEncryptionSettings Não Não
Passo 6. Defina a configuração de criptografia desejada, especificada na matriz de objetos SqlColumnMasterKeySettings, criada na etapa anterior. Uma coluna será criptografada, recriptografada ou descriptografada, dependendo das configurações de destino especificadas e da configuração de criptografia atual da coluna. Set-SqlColumnEncryption

Nota: Este passo pode demorar muito tempo. Seus aplicativos não poderão acessar as tabelas durante toda a operação ou parte dela, dependendo da abordagem (online versus offline) selecionada.		 Sim Sim

Criptografar colunas usando a abordagem offline - Exemplo

O exemplo abaixo demonstra a configuração da criptografia de destino para algumas colunas. Se uma das colunas ainda não estiver encriptada, será encriptada. Se alguma coluna já estiver encriptada usando uma chave diferente e/ou um tipo de encriptação diferente, será desencriptada e, em seguida, encriptada novamente com a chave/tipo de destino especificado.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Criptografar colunas usando a abordagem on-line - Exemplo

O exemplo abaixo demonstra a configuração de criptografia de destino para algumas colunas usando a abordagem online. Se uma das colunas ainda não estiver encriptada, será encriptada. Se alguma coluna já estiver encriptada usando uma chave diferente e/ou um tipo de encriptação diferente, será desencriptada e, em seguida, encriptada novamente com a chave/tipo de destino especificado.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Encrypt the selected columns (or re-encrypt, if they are already encrypted using keys/encrypt types, different than the specified keys/types.
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.SSN" -EncryptionType "Deterministic" -EncryptionKey "CEK1"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Patients.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK1"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -UseOnlineApproach -MaxDowntimeInSeconds 180 -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Desencriptar colunas - Exemplo

O exemplo a seguir mostra como descriptografar todas as colunas que estão atualmente criptografadas em um banco de dados.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
Import-Module Az.Accounts -MinimumVersion 2.2.0

#Connect to Azure
Connect-AzAccount

# Obtain an access token for key vaults.
$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl https://vault.azure.net).Token  

# Connect to your database.
$serverName = "<server name>"
$databaseName = "<database name>"
# Change the authentication method in the connection string, if needed.
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
$database = Get-SqlDatabase -ConnectionString $connStr

# Find all encrypted columns, and create a SqlColumnEncryptionSetting object for each column.
$ces = @()
$tables = $database.Tables
for($i=0; $i -lt $tables.Count; $i++){
    $columns = $tables[$i].Columns
    for($j=0; $j -lt $columns.Count; $j++) {
        if($columns[$j].isEncrypted) {
            $threeColPartName = $tables[$i].Schema + "." + $tables[$i].Name + "." + $columns[$j].Name 
            $ces += New-SqlColumnEncryptionSettings -ColumnName $threeColPartName -EncryptionType "Plaintext" 
        }
    }
}

# Decrypt all columns.
Set-SqlColumnEncryption -ColumnEncryptionSettings $ces -InputObject $database -LogFileDirectory . -KeyVaultAccessToken $keyVaultAccessToken

Pós-encriptação

Limpe o cache de plano para todos os lotes e procedimentos armazenados que acessam a tabela para atualizar as informações de criptografia de parâmetros.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Observação

Se você não remover o plano para a consulta afetada do cache, a primeira execução da consulta após a criptografia poderá falhar.

Use ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ou DBCC FREEPROCCACHE para limpar o cache do plano com cuidado, pois isso pode resultar em degradação temporária do desempenho da consulta. Para minimizar o impacto negativo da limpeza do cache, você pode remover seletivamente os planos apenas para as consultas afetadas.

Chame sp_refresh_parameter_encryption para atualizar os metadados para os parâmetros de cada módulo (procedimento armazenado, função, exibição, gatilho) que persistem em sys.parameters e podem ter sido invalidados pela criptografia das colunas.

Próximos passos

Ver também