TDE を設定する: マスターキー、証明書、証明書のバックアップ、暗号化の進捗

コマンド

USE master;
IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<GucluParola_1>';
CREATE CERTIFICATE [TDE_Cert_2026]
    WITH SUBJECT = N'TDE certificate', EXPIRY_DATE = '20361231';

-- Back it up NOW: without cert + private key the backups cannot be restored anywhere
BACKUP CERTIFICATE [TDE_Cert_2026]
    TO FILE = N'D:\Keys\TDE_Cert_2026.cer'
    WITH PRIVATE KEY (FILE = N'D:\Keys\TDE_Cert_2026.pvk',
                      ENCRYPTION BY PASSWORD = N'<GucluParola_2>');

USE [Musteri];
CREATE DATABASE ENCRYPTION KEY
    WITH ALGORITHM = AES_256
    ENCRYPTION BY SERVER CERTIFICATE [TDE_Cert_2026];
ALTER DATABASE [Musteri] SET ENCRYPTION ON;

-- Progress and certificate backup date:
SELECT DB_NAME(dek.database_id) AS db, dek.encryption_state_desc, dek.percent_complete,
       dek.encryption_scan_state_desc, c.name AS cert_name, c.expiry_date, c.pvt_key_last_backup_date
FROM   sys.dm_database_encryption_keys dek
  LEFT JOIN master.sys.certificates c ON c.thumbprint = dek.encryptor_thumbprint;

-- On the target server (before restore / AG secondary):
-- CREATE CERTIFICATE [TDE_Cert_2026] FROM FILE = N'D:\Keys\TDE_Cert_2026.cer'
--   WITH PRIVATE KEY (FILE = N'D:\Keys\TDE_Cert_2026.pvk', DECRYPTION BY PASSWORD = N'<GucluParola_2>');

概要

master にデータベースマスターキーと証明書を作成し、証明書を秘密キーとともにバックアップし、AES-256 のデータベース暗号化キーを作成して、保存時のデータファイルとログファイルを暗号化する。バックグラウンドの暗号化スキャンの進捗を表示する。

使用する場面

GDPR/PCI などの保存データ暗号化の要件に、およびバックアップファイルや MDF/LDF ファイルの盗難対策として。

注意

証明書 + 秘密キーのバックアップまたはそのパスワードを失うと、TDE データベースとそのすべてのバックアップは永久に読み取れなくなる。バックアップはサーバーから離れた安全な保管庫に保管する。いずれかのデータベースで TDE を有効にすると tempdb も暗号化される (サーバー全体でわずかな CPU コスト)。暗号化スキャンは I/O 負荷が高い。2019 以降では ALTER DATABASE ... SET ENCRYPTION SUSPEND/RESUME で一時停止できる。AG / ログ配布では、先にすべてのターゲットサーバーに証明書をインストールする必要がある。TDE はメモリ上やネットワーク上のデータは暗号化しない。