Windows 証明書ストアを使用して Always Encrypted を構成する

適用対象: Azure SQL Database

この記事では、SQL Server Management Studio (SSMS)Always Encrypted ウィザードを使用して、Azure SQL Database または Azure SQL Managed Instance 内のデータベースにある機密データをデータベースの暗号化により保護する方法について説明します。 さらに、Windows 証明書ストアにキーを格納する方法も説明します。

Always Encrypted はデータ暗号化テクノロジです。サーバーでの保存時、クライアントとサーバー間の移動中、およびデータの使用中も機密データを保護することができるため、データベース システム内で機密データがプレーンテキストとして表示されることはありません。 データの暗号化後に、プレーンテキスト データにアクセスできるのは、キーへのアクセス権を持つクライアント アプリケーションとアプリケーション サーバーだけです。 詳細については、 Always Encrypted (データベース エンジン) に関するページを参照してください。

Always Encrypted を使用するようデータベースを構成したら、Visual Studio を使って、暗号化されたデータを扱う C# クライアント アプリケーションを作成します。

この記事の手順に従って、Azure SQL Database または SQL Managed Instance に Always Encrypted を設定する方法を学習します。 この記事では、次のタスクを実行する方法を説明します。

前提条件

このチュートリアルには次のものが必要です。

  • Azure アカウントとサブスクリプション。 お持ちでない場合は、 無料試用版にサインアップしてください。

クライアント アプリケーションへのアクセスを有効にする

Azure Active Directory (AAD) アプリケーションを設定し、アプリケーションを認証するために必要な "アプリケーション ID" と "キー" をコピーして、クライアント アプリケーションから SQL Database または SQL Managed Instance にアクセスできるようにする必要があります。

"アプリケーション ID" と "キー" を取得するには、リソースにアクセスできる Azure Active Directory アプリケーションとサービス プリンシパルの作成に関するページの手順に従ってください。

SSMS との接続

SQL Server Management Studio (SSMS) を開き、データベースがあるサーバーまたはマネージドに接続します。

  1. SSMS を開きます。 ( [サーバーへの接続] ウィンドウを開いていない場合は、 [接続] > [データベース エンジン] の順にクリックして開きます)。

  2. サーバー名と資格情報を入力します。

    接続文字列のコピー

[新しいファイアウォール規則] ウィンドウが表示された場合は、Azure にサインインして、SSMS で自動的に新しいファイアウォール規則を作成します。

テーブルを作成する

このセクションでは、患者データを保持するテーブルを作成します。 これは最初は通常のテーブルで、次のセクションで暗号化を構成します。

  1. [データベース] を展開します。

  2. Clinic データベースを右クリックして、 [新しいクエリ] をクリックします。

  3. [新しいクエリ] ウィンドウに次の Transact-SQL (T-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 に用意されているウィザードを使用すると、CMK、CEK、および暗号化する列を設定するだけで簡単に Always Encrypted を構成できます。

  1. [データベース] > 空の > [テーブル] を使用して、SQL データベース内の機密データを保護する方法について説明します。

  2. Patients テーブルを右クリックして [列の暗号化] を選択すると、Always Encrypted ウィザードが起動します。

    Patients テーブルの [列の暗号化] メニュー オプションのスクリーンショット。

Always Encrypted ウィザードには、 [列の選択][マスター キー構成] (CMK)、 [検証] 、および [まとめ] のセクションがあります。

列の選択

[説明] ページの [次へ] をクリックして、 [列の選択] ページを開きます。 このページで、暗号化する列、 暗号化の種類、使用する列暗号化キー (CEK) を選択します。

各患者の SSNBirthDate 情報を暗号化します。 SSN 列では決定論的な暗号化を使用します。この場合、等値のルックアップ、結合、グループ化を実行できます。 BirthDate 列ではランダム化された暗号化を使用します。この場合、操作は実行できません。

[暗号化の種類] として、SSN 列には [決定論的] を、BirthDate 列には [ランダム化] を選択します。 [次へ] をクリックします。

[列の暗号化]

マスター キー構成

[マスター キーの構成] ページでは、CMK を設定し、その CMK を格納するキー ストア プロバイダーを選択します。 現時点では、Windows 証明書ストア、Azure Key Vault、またはハードウェア セキュリティ モジュール (HSM) に格納できます。 このチュートリアルでは、Windows 証明書ストアにキーを格納する方法を説明します。

[Windows 証明書ストア] が選択されていることを確認し、 [次へ] をクリックします。

マスター キー構成

検証

列の暗号化はすぐに実行することも、PowerShell スクリプトを保存して後から実行することもできます。 このチュートリアルでは、 [今すぐ続行して完了] を選択して [次へ] をクリックします。

まとめ

設定がすべて正しいことを確認し、 [完了] をクリックすれば、Always Encrypted の設定は完了です。

タスクが成功としてマークされている [結果] ページを示すスクリーンショット。

ウィザードのアクションの確認

ウィザードが完了すると、データベースに Always Encrypted が設定されています。 ウィザードでは、次の操作が実行されました。

  • CMK が作成されました。
  • CEK が作成されました。
  • 選択した列の暗号化の構成 Patients テーブルにはまだデータがありませんが、選択した列にデータが存在していれば、この段階で暗号化されています。

SSMS でキーが生成されていることを確認するには、 [Clinic] > [セキュリティ] > [Always Encrypted キー] の順に進みます。 ウィザードで生成された新しいキーを確認できます。

暗号化されたデータを扱うクライアント アプリケーションを作成する

Always Encrypted を設定したので、暗号化された列に対して、insertsselects を実行するアプリケーションを構築できます。 サンプル アプリケーションを正常に実行するには、Always Encrypted ウィザードを実行したコンピューター上でアプリケーションを実行する必要があります。 別のコンピューター上でアプリケーションを実行する場合は、クライアント アプリケーションを実行するコンピューターに Always Encrypted 証明書をデプロイする必要があります。

重要

Always Encrypted 列を構成したサーバーにプレーンテキスト データを渡す場合は、 SqlParameter オブジェクトを使用する必要があります。 SqlParameter オブジェクトを使用せずにリテラル値を渡すと、例外が発生します。

  1. Visual Studio を開き、新しい C# コンソール アプリケーションを作成します。 プロジェクトは必ず .NET Framework 4.6 以降に設定してください。
  2. プロジェクトに AlwaysEncryptedConsoleApp という名前を付けて、 [OK] をクリックします。

AlwaysEncryptedConsoleApp という名前が新しく付けられたプロジェクトのスクリーンショット。

接続文字列を変更して Always Encrypted を有効にする

このセクションでは、データベース接続文字列で Always Encrypted を有効にする方法を説明します。 次のセクション「Always Encrypted サンプル コンソール アプリケーション」で、作成したコンソール アプリケーションを変更します。

Always Encrypted を有効にするには、接続文字列に Column Encryption Setting キーワードを追加し、Enabled に設定します。

接続文字列に直接設定することも、 SqlConnectionStringBuilderを使用して設定することもできます。 SqlConnectionStringBuilder を使用する方法については、次のセクションでサンプル アプリケーションを使って説明します。

注意

Always Encrypted を有効にするためにクライアント アプリケーションで必要な変更はこれだけです。 既存のアプリケーションで接続文字列を外部の構成ファイルなどに格納している場合は、コードを変更しなくても Always Encrypted を有効にできる場合があります。

接続文字列で Always Encrypted を有効にする

接続文字列に次のキーワードを追加します。

Column Encryption Setting=Enabled

SqlConnectionStringBuilder を使って Always Encrypted を有効にする

次のコードは、SqlConnectionStringBuilder.ColumnEncryptionSettingEnabled に設定して Always Encrypted を有効にする方法を示しています。

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

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

Always Encrypted サンプル コンソール アプリケーション

このサンプルでは次の操作を行います。

  • 接続文字列を変更して Always Encrypted を有効にする。
  • 暗号化された列にデータを挿入する。
  • 暗号化された列をフィルター処理して、特定の値を持つレコードを選択する。

Program.cs のコンテンツを次のコードに置き換えます。 Main メソッドのすぐ上の行にある connectionString のグローバル変数の接続文字列を、Azure ポータルから取得した有効な接続文字列に置き換えます。 コードに対する変更はこれだけです。

アプリケーションを実行して、Always Encrypted の動作を見てみましょう。

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;

namespace AlwaysEncryptedConsoleApp
{
    class Program
    {
        // Update this line with your Clinic database connection string from the Azure portal.
        static string connectionString = @"Data Source = SPE-T640-01.sys-sqlsvr.local; Initial Catalog = Clinic; Integrated Security = true";

        static void Main(string[] args)
        {
            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...");

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


            // 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 let's 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. 123-45-6789):");
                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();
        }


        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; }
    }
}

データが暗号化されていることを確認する

サーバー上の実際のデータが暗号化されていることをすばやく確認するには、SSMS で Patients データをクエリします (Column Encryption Setting がまだ有効にされていない現在の接続を使用します)。

Clinic データベースで次のクエリを実行します。

SELECT FirstName, LastName, SSN, BirthDate FROM Patients;

暗号化された列にプレーンテキスト データが含まれていないことがわかります。

暗号化された列の暗号化されたデータのスクリーンショット。

SSMS を使用してプレーンテキスト データにアクセスするには、接続に Column Encryption Setting=enabled パラメーターを追加します。

  1. SSMS の オブジェクト エクスプローラー でサーバーを右クリックし、 [切断] をクリックします。

  2. [接続] > [データベース エンジン] の順にクリックして [サーバーへの接続] ウィンドウを開き、 [オプション] をクリックします。

  3. [追加の接続パラメーター] をクリックし、「Column Encryption Setting=enabled」と入力します。

    [追加の接続パラメーター] タブのスクリーンショット。ボックスに「Column Encryption Setting=enabled」と入力されています。

  4. Clinic データベースで次のクエリを実行します。

    SELECT FirstName, LastName, SSN, BirthDate FROM Patients;
    

    暗号化された列のプレーンテキスト データを確認できます。

    新しいコンソール アプリケーション

注意

別のコンピューターの SSMS (または任意のクライアント) から接続した場合は暗号化キーにアクセスできず、データの暗号化を解除することはできません。

次のステップ

Always Encrypted を使用するデータベースを作成したら、次の操作を試してみてください。