SQL Server 2016 Express の暗号化

SQL Server 2016 Express の暗号化機能として、Always Encrypted 機能というのがあるようです。 ## Always Encrypted 機能について 以下の URL で示されているように、クライアント側 (ADO.NET Library 内) で暗号・復号処理が行われようです。 - https://enterprisezine.jp/dbonline/detail/7961?p=2 これを使用することによるメリットとして、 - クライアントアプリケーション側で暗号・復号を意識する必要がなくなる - サーバー側のデータが常に暗号化される（通信上のデータまで暗号化される） - サーバー側の負荷が小さくなる（クライアント側の負荷が大きくなる） が挙げられます。 ## 暗号処理の適用方法 適用方法の流れは以下の通りです。 1. 自己署名証明書の作成 1. 暗号鍵の作成 1. テーブル暗号化 ### 自己署名証明書の作成 自己署名証明書の作成方法は主に２通りあるようです。 - https://blogs.technet.microsoft.com/junichia/2010/11/09/azure-for-itpro-3/ ここでは `makecert.exe` `pvk2pfx.exe` を使用し、自己署名証明書を作成します。 私の環境ではこれらの exe は `C:\Program Files (x86)\Windows Kits\10\bin\10.0.15063.0\x64` にありました。 まずは `makecert.exe` で証明書ファイル (CER) と秘密鍵ファイル (PVK) を作成します。 以下、コマンド例です。 ```sh makecert -r -pe -n "CN=AE Test" -a sha1 -b 01/01/2000 -e 01/01/2100 -sky exchange -sv test.pvk test.cer ``` コマンドの内容については以下を参照してください。 - https://msdn.microsoft.com/ja-jp/library/bfsktky3(v=vs.100).aspx 次に個人情報交換ファイル (PFX) を作成します。 以下、コマンド例です。 ```sh pvk2pfx -pvk test.pvk -pi 1234 -spc test.cer -pfx test.pfx -po 1234 ``` コマンドの内容については以下を参照してください。 - https://msdn.microsoft.com/ja-jp/library/ff550672(VS.85).aspx ### 証明書のインポート 前節で作成した証明書を PC にインポートします。 [ファイル名を指定して実行]から `certmgr.msc` を実行し、現在のユーザーの証明書マネージャーツール起動します。 ※ちなみにローカルコンピューターの証明書マネージャーツールは `certlm.msc` で起動します。 [個人]を右クリックし、[すべてのタスク] > [インポート]を選択します。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheKxNw3YBhCIFPu0aViZbNeH0FVuPagRG2qTh0kVKSHcz2fWrIWmlXbVyAVKuY_8HAaGOUOK025w0bRhqmurQSXDC6FHCn8W_HPj-5ZGyUe4hwRzo6oFhjnkZYOf-PZTdhtBQUbVfbq8Y/s1600/cert01.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEheKxNw3YBhCIFPu0aViZbNeH0FVuPagRG2qTh0kVKSHcz2fWrIWmlXbVyAVKuY_8HAaGOUOK025w0bRhqmurQSXDC6FHCn8W_HPj-5ZGyUe4hwRzo6oFhjnkZYOf-PZTdhtBQUbVfbq8Y/s320/cert01.png" width="320" height="227" data-original-width="626" data-original-height="444" /></a> [証明書のインポート ウィザード]で、[次へ]をクリックします。 [インポートする証明書ファイル]ページで、前節で作成した個人情報交換ファイル (PFX) を選択します。 ※[ファイルの種類]で[Personal Information Exchange (*.pfx; *.p12)]を選択する必要があります。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge25Zk4C4GjLvUikORwnR-wxlqcu66AdenjzKCciL8Wg8XpTwnNIAFm21-__nq_svhUDyQGEQTxZmcTKu2roCnUOzmonRwd6-nEJ4a9YrzClPyXlyL18gw-8UgaluOl5Ecf9C4qITD9MQ/s1600/cert02.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge25Zk4C4GjLvUikORwnR-wxlqcu66AdenjzKCciL8Wg8XpTwnNIAFm21-__nq_svhUDyQGEQTxZmcTKu2roCnUOzmonRwd6-nEJ4a9YrzClPyXlyL18gw-8UgaluOl5Ecf9C4qITD9MQ/s320/cert02.png" width="320" height="78" data-original-width="774" data-original-height="188" /></a> 秘密キーのパスワードは、PFX ファイルに割り当てられているパスワードを入力します。 [証明書ストア]ページで、[証明書をすべて次のストアに配置する]を選択し、[次へ]をクリックします。 以上でインポートは完了です。 ### 暗号鍵の作成 CMK (Column Master Key) と CEK (Column Encryption Key) の２種類の鍵が必要です。 |名称|内容| |---|---| |CMK|CEK を保護するための鍵。前節で作成した証明書の情報が記録される| |CEK|テーブルの列を暗号化するための鍵| SQL Server Management Studio より、上記の鍵を作成します(Transact-SQL でも可)。 **CMK の場合** 対象のデータベースの [セキュリティ] > [常に暗号化されたキー] > [列マスターキー] を右クリックし、[新しい列マスターキー] を選択します。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfNFvePT1oaAt7NKLyo2QU6MG7oOq7lRA3R72N4oZBiv2Zex4ZowKuvgt0t77oOSK-8qoDscXcAiMDf3yJSpSoEqwgmQCJsYWHNLvMiUfZyCSHLJGocjR7WlTkR52rCidWBrhribDlCis/s1600/ssms01.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfNFvePT1oaAt7NKLyo2QU6MG7oOq7lRA3R72N4oZBiv2Zex4ZowKuvgt0t77oOSK-8qoDscXcAiMDf3yJSpSoEqwgmQCJsYWHNLvMiUfZyCSHLJGocjR7WlTkR52rCidWBrhribDlCis/s320/ssms01.png" width="268" height="320" data-original-width="398" data-original-height="476" /></a> 任意の名前を入力し、前節でインポートした証明書を選択し、OK をクリックします。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjB1UdK6l0240YN-KCDliVIZNqHRqOzDOm6l1u1TNOZkrMAwRqkUIfi4ttT7hwAevBeGynGthxU6PHuXhz7HRMYY5fEecl6oTCAkC9HHrlXdWP2AxkPg7gw9xiZumwCCXDdue71ryVYvI/s1600/ssms02.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjB1UdK6l0240YN-KCDliVIZNqHRqOzDOm6l1u1TNOZkrMAwRqkUIfi4ttT7hwAevBeGynGthxU6PHuXhz7HRMYY5fEecl6oTCAkC9HHrlXdWP2AxkPg7gw9xiZumwCCXDdue71ryVYvI/s320/ssms02.png" width="320" height="269" data-original-width="690" data-original-height="579" /></a> **CEK の場合** 対象のデータベースの [セキュリティ] > [常に暗号化されたキー] > [列の暗号化キー] を右クリックし、[新しい列の暗号化キー] を選択します。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0tkajpb4qegvQNJC-PRZrBME4yQp0cTwfg2svRM2uvZLMC46iX1_UEwJ5JjL7oGJWrrGYqFxpT9chJr-APJ96lUensPnPK9NEiVkepb5EMSU3HhQ088cVhv-GRgvcuPlToFbHv_i9OYk/s1600/ssms03.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0tkajpb4qegvQNJC-PRZrBME4yQp0cTwfg2svRM2uvZLMC46iX1_UEwJ5JjL7oGJWrrGYqFxpT9chJr-APJ96lUensPnPK9NEiVkepb5EMSU3HhQ088cVhv-GRgvcuPlToFbHv_i9OYk/s320/ssms03.png" width="269" height="320" data-original-width="408" data-original-height="485" /></a> 任意の名前を入力し、上記の列マスターキーを選択し、OK をクリックします。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFHBbbLbV4Ft2PJzf4ZBye8svlc5h20JDFvbkyiCo1kN7XvG5FvnQ-FtKjsPcTzOKNejDTwI4Eji1XHNkKhxwMoAjqKROR1zC9eSQcoCJSz8_TnhmgAyvSs8uNc_oLf5xV6J-15EiBcxg/s1600/ssms04.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFHBbbLbV4Ft2PJzf4ZBye8svlc5h20JDFvbkyiCo1kN7XvG5FvnQ-FtKjsPcTzOKNejDTwI4Eji1XHNkKhxwMoAjqKROR1zC9eSQcoCJSz8_TnhmgAyvSs8uNc_oLf5xV6J-15EiBcxg/s320/ssms04.png" width="320" height="269" data-original-width="690" data-original-height="579" /></a> ### テーブルの暗号化 暗号化するテーブルを右クリックし、[列の暗号化] をクリックします。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7udRWG89-O595ScSzKubacYpAkkNW5QcxWQlO46TIrhXQwj7ETOtH_bouHqiy49sPO_3ltOKOMwCJdni6C6RWssWIKu6gJzsmtSE_7YLb43Lp59qiwAAV8EVsuCU1lS3QCoTVkx4BvRs/s1600/ssms05.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7udRWG89-O595ScSzKubacYpAkkNW5QcxWQlO46TIrhXQwj7ETOtH_bouHqiy49sPO_3ltOKOMwCJdni6C6RWssWIKu6gJzsmtSE_7YLb43Lp59qiwAAV8EVsuCU1lS3QCoTVkx4BvRs/s320/ssms05.png" width="276" height="320" data-original-width="398" data-original-height="461" /></a> 暗号化したい列にチェックを入れます。 暗号の種類は「決定論的」と「ランダム化」を選択できますが、暗号化した列を WHERE 句の条件として使用する場合は「決定論的」を選択します。 暗号化キーは前節で作成した CEK を選択します。 <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaLprcVE0yEq3yPSLJPMsJwBa97wjBeo06KHe-3l8dYlYsz2JwrOqneN3ZcwTVeiE0NvOZEQZCSfAdeq_EerhebDtHqJiCHN9-gv3Bdpg3iJvB-h4nJdj-jJ-ey7EJb9TUEY2o7XN3oXY/s1600/ssms06.png" imageanchor="1" ><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgaLprcVE0yEq3yPSLJPMsJwBa97wjBeo06KHe-3l8dYlYsz2JwrOqneN3ZcwTVeiE0NvOZEQZCSfAdeq_EerhebDtHqJiCHN9-gv3Bdpg3iJvB-h4nJdj-jJ-ey7EJb9TUEY2o7XN3oXY/s320/ssms06.png" width="320" height="291" data-original-width="823" data-original-height="748" /></a> 暗号化は以上で完了です。 ## アプリケーション側での利用方法 アプリケーション側で Always Encrypted 機能を使用する手順は以下です。 - 証明書のインポート - 接続文字列の追加 - SqlParameter クラスの使用 ### 証明書のインポート 手順は前章と同じなので省略します。 ### 接続文字列の追加 データベースの接続文字列に `Column Encryption Setting=enabled` を追加する必要があります。 Entity Framework を使用する場合はこれだけで OK です。 尚、Entity Framework Core は現在 Always Encrypted には未対応です。 - https://github.com/aspnet/EntityFrameworkCore/issues/9193 ## 参考 URL - https://enterprisezine.jp/dbonline/detail/7961 - https://blogs.technet.microsoft.com/junichia/2010/11/09/azure-for-itpro-3/ - http://tnakamura.hatenablog.com/entry/2014/08/28/123000 - https://knowledge.autodesk.com/ja/search-result/caas/CloudHelp/cloudhelp/2016/JPN/AutoCAD-Customization/files/GUID-19D6716A-0AD1-4A7A-82BA-A067E6D65F66-htm.html