Руководство. Начало работы с 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" -MinimumVersion 22.0.50

# Connect to your database
# Set the valid server name, database name and authentication keywords in the connection string
$serverName = "<server name>.database.windows.net"
$databaseName = "ContosoHR"
$connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
	throw
}


$query = @'
 IF SCHEMA_ID(N'HR') IS NULL
BEGIN
    EXEC(N'CREATE SCHEMA [HR]');
END;

IF OBJECT_ID(N'HR.Employees', N'U') IS NULL
BEGIN
    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,
        CONSTRAINT [PK_HR_Employees] PRIMARY KEY CLUSTERED ([EmployeeID])
    );
END;
'@

#write-host @query

Invoke-SqlCmd -ConnectionString $connStr -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 $connStr -Query $query

Write-Host 'Completed successfully.'

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

1. В обозреватель объектов разверните Databases>ContosoHR>Tables.

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. Оставьте значение по умолчанию Current User — это укажет мастеру создать сертификат (ваш новый главный ключ столбца) в хранилище Current User.

        

     3. Выберите Далее.

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

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

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

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

   

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

    1. Разверните ContosoHR>Security>Ключи Always Encrypted, чтобы изучить объекты метаданных для главного ключа столбца и шифрования столбца, созданные мастером.

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

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

    3. В обозреватель объектов щелкните правой кнопкой мыши таблицу Employees и выберите Script Table as>CREATE To>New Редактор запросов Window. Откроется новое окно запроса с CREATE TABLE инструкцией для таблицы Employees . Обратите внимание, что в определениях столбцов SSN и Зарплата присутствует выражение ENCRYPTED WITH.

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

     [CmdletBinding()]
       param(
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$SubscriptionId,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$ResourceGroupName,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$KeyVaultName,
     
           [Parameter(Mandatory = $true)]
           [ValidateNotNullOrEmpty()]
           [string]$KeyVaultKeyName,
     
           [Parameter(Mandatory = $false)]
           [ValidateSet('RSA', 'RSA-HSM', 'EC', 'EC-HSM', 'oct')]
           [string]$KeyType = 'RSA',
     
           [Parameter(Mandatory = $false)]
           [ValidateSet('Software', 'HSM')]
           [string]$Destination = 'Software'
       )
     
       Set-StrictMode -Version Latest
       $ErrorActionPreference = 'Stop'
     
       Import-Module Az.Accounts -ErrorAction Stop
       Import-Module Az.KeyVault -ErrorAction Stop
       Import-Module Az.Resources -ErrorAction Stop
     
       Write-Host "[AKV] Signing in to Azure"
       Connect-AzAccount -ErrorAction Stop | Out-Null
     
       Write-Host "[AKV] Setting subscription context to '$SubscriptionId'"
       Set-AzContext -SubscriptionId $SubscriptionId -ErrorAction Stop | Out-Null
     
       Write-Host "[AKV] Validating Key Vault '$KeyVaultName' in resource group '$ResourceGroupName'"
       $vault = Get-AzKeyVault -VaultName $KeyVaultName -ResourceGroupName $ResourceGroupName -ErrorAction Stop
     
       Write-Host "[AKV] Checking if key '$KeyVaultKeyName' already exists"
       $existingKey = Get-AzKeyVaultKey -VaultName $KeyVaultName -Name $KeyVaultKeyName -ErrorAction SilentlyContinue
     
       if ($existingKey) {
           Write-Host "[AKV] Key already exists. Returning existing key metadata."
           $existingKey
           return
       }
     
       Write-Host "[AKV] Creating key '$KeyVaultKeyName' (Type=$KeyType, Destination=$Destination)"
       $keyVaultKey = Add-AzKeyVaultKey `
           -VaultName $KeyVaultName `
           -Name $KeyVaultKeyName `
           -Destination $Destination `
           -KeyType $KeyType `
           -ErrorAction Stop
     
       Write-Host '[AKV] Key created successfully.'
       $keyVaultKey
     

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

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

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

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

     Write-Host "[AE] Connecting to Azure SQL and creating metadata"
     $keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl "https://vault.azure.net").Token
     
     $serverName = "<server name>.database.windows.net"
     $databaseName = "ContosoHR"
     $connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
     
     try {
         $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
     }
     catch {
         Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
         throw
     }
     
     $cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyURL $keyVaultKey.Key.Kid
     
     $existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
     if (-not $existingCmk) {
         New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
     }
     

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

     Write-Host "[AE] Connecting to Azure SQL and creating metadata"
     $serverName = "<server name>.database.windows.net"
     $databaseName = "ContosoHR"
     $connStr = "Server=tcp:$serverName,1433;Database=$databaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
     
     try {
         $database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory -ErrorAction Stop
     }
     catch {
         Write-Error "Failed to connect. Verify server name, database name, Azure SQL firewall access, and Microsoft Entra permissions."
         throw
     }
     
     $cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint
     
     $existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
     if (-not $existingCmk) {
         New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
     }
     

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

   $existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
    if (-not $existingCek) {
    	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName -KeyVaultAccessToken $keyVaultAccessToken | Out-Null
    }
   

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

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

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

     $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