【５分で流し読み】SQL Server / Azure SQL DatabaseのAlways Encrypted機能でできることを理解する

こんにちは。今日はSQL ServerのAlways Encrypted機能について勉強しましたので、分かったことなぞ簡単にまとめておきたいと思います。

Always Encryptedとは？

データの暗号化と復号をクライアント側で実施することでデータのセキュリティを高めることができる（データ管理者でもデータの中身を閲覧できないようにすることができる）機能と位置付けられています。機密データを含むテーブルの各列に対して構成することができます。

Always Encrypted を使用すると、クライアントは データベース エンジン (SQL Database または SQL Server) に暗号化キーを開示することなく、クライアント アプリケーション内の機微なデータを暗号化することができます。 結果として、Always Encrypted により、データを所有していて見ることができるユーザーと、データを管理するがアクセスできてはならないユーザーが分離されます。

Always Encrypted – SQL Server | Microsoft Docs

このデータ暗号化と復号は、Webアプリケーションの場合、データベース接続ドライバで行われます。ので、Always Encryptedに対応しているドライバを利用する必要があります。

Always Encrypted は、アプリケーションに対して暗号化を透過的に実行します。 クライアント コンピューターにインストールされている、Always Encrypted が有効のドライバーは、クライアント アプリケーション内の機微なデータを自動的に暗号化および暗号化解除することで、この処理を実行します。 ドライバーは、 データベース エンジンにデータを渡す前に機微な列のデータを暗号化し、アプリケーションに対するセマンティクスが維持されるように自動的にクエリを書き換えます。 また、暗号化されたデータベース列に格納され、クエリ結果に含まれているデータを、同じように透過的に暗号化解除します。

Always Encrypted – SQL Server | Microsoft Docs

Always Encryptedを利用するとセキュリティが高められる一方、データベースからもデータが暗号化された状態でしかみえなくしてしまうため、いくつかの操作の制約が発生します。

暗号化および復号化は、クライアント ドライバーを介して実行されます。 つまり、Always Encrypted を使用する場合、サーバー側でのみ発生する一部のアクションは動作しないことを意味します。 このようなアクションには以下のものが含まれます (ただし、これらだけではありません)。

・UPDATE、BULK INSERT (T-SQL)、SELECT INTO、INSERT..SELECT を使用して、ある列から別の列にデータをコピーすること。

・トリガー、テンポラル テーブル、スパース列、フルテキスト、インメモリ OLTP、および変更データ キャプチャ (CDC)。

Always Encrypted – SQL Server | Microsoft Docs

なお、Always Encryptedによる暗号化には２つの方法があります。

• ランダム化された暗号化
• 明確な暗号化

それぞれのメリット/デメリットは以下に解説されています。明確な暗号化を利用すると、特定の文字列に対する暗号化の結果は常に同じになるため、値が推測されやすくなるリスクがある一方、暗号化された列に対するクエリ（条件指定かな？）や、グループ化、インデックス作成、結合などの処理が行えます。

Always Encrypted – SQL Server | Microsoft Docs

暗号化・複合化に利用するキーはWindows証明書ストアか、Azure Key Vaultに格納しておくことができます。

Always Encrypted では、列暗号化キーと列マスター キーという 2 種類のキーを使用します。 列暗号化キーは、暗号化された列のデータを暗号化するために使用されます。 列マスター キーは、1 つ以上の列暗号化キーを暗号化するキー保護キーです。

Always Encrypted – SQL Server | Microsoft Docs

以下にも登場の背景など詳しく書かれているので参考になるかと思います。

セキュリティの概要 – Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs

Always Encrypted登場！(前編) (1/3)|EnterpriseZine（エンタープライズジン）

最後に、SQL Server 2019以降、またはAzure SQL Databaseでは、「セキュアエンクレーブを使用するAlways Encrypted」なる機能が追加され、Always Encryptedの機能が拡張（上記制約が軽減）されているようです。

セキュア エンクレーブを使用する Always Encrypted では、インプレース暗号化と豊富な機密クエリを有効にすることで、Always Encrypted の機密コンピューティング機能が拡張されます。 セキュリティで保護されたエンクレーブを使用した Always Encrypted は、SQL Server 2019 (15.x) 以降と Azure SQL Database で使用できます。

セキュア エンクレーブを使用する Always Encrypted – SQL Server | Microsoft Docs

セキュアエンクレーブってなんだ？上のDocsによると、「データベース エンジン プロセス内のメモリの保護された領域であり、データベース エンジンの他の部分や、ホスティング マシン上の他のプロセスからは、不透明なボックスとして認識される」とある。エンクレーブは「飛び地」という意味だけど、そういうことか。

以下のように、外部からは完全にブラックボックスのセキュアエンクレーブ内で一時的に復号を行い、その中で従来のAlways Encryptedでは制限されていた高度なクエリ処理ができるようになっているようです。

なお、このセキュアエンクレーブは、WindowsのVBSというしくみを使って実現しているようです。

仮想化ベースのセキュリティ (VBS) | Microsoft Docs

セキュアエンクレーブを使用したAlways Encryptedの詳細は以下参照。

セキュア エンクレーブを使用する Always Encrypted – SQL Server | Microsoft Docs

Always Encryptedはどうやって構成する？

Always Encryptedはデータベース側での構成と、アプリケーション側の構成が必要になります。

データベース側の構成

Always Encryptedを実現するためのデータベース側の構成はいくつかの方法があります。（他にもあるかもしれませんが、公式Docの目次構成からパッとみつけられたのは以下２つでした）

• PowerShell（PowerShell を使用した Always Encrypted の構成 – SQL Server | Microsoft Docs）
• SSMS（SSMS を使用した Always Encrypted の構成 – SQL Server | Microsoft Docs）

今回は、雰囲気を掴むためにSSMSを利用した構成を試してみました。

まず、AlwaysEncrypted構成前の状態。（一応補足ですが、これはMicrosoftが提供するサンプルデータベースの情報で、すべてダミーデータです）

暗号化したいテーブルを右クリック＞列の暗号化からAlways Encryptedの構成を開始できます。

暗号化対象の列を指定し、暗号化の種類、暗号化キーを指定します（なければこの手順の中で作成できます）

列マスターキーを格納する場所を選べます。

即時実行で。

すると、キーの作成と暗号化が行われます。

暗号化完了した後に同じクエリをたたくと、ちゃんとデータが暗号化されたことが確認できます。

なお、暗号化された列に以下のようなクエリを投げてもエラーで返ってしまいます。

ちらっと探したところ暗号化された列へのクエリはパラメータ化クエリで行う必要があるとのことでした。こちらはまた追ってちゃんと調べてUpdateしようと思います。

SQL Server 2016 CTP 2.0 の Always Encrypted を使ってみる at SE の雑記 (engineer-memo.com)

なお、暗号化キーに関する情報は、データベースエンジン上からも確認できますが、キーそのものの平文情報は含んでいないので、万が一データベースの中を悪意あるユーザに探索されても安全、ということのようです。

Always Encrypted のキー管理の概要 – SQL Server | Microsoft Docs

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

SELECT * FROM sys.column_master_keys;

列マスタキーは、Windwos証明書ストア内に格納されていることが確認できます。

アプリケーション側の構成

Webアプリケーションの場合はここからドライバの構成が必要だと思いますが、今回はSSMSを使って暗号化されたデータを復号して表示する方法をためしてみました。

アプリの場合の参考記事は以下にはっておきます。

Always Encrypted を使用したアプリケーションの開発 – SQL Server | Microsoft Docs

さて、SSMS上で暗号化されたデータを復号してみたい場合、データベース接続時のオプションを追加する必要があります。

このオプションを指定した状態で再度データの表示を試してみると・・再度暗号化前のデータで確認できました！これは、SSMSが先ほど作成したキーを利用して透過的に暗号化と復号を行ってくれているから、ということですね。

以上、Always Encryptedについての簡単な概要のまとめでした。

少しでも参考になりましたら幸いです。

おしまい