Руководство. Начало работы с Always Encrypted

Статья
08/21/2023

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этом руководстве описано, как приступить к работе с Always Encrypted. Буду рассмотрены следующие темы.

Как зашифровать выбранные столбцы в базе данных.
Как запрашивать зашифрованные столбцы.

Примечание.

Если вы ищете информацию о Always Encrypted с безопасными анклавами, ознакомьтесь со следующими руководствами.

Необходимые компоненты

Для работы с этим руководством необходимы указанные ниже компоненты.

Пустая база данных в База данных SQL Azure, Управляемый экземпляр SQL Azure или SQL Server. В приведенных ниже инструкциях предполагается, что имя базы данных — ContosoHR. Необходимо быть владельцем базы данных (членом роли db_owner ). Сведения о создании базы данных см. в кратком руководстве. Создание одной базы данных — База данных SQL Azure или создание базы данных в SQL Server.
Необязательно, но рекомендуется, особенно если база данных находится в Azure: хранилище ключей в Azure Key Vault. Сведения о создании хранилища ключей см. в кратком руководстве по созданию хранилища ключей с помощью портал Azure.
- Если хранилище ключей использует модель разрешений политики доступа, убедитесь, что у вас есть следующие разрешения ключа в хранилище ключей: get, , listcreate, unwrap key, wrap key, . verifysign См. раздел "Назначение политики доступа Key Vault".
- Если вы используете модель разрешений на основе ролей Azure (RBAC), убедитесь, что вы являетесь членом роли офицера шифрования Key Vault для хранилища ключей. Сведения о предоставлении доступа к ключам Key Vault, сертификатам и секретам с помощью управления доступом на основе ролей Azure.
Последняя версия SQL Server Management Studio (SSMS) или последняя версия модулей SqlServer и Az PowerShell. Модуль Az PowerShell требуется только в том случае, если вы используете Azure Key Vault.

Шаг 1. Создание и заполнение схемы базы данных

На этом шаге вы создадите схему отдела кадров и таблицу Employees . Затем вы заполните таблицу некоторыми данными.

SSMS
PowerShell

Подключитесь к базе данных. Инструкции по подключению к базе данных из SSMS см. в кратком руководстве. Подключение и запрос База данных SQL Azure или Управляемый экземпляр SQL Azure с помощью СРЕДЫ SQL Server Management Studio (SSMS) или краткое руководство. Подключение и запрос экземпляр SQL Server с помощью SQL Server Management Studio (SSMS).
Откройте новое окно запроса для базы данных ContosoHR .

Вставьте и выполните приведенные ниже инструкции, чтобы создать новую таблицу с именем 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];

Вставьте и выполните приведенные ниже инструкции, чтобы добавить несколько записей сотрудников в таблицу Employees .

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 выполните следующие команды. Обязательно обновите строка подключения адресом сервера и параметрами проверки подлинности, допустимыми для базы данных.

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

Шаг 2. Шифрование столбцов

На этом шаге вы подготовите главный ключ столбца и ключ шифрования столбцов для Always Encrypted. Затем вы зашифруете столбцы SSN и Зарплата в таблице Employees .

SSMS
PowerShell

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

В обозреватель объектов разверните таблицы ContosoHR>баз данных>.
Щелкните правой кнопкой мыши таблицу Employees и выберите "Шифровать столбцы", чтобы открыть мастер Always Encrypted.
Нажмите кнопку "Далее" на странице "Введение" мастера.
На странице выбора столбцов.
1. Выберите столбцы SSN и Зарплата. Выберите детерминированное шифрование для столбца SSN и случайное шифрование для столбца "Зарплата ". Детерминированное шифрование поддерживает запросы, такие как поиск по точкам, включающих сравнения равенства по зашифрованным столбцам. Случайное шифрование не поддерживает вычисления в зашифрованных столбцах.
2. Оставьте CEK-Auto1 (New) в качестве ключа шифрования столбца для обоих столбцов. Этот ключ еще не существует и будет создан мастером.
3. Выберите Далее.
На странице "Конфигурация главного ключа" настройте новый главный ключ столбца, который будет создан мастером. Сначала необходимо выбрать место хранения главного ключа столбца. Мастер поддерживает два типа хранилища ключей:
- Azure Key Vault — рекомендуется, если база данных находится в Azure
- Хранилище сертификатов Windows
Как правило, Azure Key Vault рекомендуется использовать, особенно если база данных находится в Azure.
- Чтобы использовать Azure Key Vault, выполните приведенные действия.
  1. Выберите Хранилище ключей Azure.
  2. Выберите вход и завершите вход в Azure.
  3. После входа на страницу отобразится список подписок и хранилищ ключей, к которых у вас есть доступ. Выберите подписку Azure, содержащую хранилище ключей, которую вы хотите использовать.
  4. Выберите нужное хранилище ключей.
  5. Выберите Далее.
- Чтобы использовать хранилище сертификатов Windows, выполните приведенные действия.
  1. Выберите хранилище сертификатов Windows.
  2. Оставьте выбранный по умолчанию выбор текущего пользователя . Это позволит мастеру создать сертификат (новый главный ключ столбца) в текущем хранилище пользователей .
  3. Выберите Далее.
На странице параметров шифрования на месте дополнительная конфигурация не требуется, так как база данных не включает анклав. Выберите Далее.
На странице "Параметры запуска" вам будет предложено продолжить шифрование или создать скрипт PowerShell для последующего выполнения. Оставьте параметры по умолчанию и нажмите кнопку "Далее".
На странице сводки мастер сообщает о выполняемых действиях. Проверьте правильность всех сведений и нажмите кнопку "Готово".
На странице результатов можно отслеживать ход выполнения операций мастера. Дождитесь успешного завершения всех операций и нажмите кнопку "Закрыть".
(Необязательно) Изучите изменения, внесенные мастером в базу данных.
1. Разверните ключи Безопасности>ContosoHR>Always Encrypted, чтобы изучить объекты метаданных для главного ключа столбца и шифрование столбцов, созданное мастером.
2. Вы также можете выполнить приведенные ниже запросы к представлениям системного каталога, содержащим ключевые метаданные.
```
SELECT * FROM sys.column_master_keys;
SELECT * FROM sys.column_encryption_keys
SELECT * FROM sys.column_encryption_key_values
```
3. В обозреватель объектов щелкните правой кнопкой мыши таблицу "Сотрудники" и выберите "Таблица скриптов" как>CREATE To>New Редактор запросов Window. Откроется новое окно запроса с инструкцией CREATE TABLE для таблицы Employees . Обратите внимание на предложение ENCRYPTED WITH , которое отображается в определениях столбцов SSN и Зарплата .
4. Вы также можете выполнить приведенный ниже запрос к sys.column , чтобы получить метаданные шифрования на уровне столбцов для двух зашифрованных столбцов.
```
SELECT
[name]
, [encryption_type]
, [encryption_type_desc]
, [encryption_algorithm_name]
, [column_encryption_key_id]
FROM sys.columns
WHERE [encryption_type] IS NOT NULL;
```

Создайте главный ключ столбца в хранилище ключей.
- Если вы используете Azure Key Vault, выполните приведенные ниже команды, чтобы создать асимметричный ключ в хранилище ключей. Убедитесь, что указан правильный идентификатор подписки, имя группы ресурсов, содержащей хранилище ключей, и имя хранилища ключей.
```
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
```
- Если вы используете хранилище сертификатов Windows, выполните приведенные ниже команды, чтобы создать сертификат в текущем хранилище пользователей.
```
$cert = New-SelfSignedCertificate -Subject "HRCMK" -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange
```
Подключитесь к базе данных с помощью модуля SqlServer PowerShell. Убедитесь, что для базы данных указан допустимый строка подключения.
```
$database = Get-SqlDatabase -ConnectionString $connectionString
$database
```

Подготовьте объект метаданных главного ключа столбца (который ссылается на главный ключ физического столбца, созданный в хранилище ключей) в базе данных.

Если вы используете Azure Key Vault, выполните приведенные ниже команды.

# 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

Если вы используете хранилище сертификатов Windows, выполните приведенные ниже команды.

# 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

Создайте ключ шифрования столбца, зашифруйте его с помощью созданного главного ключа столбца и создайте объект метаданных ключа шифрования столбца в базе данных.
```
$cekName = "CEK1"
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
```
Шифрование столбцов SSN и Зарплаты в таблице "Сотрудники ". Выберите детерминированное шифрование для столбца SSN и случайное шифрование для столбца "Зарплата ". Детерминированное шифрование поддерживает запросы, такие как поиск по точкам, включающих сравнения равенства по зашифрованным столбцам. Случайное шифрование не поддерживает вычисления в зашифрованных столбцах.
```
# 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 .
```
(Необязательно) Изучите изменения, внесенные в базу данных.
- Выполните приведенные ниже команды, чтобы запрашивать представления системного каталога, содержащие метаданные о главном ключе столбца и созданном ключе шифрования столбца.
```
$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
```
- Выполните приведенные ниже команды, чтобы запросить sys.column метаданные шифрования на уровне столбцов для двух зашифрованных столбцов.
```
$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
```

Подключитесь к базе данных с отключенным always Encrypted для подключения.
1. Откройте новое окно запроса.
2. Щелкните правой кнопкой мыши в любом месте в окне запроса и выберите пункт "Изменение подключения".> Откроется диалоговое окно "Подключение к ядро СУБД".
3. Выберите Параметры<<. В диалоговом окне "Подключение к ядро СУБД" отображаются дополнительные вкладки.
4. Выберите вкладку Always Encrypted.
5. Убедитесь, что параметр Enable Always Encrypted (шифрование столбцов) не выбран.
6. Нажмите Подключиться.
Вставьте и выполните следующий запрос. Запрос должен возвращать двоичные зашифрованные данные.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```
Подключитесь к базе данных с поддержкой Always Encrypted для подключения.
1. Щелкните правой кнопкой мыши в любом месте в окне запроса и выберите пункт "Изменение подключения".> Откроется диалоговое окно "Подключение к ядро СУБД".
2. Выберите Параметры<<. В диалоговом окне "Подключение к ядро СУБД" отображаются дополнительные вкладки.
3. Выберите вкладку Always Encrypted.
4. Выберите Включить Always Encrypted (шифрование столбцов).
5. Нажмите Подключиться.
Повторно выполните тот же запрос. Так как вы подключены к подключению к базе данных с поддержкой Always Encrypted, драйвер клиента в SSMS попытается расшифровать данные, хранящиеся в обоих зашифрованных столбцах. Если вы используете Azure Key Vault, вам может потребоваться войти в Azure.
Включите параметризацию для Always Encrypted. Эта функция позволяет выполнять запросы, фильтрующие данные по зашифрованным столбцам (или вставлять данные в зашифрованные столбцы).
1. В главном меню SSMS выберите Запрос.
2. Щелкните Параметры запроса....
3. Выберите Выполнение>Дополнительно.
4. Убедитесь, что установлен флажок включить параметризацию для Always Encrypted .
5. Нажмите ОК.
Вставьте и выполните приведенный ниже запрос, который фильтрует данные по зашифрованным столбцу SSN . Запрос должен возвращать одну строку, содержащую значения обычного текста.
```
DECLARE @SSN [char](11) = '795-73-9838'
SELECT [SSN], [Salary] FROM [HR].[Employees]
WHERE [SSN] = @SSN
```
При необходимости, если вы используете Azure Key Vault, настроенную с моделью разрешений политики доступа, следуйте приведенным ниже инструкциям, чтобы узнать, что происходит при попытке пользователя получить данные открытого текста из зашифрованных столбцов без доступа к главному ключу столбца, защищающим данные.
1. Удалите разрешение ключа unwrap для себя в политике доступа для хранилища ключей. Подробнее см. в статье Назначение политики доступа Key Vault.
2. Так как драйвер клиента в SSMS кэширует ключи шифрования столбцов, полученные из хранилища ключей в течение 2 часов, закройте SSMS и снова откройте его. Это гарантирует, что кэш ключей пуст.
3. Подключитесь к базе данных с поддержкой Always Encrypted для подключения.
4. Вставьте и выполните следующий запрос. Запрос должен завершиться ошибкой с сообщением об ошибке, указывая, что у вас отсутствует требуемое unwrap разрешение.
```
SELECT [SSN], [Salary] FROM [HR].[Employees]
```

Подключитесь к базе данных с отключенным параметром Always Encrypted и запустите запрос для чтения данных из зашифрованных столбцов. Запрос должен возвращать зашифрованные данные в виде двоичных массивов.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString $connectionString -Query $query
```
Подключитесь к базе данных с поддержкой Always Encrypted и запустите запрос для чтения данных из зашифрованных столбцов. Так как у вас есть доступ к главному ключу столбца, защищающим зашифрованные столбцы, запрос должен возвращать данные обычного текста.
```
$query = "SELECT [SSN], [Salary] FROM [HR].[Employees]"
Invoke-SqlCmd -ConnectionString "$connectionString; Column Encryption Setting = Enabled" -Query $query
```

Примечание.

Invoke-SqlCmd не поддерживает запросы, которые могут фильтровать или вставлять данные в зашифрованные столбцы. Такие запросы необходимо параметризовать, и Invoke-SqlCmd не поддерживает параметризованные запросы.

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

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

Поделиться через