SQL Server 2016は常に暗号化されています：実装が簡単で、解読が困難

公開: 2022-03-11

データはどの企業にとっても重要な資産であり、特に財務記録や健康記録などのビジネス上の秘密を保持するトランザクションデータです。 データは、データを格納するサーバーとデータを要求するクライアントの間で転送されるときに最も脆弱になります。

セキュリティを確保するための標準的なアプローチは、サーバー上のデータを暗号化し、SSL対応のHTTPSプロトコルを使用して転送中のデータを保護することです。 ただし、HTTPSを使用し、通信回線を介して暗号化された形式でデータを送信し、有効な証明書を持つクライアントのデータを復号化することで、セキュリティのレベルをさらに高めることができるとしたらどうでしょうか。 このアプローチでは、従来の中間者（MITM）攻撃がはるかに困難になります。

SQLServer暗号化カバー画像

この問題に対するMicrosoftの解決策は、Always Encryptedです。これは、暗号化されたデータをパイプライン経由で送信し、有効な証明書にアクセスできるユーザーのみが暗号化を解除する方法です。 したがって、攻撃者がデータを取得したとしても、クライアントマシンに適切な証明書が保存されていないと、データは役に立たなくなります。

この記事では、Always Encryptedを設定して使用する方法について説明します。SSLで保護されている場合でも、公共の通信回線を介して重要なデータを送信する場合は、この記事を読むことをお勧めします。

常に暗号化される背後にある概念

Always Encryptedは、MicrosoftがSQL Server 2016で導入したクライアント側の暗号化テクノロジです。AlwaysEncryptedは、データが書き込まれるときだけでなく、承認されたアプリケーションによって読み取られるときにも、データを自動的に暗号化します。 ディスク上のデータとログファイルをリアルタイムで暗号化するが、データをクエリする任意のアプリケーションでデータを読み取ることができる透過的データ暗号化とは異なり、Always Encryptedでは、クライアントアプリケーションがAlwaysEncrypted対応のドライバーを使用してデータベース。 このドライバーを使用することにより、アプリケーションは暗号化されたデータをデータベースに安全に転送し、暗号化キーにアクセスできるアプリケーションによってのみ後で復号化できます。 データをクエリする他のアプリケーションも暗号化された値を取得できますが、そのアプリケーションは暗号化キーがないとデータを使用できないため、データが使用できなくなります。 この暗号化アーキテクチャにより、SQLServerインスタンスは暗号化されていないバージョンのデータを認識しません。

現時点では、AlwaysEncryptedが有効なドライバーはSQLServer用の.NETFrameworkデータプロバイダーのみです。これには、クライアントコンピューターに.NETFrameworkバージョン4.6をインストールする必要があります。JDBC6.0ドライバーも必要です。 それはおそらく時間とともに変わるでしょうが、これらは2017年4月現在の公式のAlwaysEncrypted要件です。

しかし、なぜこのテクノロジーが必要なのですか？ AlwaysEncryptedを使用する理由はいくつかあります。

  • セキュリティ—データは常に安全である必要がありました。 SSLが危険にさらされているため、Always Encryptedは、トランスポートパイプライン保護の別のレイヤーでギャップを埋めます。
  • 規制のサポート—データは暗号化され、主に金融および通信業界でますます多くの業界規制によってDBAの目を奪われないようにする必要があります。 これは、クレジットカード番号、社会保障番号、名前、住所などを保護する必要がある、またはデータ所有者に厳しい罰則を科すことができると述べているPII標準（「個人を特定できる情報」）に記載されています。

常に暗号化された使用方法

Always Encryptedを使用するには、暗号化されたテーブルを格納するデータベースサーバー内で少量の準備が必要です。 準備は2段階のプロセスです。

  • 列マスターキー定義を作成します
  • 列暗号化キーを作成します

列マスターキー

では、列マスターキーとは何ですか？

SQLServer2016の列マスターキー

列マスターキーは、Windows証明書ストア（デモを証明書ストレージオプションとして使用）、サードパーティのハードウェアセキュリティモジュール（インストール、管理、および使用するためのサードパーティソリューションの総称）内に格納される証明書です。証明書）、またはAzure Key Vault（Microsoftの証明書管理用のクラウドベースのソリューション）。

データを暗号化するアプリケーションは、列マスターキーを使用して、データベーステーブルの列内のデータの暗号化を処理するさまざまな列暗号化キーを保護します。 SQL Serverの証明書ストア（ Enterprise Key Managerと呼ばれることもあります）を使用するには、SQL ServerEnterpriseEditionを使用する必要があります。

この記事では、WindowsオペレーティングシステムのMicrosoft証明書ストアに保存する自己署名証明書の使用について説明します。 これは最適な構成ではありませんが、Always Encryptedの概念を示していますが、証明書管理を個別の安全なユーザーアカウントで実行する必要がある本番環境では、このアプローチは受け入れられないことも明記する必要があります。 、別のサーバー上。

SQL Server Management Studio（SSMS）内のグラフィカルインターフェイスを使用するか、T-SQLを使用して、列マスターキー定義を作成できます。 SSMSで、AlwaysEncryptedを使用してデータベーステーブルを保護するSQLServer2016データベースインスタンスに接続します。

列マスターキーの作成と使用

次の図に示すように、オブジェクトエクスプローラーで、最初にデータベースに移動し、次にセキュリティに移動してから、AlwaysEncryptedKeysフォルダーを展開して2つのサブフォルダーを表示します。

SSMSでキーを作成します。

SSMSでキーを作成します。

新しい[列マスターキー]ダイアログボックスを開きます。

新しい列マスターキーダイアログボックスを開く

Windows証明書ストアでキーの存在を確認してください。

Windows証明書ストアでキーの存在を確認してください

列マスターキーを作成するには、[ Column Master Keys ]フォルダーを右クリックして、[ New Column Master Keyキー]を選択します。 [ New Column Master Key ]ダイアログボックスで、列マスターキーの名前を入力し、現在のユーザーまたはローカルマシンの証明書ストアに保存するか、Azure Key Vaultに保存するかを指定して、リストから証明書を選択します。 証明書がない場合、または新しい自己署名証明書を使用する場合は、[証明書のGenerate Certificate ]ボタンをクリックし、[ OK ]をクリックします。 この手順では、自己署名証明書を作成し、SSMSを実行している現在のユーザーアカウントの証明書ストアにロードします。

注：これらの手順は、SQL Serverインスタンスをホストしているコンピューターではなく、信頼できるマシンで実行する必要があります。 これにより、ホストコンピューターが危険にさらされた場合でも、SQLServerでデータが保護されたままになります。

したがって、証明書を作成して列マスターキーとして構成した後、データへのアクセスを必要とするクライアントをホストしているすべてのコンピューターに証明書をエクスポートして配布する必要があります。 クライアントアプリケーションがWebベースの場合は、Webサーバーに証明書をロードする必要があります。 ユーザーのコンピューターにインストールされているアプリケーションの場合は、証明書を各ユーザーのコンピューターに個別に展開する必要があります。

オペレーティングシステムの証明書をエクスポートおよびインポートするための適切な手順は、次のURLにあります。

  • 証明書のエクスポート
    • Windows7およびWindowsServer2008 R2
    • Windows8およびWindowsServer2012
    • Windows8.1およびWindowsServer2012 R2
    • Windows10およびWindowsServer2016
  • 証明書のインポート
    • Windows7およびWindowsServer2008 R2
    • Windows8およびWindowsServer2012
    • Windows8.1およびWindowsServer2012 R2
    • Windows10およびWindowsServer2016

データを暗号化および復号化するアプリケーションを備えたコンピューターの証明書ストアに証明書をインポートする場合は、アプリケーションを実行しているドメインアカウントのマシン証明書ストアまたは証明書ストアのいずれかに証明書をインポートする必要があります。

列暗号化キー

列マスターキーを作成すると、特定の列の暗号化キーを作成する準備が整います。 SQL Server 2016 ADO.NETドライバーは、列暗号化キーを使用して、データをSQL Serverに送信する前に暗号化し、SQLServer2016インスタンスからデータを取得した後にデータを復号化します。 列マスターキーと同様に、T-SQLまたはSSMSを使用して列暗号化キーを作成できます。 列マスターキーはT-SQLを使用して作成する方が簡単ですが、列暗号化キーはSSMSを使用して作成する方が簡単です。

列暗号化キーを作成するには、 Object Explorerを使用してデータベースインスタンスに接続し、データベースに移動してから[ Security ]に移動し、[ Always Encrypted Keys ]フォルダーを展開します。 [ Column Encryption Keysの暗号化キー]を右クリックし、[ New Column Encryption Key ]を選択します。 [ New Column Encryption Key ]ダイアログボックスで、新しい暗号化キーの名前を入力し、ドロップダウンリストで[ Column Master Key Definition ]を選択して、[ OK ]をクリックします。 これで、新しいテーブルの定義で列暗号化キーを使用できます。

列暗号化キーの作成

SQL暗号化：列暗号化キーの作成、画像1

SQL暗号化：列暗号化キーの作成、画像2

暗号化された値を使用したテーブルの作成

列マスターキー定義と列暗号化キーを作成した後、暗号化された値を保持するテーブルを作成できます。

これを行う前に、使用する暗号化のタイプ、暗号化する列、およびこれらの列にインデックスを付けることができるかどうかを決定する必要があります。 常に暗号化される機能を使用すると、通常どおり列サイズを定義し、SQLServerは暗号化設定に基づいて列のストレージサイズを調整します。 テーブルを作成した後、 AlwaysEncryptedを使用してこのテーブルでコマンドを実行するようにアプリケーションを変更する必要がある場合があります。

SQLServer2016の暗号化の種類

暗号化された値を含むテーブルを作成する前に、各列を暗号化するかどうかを決定する必要があります。

まず、この列は値を検索するために使用されますか、それとも単にそれらの値を返すために使用されますか？

列をルックアップに使用する場合、列は確定的暗号化タイプを使用する必要があります。これにより、等価演算が可能になります。 ただし、 AlwaysEncrypted機能を使用して暗号化されたデータの検索には制限があります。 SQL Server 2016は、 equal to 、 not equal to 、 joins （等式を使用）、およびGROUP BY句の値の使用を含む等式操作のみをサポートします。 LIKEを使用した検索はサポートされていません。 さらに、 Always Encryptedを使用して暗号化されたデータの並べ替えは、アプリケーションレベルで実行する必要があります。これは、SQLServerが復号化された値ではなく暗号化された値に基づいて並べ替えるためです。

列がレコードの検索に使用されない場合、列はランダム化された暗号化タイプを使用する必要があります。 このタイプの暗号化はより安全ですが、検索、結合、またはグループ化操作をサポートしていません。

暗号化された列を含むテーブルの作成

テーブルを作成するときは、通常のCREATE TABLE構文を使用し、列定義内にいくつかの追加パラメーターを指定します。 CREATE TABLEステートメントのENCRYPTED WITH構文内で3つのパラメーターが使用されます。

これらの最初のものはENCRYPTION_TYPEパラメーターであり、 RANDOMIZEDまたはDETERMINISTICの値を受け入れます。 2番目はALGORITHMパラメーターであり、 RAEAD_AES_256_CBC_HMAC_SHA_256の値のみを受け入れます。 3番目のパラメーターはCOLUMN_ENCRYPTION_KEY 。これは、値の暗号化に使用する暗号化キーです。

 CREATE TABLE [dbo].[Customers] ( [CustomerId] [int] IDENTITY(1,1), [TaxId] [varchar](11) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [MiddleName] [nvarchar](50) NULL, [Address1] [nvarchar](50) NULL, [Address2] [nvarchar](50) NULL, [Address3] [nvarchar](50) NULL, [City] [nvarchar](50) NULL, [PostalCode] [nvarchar](10) NULL, [State] [char](2) NULL, [BirthDate] [date] ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', COLUMN_ENCRYPTION_KEY = YOUR_COLUMN_ENCRYPTION_KEY) NOT NULL PRIMARY KEY CLUSTERED ([CustomerId] ASC) ON [PRIMARY] ); GO

常に暗号化されたインデックス作成

暗号化されたデータを含む列は、 DETERMINISTIC暗号化タイプを使用して暗号化されている場合、インデックス内のキー列として使用できます。 RANDOMIZED暗号化タイプを使用して暗号化された列は、それらの列にインデックスを作成しようとするとエラーメッセージを返します。 いずれかの暗号化タイプを使用して暗号化された列は、非クラスター化インデックス内のINCLUDE列として使用できます。

暗号化された値はインデックスになる可能性があるため、Always Encryptedで暗号化された値には、通常実行するインデックス作成と調整以外に、追加のパフォーマンス調整手段は必要ありません。 返される値のサイズが大きくなることで生じる副作用は、ネットワーク帯域幅の追加とI/Oの増加だけです。

常に暗号化されたパフォーマンス

特にこの場合、通常のデータベーストラフィックに暗号化のオーバーヘッドを追加する場合、パフォーマンスは常に重要な要素です。 パフォーマンスをテストするのに最適なサイトはSQLパフォーマンスです。これは、さまざまなシナリオでクエリの実行とディスクの使用状況をテストしました。

SQLServerの常に暗号化されたパフォーマンス結果テスト。

SQL Serverの常に暗号化されたパフォーマンス結果テスト、イメージ1

SQL Serverの常に暗号化されたパフォーマンス結果テスト、イメージ2

暗号化および復号化プロセスで実行する必要のあるCPUおよびハードドライブの作業があるため、使用されるストレージスペースの量とクエリの期間に明らかな影響があります。 これは環境（CPU、RAM、およびディスクの機能）に影響されるため、これが本番環境で問題になるかどうかをテストする必要があります。

注： Microsoft SQL Severのパフォーマンスの最適化について詳しく知りたい場合は、以前の記事の1つであるMicrosoftSQLServerのパフォーマンスを調整する方法を確認してください。

アプリケーションの変更

レガシーコードにAlwaysEncryptedを適切に実装するには、何をする必要がありますか？

SQL Server2016のAlwaysEncrypted機能の優れた点の1つは、ストアドプロシージャ、ORM、またはパラメーター化されたT-SQLコマンドを既に使用しているアプリケーションで、不等式操作が既に使用されていない限り、AlwaysEncryptedを使用するためにアプリケーションを変更する必要がないことです。 アプリケーション内で動的SQLとしてSQLステートメントを構築し、データベースに対してそれらのコマンドを直接実行するアプリケーションは、Always Encrypted機能を利用する前に、クエリのパラメーター化を使用するように直接変更する必要があります。これは、すべてのアプリケーションに推奨されるセキュリティのベストプラクティスです。

Always Encryptedを機能させるために必要なもう1つの変更は、データベースに接続しているアプリケーションの接続文字列に接続文字列属性を追加することです。 Column Encryption Setting=enabled 。

この設定を接続文字列に追加すると、ADO.NETドライバーはSQL Serverに、実行中のコマンドに暗号化された列が含まれているかどうか、含まれている場合はどの列が暗号化されているかを確認します。 高負荷のアプリケーションの場合、特に実行中のコマンドの大部分に暗号化された値が含まれていない場合は、この設定の使用がベストプラクティスではない可能性があります。

その結果、.NET Frameworkは、 SqlCommandColumnEncryptionSettingと呼ばれるSqlConnectionオブジェクトに新しいメソッドを提供します。このメソッドには、次の3つの値があります。

  • Disabled —この接続オブジェクトを使用して実行されるクエリに使用するAlwaysEncryptedの列またはパラメーターはありません。
  • Enabled —この接続オブジェクトを使用して実行されるクエリには、常に暗号化された列やパラメータが使用されています。
  • ResultSet —常に暗号化されたパラメーターはありません。 ただし、この接続オブジェクトを使用してクエリを実行すると、AlwaysEncryptedを使用して暗号化された列が返されます。

注：このメソッドを使用すると、アプリケーションコードに大幅な変更が必要になる可能性があることに注意してください。 別のアプローチは、異なる接続を使用するようにアプリケーションをリファクタリングすることです。

SQL Serverの最高のパフォーマンスを得るには、AlwaysEncryptedを使用するクエリに対してAlwaysEncryptedに関するメタデータのみを要求することをお勧めします。 つまり、クエリの大部分がAlways Encryptedを使用するアプリケーションでは、接続文字列を有効にし、アプリケーション内の特定のクエリでSqlCommandColumnEncryptionSettingをDisabledとして指定する必要があります。 ほとんどのクエリでAlwaysEncrypted値を使用していないアプリケーションの場合、接続文字列を有効にしないでください。また、Always Encrypted列を使用しているクエリでは、必要に応じてSqlCommandColumnEncryptionSettingをEnabledまたはResultSetに設定する必要があります。 ほとんどの場合、アプリケーションは接続文字列属性を有効にするだけでよく、暗号化されたデータを使用している間、アプリケーションのパフォーマンスは変わりません。

常に暗号化する価値はありますか？

簡潔な答え？ はい、間違いなく！

これは、多くの潜在的なセキュリティ上の懸念を防ぎ、SQL開発者に追加のセキュリティ機能を提供するだけでなく、システムをより準拠させます。これは、通信から銀行、保険まで、複数の業界で不可欠です。 また、この記事に記載されている技術的な前提条件を考慮すると、 Always Encryptedは、既存のシステムへの最小限のアプリケーション変更で実装できることに注意することも重要です。

カスタムソリューションを使用して同じ効果を得ることができますが、このテクノロジは新しいバージョンのSQL Serverにバンドルされており、そのまま使用できます。 これは新しいテクノロジーであるため、その使用にはまだいくつかの制限があり、ハードウェアのデメナドが追加されることに注意することも重要です。

ただし、それらが環境にとって大きな問題であり、会社のイントラネットの外部に配布されているアプリケーションがない限り、AlwaysEncryptedを使用しない理由は事実上ありません。