Настройка Always Encrypted с помощью Azure Key Vault

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

В этой статье показано, как защищать конфиденциальные данные в базе данных службы "База данных SQL Azure" путем шифрования данных с помощью мастера Always Encrypted в SQL Server Management Studio (SSMS). Она также содержит инструкции, с помощью которых вы узнаете, как сохранить каждый ключ шифрования в хранилище ключей Azure.

Always Encrypted — это технология шифрования данных. Она помогает защитить конфиденциальные данные, которые хранятся на сервере, перемещаются между клиентом и сервером и просто используются. Always Encrypted гарантирует, что конфиденциальные данные никогда не отобразятся как открытый текст внутри системы баз данных. После настройки шифрования данных получить доступ к данным в виде открытого текста смогут только клиентские приложения и серверы приложений, у которых есть доступ к ключам. Дополнительные сведения см. в статье Always Encrypted (ядро СУБД).

После настройки функции Always Encrypted для базы данных вы создадите клиентское приложение на языке C# для работы с зашифрованными данными, используя Visual Studio.

Выполнив шаги, приведенные в этой статье, вы узнаете, как настроить Always Encrypted для базы данных в службе "База данных SQL Azure" или Управляемом экземпляре SQL. Здесь вы научитесь:

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

Включение доступа клиентского приложения

Необходимо включить доступ клиентского приложения к базе данных службы "База данных SQL". Для этого нужно настроить приложение Azure Active Directory (Azure AD) и скопировать идентификатор приложения и ключ, которые понадобятся для аутентификации приложения.

Чтобы получить идентификатор приложения и ключ, ознакомьтесь с процедурой в разделе Создание приложения Azure Active Directory и субъекта-службы с доступом к ресурсам с помощью портала.

Создание хранилища ключей для хранения ваших ключей

Теперь, когда клиентское приложение настроено и у вас есть идентификатор приложения, пора создать хранилище ключей и настроить его политику доступа таким образом, чтобы ваше приложение могло обращаться к секретам в хранилище (ключам Always Encrypted). Для создания главного ключа столбца и настройки шифрования с помощью SQL Server Management Studio необходимы разрешения create, get, list, sign, verify, wrapKey и unwrapKey.

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

Важно!

Модуль PowerShell Azure Resource Manager по-прежнему поддерживается базой данных SQL Azure, но вся будущая разработка сосредоточена на модуле Az.Sql. Исправления ошибок для модуля AzureRM будут продолжать выпускаться как минимум до декабря 2020 г. Аргументы команд в модулях Az и AzureRm практически идентичны. Дополнительные сведения о совместимости см. в статье Знакомство с новым модулем Az для Azure PowerShell.

$subscriptionName = '<subscriptionName>'
$userPrincipalName = '<username@domain.com>'
$applicationId = '<applicationId from AAD application>'
$resourceGroupName = '<resourceGroupName>' # use the same resource group name when creating your SQL Database below
$location = '<datacenterLocation>'
$vaultName = '<vaultName>'

Connect-AzAccount
$subscriptionId = (Get-AzSubscription -SubscriptionName $subscriptionName).Id
Set-AzContext -SubscriptionId $subscriptionId

New-AzResourceGroup -Name $resourceGroupName -Location $location
New-AzKeyVault -VaultName $vaultName -ResourceGroupName $resourceGroupName -Location $location

Set-AzKeyVaultAccessPolicy -VaultName $vaultName -ResourceGroupName $resourceGroupName -PermissionsToKeys create,get,wrapKey,unwrapKey,sign,verify,list -UserPrincipalName $userPrincipalName
Set-AzKeyVaultAccessPolicy  -VaultName $vaultName  -ResourceGroupName $resourceGroupName -ServicePrincipalName $applicationId -PermissionsToKeys get,wrapKey,unwrapKey,sign,verify,list

Подключение к SSMS

Откройте SQL Server Management Studio (SSMS) и подключите базу данных к серверу или управляемому экземпляру.

  1. Откройте SSMS. (Щелкните Подключиться>Ядро СУБД, чтобы открыть окно Соединение с сервером, если оно еще не открыто.)

  2. Введите имя сервера или имя экземпляра и учетные данные.

    Копирование строки подключения

Если откроется окно Новое правило брандмауэра , войдите в Azure, и SSMS создаст новое правило брандмауэра для вас.

Создание таблицы

В этом разделе вы создадите таблицу для хранения данных пациентов. Изначально в ней будет отсутствовать шифрование — его вы настроите в следующем разделе.

  1. Разверните узел Базы данных.
  2. Щелкните правой кнопкой мыши базу данных и выберите пункт Создать запрос.
  3. Вставьте следующий сценарий Transact-SQL в окно нового запроса и нажмите кнопку Выполнить .
CREATE TABLE [dbo].[Patients](
         [PatientId] [int] IDENTITY(1,1),
         [SSN] [char](11) NOT NULL,
         [FirstName] [nvarchar](50) NULL,
         [LastName] [nvarchar](50) NULL,
         [MiddleName] [nvarchar](50) NULL,
         [StreetAddress] [nvarchar](50) NULL,
         [City] [nvarchar](50) NULL,
         [ZipCode] [char](5) NULL,
         [State] [char](2) NULL,
         [BirthDate] [date] NOT NULL
         PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] );
GO

Шифрование столбцов (настройка Always Encrypted)

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

  1. Разверните узел Базы данных>Clinic>Таблицы.

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

    Снимок экрана: выделен пункт меню

Мастер настройки Always Encrypted содержит следующие разделы: Выбор столбца, Настройка главного ключа, Проверка и Сводка.

Выполните действия на странице Выбор столбцов.

На странице Введение нажмите кнопку Далее, чтобы открыть страницу Выбор столбца. На этой странице можно будет выбрать столбцы для шифрования, тип шифрования и используемый ключ шифрования столбца (CEK) .

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

Задайте для параметра Тип шифрования столбца SSN значение Детерминированное, а для столбца BirthDate — Случайное. Щелкните Далее.

Шифрование столбцов…

Настройка главного ключа

На странице Настройка главного ключа можно настроить CMK и выбрать поставщик хранилища ключей, в котором будет находиться CMK. В настоящее время CMK можно хранить в хранилище сертификатов Windows, хранилище ключей Azure или аппаратном модуле безопасности (HSM).

В этом руководстве показано, как сохранить ключи в хранилище ключей Azure.

  1. Выберите Хранилище ключей Azure.
  2. Из раскрывающегося списка выберите нужное хранилище ключей.
  3. Щелкните Далее.

Настройка главного ключа

Проверка

Можно зашифровать столбцы сейчас или сохранить сценарий PowerShell и выполнить его позже. Для целей этого руководства выберите Перейти к завершению и нажмите кнопку Далее.

Сводка

Убедитесь, что все параметры настроены правильно, и нажмите кнопку Готово , чтобы завершить настройку Always Encrypted.

Снимок экрана: страница результатов с задачами, помеченными как выполненные.

Проверка действий мастера

После завершения работы мастера для базы данных будет настроена функция Always Encrypted. Мастер выполнил следующие действия:

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

Чтобы убедиться, что ключи в SSMS созданы, разверните узлы Clinic>Безопасность>Ключи Always Encrypted.

Создание клиентского приложения, которое работает с зашифрованными данными

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

Важно!

При передаче открытого текста на сервер со столбцами с постоянным шифрованием приложение должно использовать объекты SqlParameter . Передача значений литералов без использования объектов SqlParameter приведет к возникновению исключения.

  1. Откройте Visual Studio и создайте консольное приложение C# (Visual Studio 2015 и более ранней версии) или консольное приложение (.NET Framework) (Visual Studio 2017 и более поздней версии). Убедитесь, что для проекта используется платформа .NET Framework версии 4.6 или более поздней.
  2. Укажите имя проекта AlwaysEncryptedConsoleApp и нажмите кнопку ОК.
  3. Установите следующие пакеты NuGet, выбрав Сервис>Диспетчер пакетов NuGet>Консоль диспетчера пакетов.

В консоли диспетчера пакетов выполните следующие две строки кода:

Install-Package Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider
Install-Package Microsoft.IdentityModel.Clients.ActiveDirectory

Изменение строки подключения для активации функции постоянного шифрования

В этом разделе объясняется, как включить функцию Always Encrypted в строке подключения к базе данных.

Чтобы включить функцию Always Encrypted, необходимо добавить ключевое слово Column Encryption Setting в строку подключения и задать для него значение Enabled.

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

Активация функции постоянного шифрования в строке подключения

Добавьте в строку подключения следующее ключевое слово.

Column Encryption Setting=Enabled

Включение функции Always Encrypted с помощью SqlConnectionStringBuilder

В коде ниже показано, как активировать функцию Always Encrypted, указав параметр SqlConnectionStringBuilder.ColumnEncryptionSetting со значением Enabled.

// Instantiate a SqlConnectionStringBuilder.
SqlConnectionStringBuilder connStringBuilder = new SqlConnectionStringBuilder("replace with your connection string");

// Enable Always Encrypted.
connStringBuilder.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

Регистрация поставщика хранилища ключей Azure

В следующем коде показано, как зарегистрировать поставщик хранилища ключей Azure с помощью драйвера ADO.NET.

private static ClientCredential _clientCredential;

static void InitializeAzureKeyVaultProvider() {
    _clientCredential = new ClientCredential(applicationId, clientKey);

    SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

    Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

    providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
    SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
}

Пример консольного приложения с функцией постоянного шифрования

В этом примере показано, как:

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

Замените содержимое Program.cs приведенным ниже кодом. Замените строку подключения для глобальной переменной connectionString, расположенную в строке прямо над методом Main, действительной строкой подключения с портала Azure. Это единственное изменение, которое необходимо внести в этот код.

Запустите приложение, чтобы увидеть функцию Always Encrypted в действии.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.SqlServer.Management.AlwaysEncrypted.AzureKeyVaultProvider;

namespace AlwaysEncryptedConsoleAKVApp {
    class Program {
        // Update this line with your Clinic database connection string from the Azure portal.
        static string connectionString = @"<connection string from the portal>";
        static string applicationId = @"<application ID from your AAD application>";
        static string clientKey = "<key from your AAD application>";

        static void Main(string[] args) {
            InitializeAzureKeyVaultProvider();

            Console.WriteLine("Signed in as: " + _clientCredential.ClientId);

            Console.WriteLine("Original connection string copied from the Azure portal:");
            Console.WriteLine(connectionString);

            // Create a SqlConnectionStringBuilder.
            SqlConnectionStringBuilder connStringBuilder =
                new SqlConnectionStringBuilder(connectionString);

            // Enable Always Encrypted for the connection.
            // This is the only change specific to Always Encrypted
            connStringBuilder.ColumnEncryptionSetting =
                SqlConnectionColumnEncryptionSetting.Enabled;

            Console.WriteLine(Environment.NewLine + "Updated connection string with Always Encrypted enabled:");
            Console.WriteLine(connStringBuilder.ConnectionString);

            // Update the connection string with a password supplied at runtime.
            Console.WriteLine(Environment.NewLine + "Enter server password:");
            connStringBuilder.Password = Console.ReadLine();

            // Assign the updated connection string to our global variable.
            connectionString = connStringBuilder.ConnectionString;

            // Delete all records to restart this demo app.
            ResetPatientsTable();

            // Add sample data to the Patients table.
            Console.Write(Environment.NewLine + "Adding sample patient data to the database...");

            InsertPatient(new Patient() {
                SSN = "999-99-0001",
                FirstName = "Orlando",
                LastName = "Gee",
                BirthDate = DateTime.Parse("01/04/1964")
            });
            InsertPatient(new Patient() {
                SSN = "999-99-0002",
                FirstName = "Keith",
                LastName = "Harris",
                BirthDate = DateTime.Parse("06/20/1977")
            });
            InsertPatient(new Patient() {
                SSN = "999-99-0003",
                FirstName = "Donna",
                LastName = "Carreras",
                BirthDate = DateTime.Parse("02/09/1973")
            });
            InsertPatient(new Patient() {
                SSN = "999-99-0004",
                FirstName = "Janet",
                LastName = "Gates",
                BirthDate = DateTime.Parse("08/31/1985")
            });
            InsertPatient(new Patient() {
                SSN = "999-99-0005",
                FirstName = "Lucy",
                LastName = "Harrington",
                BirthDate = DateTime.Parse("05/06/1993")
            });

            // Fetch and display all patients.
            Console.WriteLine(Environment.NewLine + "All the records currently in the Patients table:");

            foreach (Patient patient in SelectAllPatients()) {
                Console.WriteLine(patient.FirstName + " " + patient.LastName + "\tSSN: " + patient.SSN + "\tBirthdate: " + patient.BirthDate);
            }

            // Get patients by SSN.
            Console.WriteLine(Environment.NewLine + "Now lets locate records by searching the encrypted SSN column.");

            string ssn;

            // This very simple validation only checks that the user entered 11 characters.
            // In production be sure to check all user input and use the best validation for your specific application.
            do {
                Console.WriteLine("Please enter a valid SSN (ex. 999-99-0003):");
                ssn = Console.ReadLine();
            } while (ssn.Length != 11);

            // The example allows duplicate SSN entries so we will return all records
            // that match the provided value and store the results in selectedPatients.
            Patient selectedPatient = SelectPatientBySSN(ssn);

            // Check if any records were returned and display our query results.
            if (selectedPatient != null) {
                Console.WriteLine("Patient found with SSN = " + ssn);
                Console.WriteLine(selectedPatient.FirstName + " " + selectedPatient.LastName + "\tSSN: "
                    + selectedPatient.SSN + "\tBirthdate: " + selectedPatient.BirthDate);
            }
            else {
                Console.WriteLine("No patients found with SSN = " + ssn);
            }

            Console.WriteLine("Press Enter to exit...");
            Console.ReadLine();
        }

        private static ClientCredential _clientCredential;

        static void InitializeAzureKeyVaultProvider() {
            _clientCredential = new ClientCredential(applicationId, clientKey);

            SqlColumnEncryptionAzureKeyVaultProvider azureKeyVaultProvider =
              new SqlColumnEncryptionAzureKeyVaultProvider(GetToken);

            Dictionary<string, SqlColumnEncryptionKeyStoreProvider> providers =
              new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();

            providers.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
            SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);
        }

        public async static Task<string> GetToken(string authority, string resource, string scope) {
            var authContext = new AuthenticationContext(authority);
            AuthenticationResult result = await authContext.AcquireTokenAsync(resource, _clientCredential);

            if (result == null)
                throw new InvalidOperationException("Failed to obtain the access token");
            return result.AccessToken;
        }

        static int InsertPatient(Patient newPatient) {
            int returnValue = 0;

            string sqlCmdText = @"INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate])
     VALUES (@SSN, @FirstName, @LastName, @BirthDate);";

            SqlCommand sqlCmd = new SqlCommand(sqlCmdText);

            SqlParameter paramSSN = new SqlParameter(@"@SSN", newPatient.SSN);
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Size = 11;

            SqlParameter paramFirstName = new SqlParameter(@"@FirstName", newPatient.FirstName);
            paramFirstName.DbType = DbType.String;
            paramFirstName.Direction = ParameterDirection.Input;

            SqlParameter paramLastName = new SqlParameter(@"@LastName", newPatient.LastName);
            paramLastName.DbType = DbType.String;
            paramLastName.Direction = ParameterDirection.Input;

            SqlParameter paramBirthDate = new SqlParameter(@"@BirthDate", newPatient.BirthDate);
            paramBirthDate.SqlDbType = SqlDbType.Date;
            paramBirthDate.Direction = ParameterDirection.Input;

            sqlCmd.Parameters.Add(paramSSN);
            sqlCmd.Parameters.Add(paramFirstName);
            sqlCmd.Parameters.Add(paramLastName);
            sqlCmd.Parameters.Add(paramBirthDate);

            using (sqlCmd.Connection = new SqlConnection(connectionString)) {
                try {
                    sqlCmd.Connection.Open();
                    sqlCmd.ExecuteNonQuery();
                }
                catch (Exception ex) {
                    returnValue = 1;
                    Console.WriteLine("The following error was encountered: ");
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(Environment.NewLine + "Press Enter key to exit");
                    Console.ReadLine();
                    Environment.Exit(0);
                }
            }
            return returnValue;
        }


        static List<Patient> SelectAllPatients() {
            List<Patient> patients = new List<Patient>();

            SqlCommand sqlCmd = new SqlCommand(
              "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients]",
                new SqlConnection(connectionString));

            using (sqlCmd.Connection = new SqlConnection(connectionString))

            using (sqlCmd.Connection = new SqlConnection(connectionString)) {
                try {
                    sqlCmd.Connection.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    if (reader.HasRows) {
                        while (reader.Read()) {
                            patients.Add(new Patient() {
                                SSN = reader[0].ToString(),
                                FirstName = reader[1].ToString(),
                                LastName = reader["LastName"].ToString(),
                                BirthDate = (DateTime)reader["BirthDate"]
                            });
                        }
                    }
                }
                catch (Exception ex) {
                    throw;
                }
            }

            return patients;
        }

        static Patient SelectPatientBySSN(string ssn) {
            Patient patient = new Patient();

            SqlCommand sqlCmd = new SqlCommand(
                "SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE [SSN]=@SSN",
                new SqlConnection(connectionString));

            SqlParameter paramSSN = new SqlParameter(@"@SSN", ssn);
            paramSSN.DbType = DbType.AnsiStringFixedLength;
            paramSSN.Direction = ParameterDirection.Input;
            paramSSN.Size = 11;

            sqlCmd.Parameters.Add(paramSSN);

            using (sqlCmd.Connection = new SqlConnection(connectionString)) {
                try {
                    sqlCmd.Connection.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();

                    if (reader.HasRows) {
                        while (reader.Read()) {
                            patient = new Patient() {
                                SSN = reader[0].ToString(),
                                FirstName = reader[1].ToString(),
                                LastName = reader["LastName"].ToString(),
                                BirthDate = (DateTime)reader["BirthDate"]
                            };
                        }
                    }
                    else {
                        patient = null;
                    }
                }
                catch (Exception ex) {
                    throw;
                }
            }
            return patient;
        }

        // This method simply deletes all records in the Patients table to reset our demo.
        static int ResetPatientsTable() {
            int returnValue = 0;

            SqlCommand sqlCmd = new SqlCommand("DELETE FROM Patients");
            using (sqlCmd.Connection = new SqlConnection(connectionString)) {
                try {
                    sqlCmd.Connection.Open();
                    sqlCmd.ExecuteNonQuery();

                }
                catch (Exception ex) {
                    returnValue = 1;
                }
            }
            return returnValue;
        }
    }

    class Patient {
        public string SSN { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public DateTime BirthDate { get; set; }
    }
}

Как проверить, что данные шифруются

Вы можете быстро проверить, действительно ли данные на сервере зашифрованы. Для этого можно запросить данные таблицы Patients с помощью SSMS (используя текущее подключение, где не задан параметр Column Encryption Setting).

Выполните следующий запрос к базе данных Clinic.

SELECT FirstName, LastName, SSN, BirthDate FROM Patients;

Вы увидите, что в зашифрованных столбцах не содержатся данные в виде открытого текста.

Снимок экрана: в зашифрованных столбцах не содержатся данные в виде открытого текста.

Чтобы получить доступ к данным в виде открытого текста, сначала убедитесь, что пользователь имеет необходимые разрешения в Azure Key Vault: get, unwrapKey и verify. Дополнительные сведения см. в статье Создание и хранение главных ключей столбцов (постоянное шифрование).

Затем во время подключения добавьте параметр Column Encryption Setting=enabled.

  1. В обозревателе объектов SSMS щелкните сервер правой кнопкой мыши и выберите пункт Отключить.

  2. Щелкните Подключиться>Ядро СУБД, чтобы открыть окно Соединение с сервером, и нажмите кнопку Параметры.

  3. Щелкните Дополнительные параметры соединения и введите Column Encryption Setting=enabled.

    Снимок экрана: вкладка дополнительных параметров исправления.

  4. Выполните следующий запрос к базе данных Clinic.

    SELECT FirstName, LastName, SSN, BirthDate FROM Patients;
    

    Теперь в зашифрованных столбцах отображаются незашифрованные данные.

    Новое консольное приложение

Дальнейшие действия

После настройки базы данных для использования Always Encrypted может потребоваться выполнить следующие действия: