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

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

2. Откройте новое окно запроса для базы данных ContosoHR .

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

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

1. В обозреватель объектов разверните таблицы ContosoHR>баз данных>.

2. Щелкните правой кнопкой мыши таблицу Employees и выберите "Шифровать столбцы", чтобы открыть мастер Always Encrypted.

   

3. Нажмите кнопку "Далее" на странице "Введение" мастера.

4. На странице выбора столбцов.

   1. Выберите столбцы SSN и Зарплата. Выберите детерминированное шифрование для столбца SSN и случайное шифрование для столбца "Зарплата ". Детерминированное шифрование поддерживает запросы, такие как поиск по точкам, включающих сравнения равенства по зашифрованным столбцам. Случайное шифрование не поддерживает вычисления в зашифрованных столбцах.
   2. Оставьте CEK-Auto1 (New) в качестве ключа шифрования столбца для обоих столбцов. Этот ключ еще не существует и будет создан мастером.
   3. Выберите Далее.

   

5. На странице "Конфигурация главного ключа" настройте новый главный ключ столбца, который будет создан мастером. Сначала необходимо выбрать место хранения главного ключа столбца. Мастер поддерживает два типа хранилища ключей:

   • 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. Выберите Далее.

6. На странице параметров шифрования на месте дополнительная конфигурация не требуется, так как база данных не включает анклав. Выберите Далее.

7. На странице "Параметры запуска" вам будет предложено продолжить шифрование или создать скрипт PowerShell для последующего выполнения. Оставьте параметры по умолчанию и нажмите кнопку "Далее".

8. На странице сводки мастер сообщает о выполняемых действиях. Проверьте правильность всех сведений и нажмите кнопку "Готово".

9. На странице результатов можно отслеживать ход выполнения операций мастера. Дождитесь успешного завершения всех операций и нажмите кнопку "Закрыть".

   

10. (Необязательно) Изучите изменения, внесенные мастером в базу данных.

    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;
       

1. Создайте главный ключ столбца в хранилище ключей.

   • Если вы используете 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
     

2. Подключитесь к базе данных с помощью модуля SqlServer PowerShell. Убедитесь, что для базы данных указан допустимый строка подключения.

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

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

   • Если вы используете 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
     

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

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

5. Шифрование столбцов 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 .
   
   

6. (Необязательно) Изучите изменения, внесенные в базу данных.

   • Выполните приведенные ниже команды, чтобы запрашивать представления системного каталога, содержащие метаданные о главном ключе столбца и созданном ключе шифрования столбца.

     $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
     

1. Подключитесь к базе данных с отключенным always Encrypted для подключения.

   1. Откройте новое окно запроса.
   2. Щелкните правой кнопкой мыши в любом месте в окне запроса и выберите пункт "Изменение подключения".> Откроется диалоговое окно "Подключение к ядро СУБД".
   3. Выберите Параметры<<. В диалоговом окне "Подключение к ядро СУБД" отображаются дополнительные вкладки.
   4. Выберите вкладку Always Encrypted.
   5. Убедитесь, что параметр Enable Always Encrypted (шифрование столбцов) не выбран.
   6. Нажмите Подключиться.

   

2. Вставьте и выполните следующий запрос. Запрос должен возвращать двоичные зашифрованные данные.

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

   

3. Подключитесь к базе данных с поддержкой Always Encrypted для подключения.

   1. Щелкните правой кнопкой мыши в любом месте в окне запроса и выберите пункт "Изменение подключения".> Откроется диалоговое окно "Подключение к ядро СУБД".
   2. Выберите Параметры<<. В диалоговом окне "Подключение к ядро СУБД" отображаются дополнительные вкладки.
   3. Выберите вкладку Always Encrypted.
   4. Выберите Включить Always Encrypted (шифрование столбцов).
   5. Нажмите Подключиться.

   

4. Повторно выполните тот же запрос. Так как вы подключены к подключению к базе данных с поддержкой Always Encrypted, драйвер клиента в SSMS попытается расшифровать данные, хранящиеся в обоих зашифрованных столбцах. Если вы используете Azure Key Vault, вам может потребоваться войти в Azure.

   

5. Включите параметризацию для Always Encrypted. Эта функция позволяет выполнять запросы, фильтрующие данные по зашифрованным столбцам (или вставлять данные в зашифрованные столбцы).

   1. В главном меню SSMS выберите Запрос.
   2. Щелкните Параметры запроса....
   3. Выберите Выполнение>Дополнительно.
   4. Убедитесь, что установлен флажок включить параметризацию для Always Encrypted .
   5. Нажмите ОК.

   

6. Вставьте и выполните приведенный ниже запрос, который фильтрует данные по зашифрованным столбцу SSN . Запрос должен возвращать одну строку, содержащую значения обычного текста.

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

7. При необходимости, если вы используете Azure Key Vault, настроенную с моделью разрешений политики доступа, следуйте приведенным ниже инструкциям, чтобы узнать, что происходит при попытке пользователя получить данные открытого текста из зашифрованных столбцов без доступа к главному ключу столбца, защищающим данные.

   1. Удалите разрешение ключа unwrap для себя в политике доступа для хранилища ключей. Подробнее см. в статье Назначение политики доступа Key Vault.
   2. Так как драйвер клиента в SSMS кэширует ключи шифрования столбцов, полученные из хранилища ключей в течение 2 часов, закройте SSMS и снова откройте его. Это гарантирует, что кэш ключей пуст.
   3. Подключитесь к базе данных с поддержкой Always Encrypted для подключения.
   4. Вставьте и выполните следующий запрос. Запрос должен завершиться ошибкой с сообщением об ошибке, указывая, что у вас отсутствует требуемое unwrap разрешение.

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

1. Подключитесь к базе данных с отключенным параметром Always Encrypted и запустите запрос для чтения данных из зашифрованных столбцов. Запрос должен возвращать зашифрованные данные в виде двоичных массивов.

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

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

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