山田健一のブログ

業務に強いフリーITエンジニア 山田健一のブログです

TrueCryptとSQLServer

私はフリーITエンジニアとして、業務分析、業務改善提案を含む設計、製造、受入検査を含む検査、システム運用開始後の運用改善提案など、様々な分野を担当しています。

TrueCrypt(暗号化ソフト)とSQLServer(データベース)との組み合わせを試してみたくなり、実験してみました。
きっかけは単なる思いつきだったんですが、「TrueCrypt SQLServer」で検索して、次のページに辿り着きました。

stackoverflow.com

「そんな馬鹿な!」という内容、すなわち、
「暗号化ボリュームの中にデータベースファイルを作成した後、暗号化ボリュームをディスマウントしてもデータが読めてしまう」という内容だったので、調べてみることにしました。

実験する前に、自分なりの仮説を立てました。
===============================================================================
SQLServer はデータベースなので、通常のファイルアクセス以外の方法でファイルを読み書きしていると思う。だから、TrueCryptでの自動暗号化/復号化は行われず、パフォーマンスには影響がないはず。暗号化/復号化が行われないと言ってもデータベースファイル(.mdf)を通常ボリュームから暗号化ボリュームにコピーした際は暗号化が行われるので、暗号化ボリュームにコピーしたデータベースファイルは使用できないはず。同様に暗号化ボリュームから通常ボリュームにコピーしたデータベースファイルも使用できないはずなので、データを隠したいという用途には使えるはず」
===============================================================================

ついでに テストデータ生成(データ数が多ければ結果は違うんじゃないか?)や
BULK INSERT も試すことにしました。

テストデータは次のサイトで 5,000件生成しました。

疑似個人情報データ生成サービス

このデータに合わす形でテーブルデザインを次のようにしました。

f:id:yamadaken1:20160121172457j:plain

BULK INSERT のSQLは次のようになります。
CSVファイルの文字コードは Shift-Jis としておきます。


BULK INSERT [m01member]
FROM 'D:\SQLDATACSV\personal_infomation.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
);

 データが入ったので、試しにデータベースをデタッチ、アタッチしてみます。

問題なくデータを見ることができます。

次は、デタッチ、データベースファイルを暗号化ボリュームにコピー、アタッチを試します。

予想どおり、アタッチはエラーになります。

f:id:yamadaken1:20160121172620j:plain

次に、暗号化ボリュームにデータベースファイルを作成し、テーブル作成とBULK INSERT を試みます。
事前に先ほどコピーしたデータベースファイルを削除しておきます。

データベースファイル作成、テーブル作成、BULK INSERT いずれも問題なく実行できます。

次に、暗号化ボリュームをディスマウントします。

ファイルが使用中という警告が出ますが、[はい]をクリックして先に進めます。

f:id:yamadaken1:20160121172644j:plain

この状態で、Management Studio から、テーブル内容を見ようとすると、なんと、エラーにならずに見えてしまいます。

stackoverflow で論じられていた現象が再現できたということです。

接続を解除してから、再接続しても結果は同じです。

次はSQLServerを停止・起動してみます。

流石に今度はエラーになります。
Management Studio を再起動すると、問題のデータベースは(復旧待ち)となっています。
暗号化ボリュームをマウントしても回復しないので、Management Studio を再起動してみますが、回復しません。
SQLServerを停止・起動してみます。
今度は、問題なくテーブルデータを見ることができました。
ただし、この現象は、「データベースファイルをTrueCryptの暗号化ボリュームの中に作成すると運用に工夫が必要である」ということを意味します。

通常、SQLServerはPC起動時に自動起動します。このとき、暗号化ボリュームはマウントされていません。
そのため、SQLServer自動起動せずに、PC起動時に「暗号化ボリュームをマウントしてからSQLServer起動」という手順を踏む必要があります。

次に、暗号化ボリューム内のデータベースをデタッチして、データベースファイルを通常ボリュームにコピーしてからアタッチします。
予想どおりエラーになります。

若干の注意点はあるものの、TrueCryptを利用することでデータベースのデータを隠すことができることが分かりました。

この実験では、「暗号化ボリュームの中にあるデータベースファイルが拡張されても問題ないか?」というところまでは、試していません。

TrueCrypt利用に興味がある方は、念のため、試してみてからの運用をお勧めします。