Настройка шифрования столбцов с помощью Always Encrypted с PowerShell

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL Azure

В этой статье приведены инструкции по настройке целевой конфигурации Always Encrypted для столбцов базы данных с помощью командлета Set-SqlColumnEncryption (в модуле SqlServer PowerShell). Командлет Set-SqlColumnEncryption изменяет схему целевой базы данных, а также данные, хранящиеся в выбранных столбцах. В зависимости от целевых параметров шифрования, указанных для столбцов и текущей конфигурации шифрования, хранимые в столбце данные могут быть зашифрованы, повторно зашифрованы или расшифрованы.

Дополнительные сведения о поддержке Always Encrypted в модуле SqlServer PowerShell см. в статье "Настройка Always Encrypted с помощью PowerShell".

Предварительные условия

Чтобы задать целевую конфигурацию шифрования, необходимо выполнить следующие условия:

• в базе данных должен быть настроен ключ шифрования столбца (если выполняется шифрование или повторное шифрование столбца). Дополнительные сведения см. в разделе "Настройка ключей Always Encrypted с помощью PowerShell".
• Вы можете получить доступ к главному ключу столбца для каждого столбца, который хотите зашифровать, повторно зашифровать или расшифровать, с компьютера, на котором выполняются командлеты PowerShell.
• вы используете модуль SqlServer PowerShell версии 22.0.50 или более поздней версии.

Вопросы, касающиеся производительности и доступности

Чтобы применить указанные параметры шифрования целевого объекта для базы данных, командлет Set-SqlColumnEncryption прозрачно загружает все данные из столбцов, содержащих целевые таблицы, отправляет данные обратно в набор временных таблиц (с целевыми зашифрованными параметрами) и, наконец, заменяет исходные таблицы новыми версиями таблиц. Базовый поставщик данных .NET Framework для SQL Server шифрует и (или) расшифровывает данные при загрузке или отправке в соответствии с текущей конфигурацией шифрования целевого столбца и целевых параметров шифрования, заданных для целевых столбцов. Операция перемещения данных может занять много времени в зависимости от размера данных в затронутых таблицах и пропускной способности сети.

Командлет Set-SqlColumnEncryption поддерживает два подхода для настройки конфигурации целевого шифрования: в сети и автономном режиме.

При использовании автономного режима целевые таблицы (и любые, относящиеся к ним таблицы, например таблицы, связанные с целевой таблицей по внешнему ключу) недоступны для записи транзакций на протяжении операции. Семантика ограничений внешнего ключа (CHECK или NOCHECK) всегда сохраняется при использовании автономного подхода.

При использовании подключенного режима (требуется модуль SqlServer PowerShell 21.x или более поздней версии) операция копирования и шифрования, расшифровки или повторного шифрования данных выполняется постепенно. Приложения могут считывать и записывать данные из целевых таблиц во время операции перемещения данных, за исключением последней итерации, длительность которой ограничена параметром MaxDownTimeInSeconds (которое можно определить). Чтобы отслеживать и обрабатывать изменения, которые приложения могут вносить во время копирования данных, командлет включает отслеживание изменений в целевой базе данных. Поэтому при использовании режима "в сети" на стороне сервера потребляется больше ресурсов. Операции в этом режиме также могут занять гораздо больше времени, в частности при интенсивной записи в базу данных. Режим "в сети" можно использовать для шифрования одной таблицы за раз. При этом она должна содержать первичный ключ. По умолчанию ограничения внешнего ключа повторно создаются с параметром NOCHECK , чтобы свести к минимуму влияние на приложения. Вы можете применить сохранение семантики ограничений внешнего ключа, указав параметр KeepCheckForeignKeyConstraints .

Ниже приведены рекомендации по выбору между режимом "в сети" и "вне сети".

Используйте подход «вне сети»:

• чтобы свести к минимуму время выполнения операции;
• чтобы выполнять шифрование, расшифровку и повторное шифрование столбцов в нескольких таблицах одновременно;
• Если в целевой таблице отсутствует первичный ключ.

Используйте онлайн-подход.

• чтобы свести к минимуму время простоя или недоступность базы данных для приложений.

Соображения безопасности

Командлет Set-SqlColumnEncryption , используемый для настройки шифрования столбцов базы данных, обрабатывает ключи Always Encrypted и данные, хранящиеся в столбцах базы данных. Поэтому командлет нужно запускать на защищенном компьютере. Если ваша база данных размещена на сервере SQL Server, командлет нужно запускать на компьютере, который не является тем же, что и компьютер с экземпляром SQL Server. Поскольку основной задачей функции постоянного шифрования является обеспечение целостности зашифрованных конфиденциальных данных даже в случае нарушения безопасности системы базы данных, выполнение скрипта PowerShell, обрабатывающего ключи или конфиденциальные данные на сервере SQL Server, может снизить или вообще отменить эффект действия функции.

Пример шифрования столбцов в режиме "вне сети"

В примере ниже показано, как задать целевую конфигурацию шифрования для нескольких столбцов. Если какой-либо столбец не зашифрован, он будет зашифрован. Если какой-либо столбец уже зашифрован с помощью другого ключа и (или) другого типа шифрования, он будет расшифрован и повторно зашифрован с помощью указанного целевого ключа или типа шифрования.

# 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

Пример шифрования столбцов в режиме "в сети"

В примере ниже показано, как задать целевую конфигурацию шифрования для нескольких столбцов с помощью режима "в сети". Если какой-либо столбец не зашифрован, он будет зашифрован. Если какой-либо столбец уже зашифрован с помощью другого ключа и (или) другого типа шифрования, он будет расшифрован и повторно зашифрован с помощью указанного целевого ключа или типа шифрования.

# 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

Пример расшифровки столбцов

В примере ниже показано, как расшифровать все столбцы, которые на данный момент зашифрованы в базе данных.

# 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

После шифрования

Снимите кэш плана для всех пакетов и хранимых процедур, которые обращаются к таблице для обновления сведений о шифровании параметров.

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

Вызовите sp_refresh_parameter_encryption, чтобы обновить метаданные для параметров каждого модуля (хранимая процедура, функция, представление, триггер), которые сохраняются в sys.parameters и могут быть недействительными путем шифрования столбцов.

Следующие шаги

• Разработка приложений с помощью Always Encrypted

См. также

• Always Encrypted
• Обзор управления ключами для Always Encrypted
• Настройка Always Encrypted с помощью PowerShell
• Настройка шифрования столбцов с помощью мастера Always Encrypted
• Настройка шифрования столбцов с помощью Always Encrypted с пакетом DAC