マイクロソフトの平山です。これから数回にわたり「透過的なデータ暗号化」を使用して暗号化されたデータベースの使い勝手について紹介していきたいと思います。 

SQL Server 2008 から導入された、「透過的なデータ暗号化」のメリットのひとつとして、データベースの物理ファイルやバックアップが流出したり盗難にあってもデータをのぞき見されることがない、ということが挙げられます。それは次のような理由によります。

  • データベース全体が暗号化されるため、流出したファイルをバイナリエディタなどで展開しても、ファイルの中身を理解することができない
  • データベースの復元(あるいはアタッチ)には証明書が必要なので、バックアップファイルのみが流出しても不正にリストアされてしまうことはない

SQL Server オンラインブックなどのドキュメントには、データベースを暗号化するための方法は親切に解説してありますが、暗号化したデータベースを復元する方法をわかりやすくまとめている箇所がありません。そこで今回のエントリでは、暗号化されたデータベースを別インスタンスに復元する方法を紹介したいと思います。

●まずはデータベースの暗号化までのおさらい

1. データベースとテーブルを作成します
create database TDE_DB
go
use TDE_DB
go
create table t1 (c1 nvarchar(500))
go
2. サービスマスターキーを作成します
use master
go
create master key encryption by password = 'secret!masterKey1'
go
3. 証明書を作成します
create certificate TDE_TEST with subject = 'Test Certificate'
go
3. データベース暗号化キーを作成します
use TDE_DB
go
create database encryption key with algorithm = AES_256
encryption by server certificate TDE_TEST
go
4. データベースを暗号化します
alter database TDE_DB set encryption on
go
 

●続いてデータベースと証明書のバックアップ

1. データベースのバックアップ
backup database TDE_DB to disk='C:\TDE_DB.bak'
go
2. 証明書のバックアップ
backup certificate TDE_TEST to file = 'C:\TDE_TEST.cer'
with private key (file = 'c:\TDE_TEST_KEY.pvk',encryption by password = 'secret!masterKey1')
go

●いよいよ別インスタンスへの復元です。データベースと証明書のバックアップファイルは復元先のマシンにコピーしておいてください。

1. 復元先のインスタンスでも独自のサービスマスターキーを作成しておく必要があります
create master key encryption by password = 'tdetest!tdetest1'
go
2. データベースの復元に先駆けて、証明書を復元します
create certificate TDE_TEST
from file = 'c:\TDE_TEST.cer'
with private key (file = 'c:\TDE_TEST_KEY.pvk', decryption by password ='secret!masterKey1')
go
3. これで別インスタンスへのデータベースの復元が可能になります
restore database TDE_DB from disk='C:\TDE_DB.bak'
go

ちなみに上記の手順ではなく、単にデータベースの復元のみを行うと次のようなエラーが発生します。

メッセージ 33111、レベル 16、状態 3、行 1
拇印 '0xC31E2828C900C230B2BC1E9BEBE5B66DCAB9EFXX' でサーバー 証明書 が見つかりません。

また、データベースの復元の際にとても重要な意味を持つ、サービスマスターキーのパスワード、証明書のバックアップの管理には細心の注意を払うようにしてください。