TDE 설정: master key, 인증서, 인증서 백업, 암호화 진행률

명령어

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에 database master key와 인증서를 만들고, 인증서를 private key와 함께 백업하며, AES-256 database encryption key를 생성해 데이터 및 로그 파일을 저장 시 암호화한다. 백그라운드 암호화 스캔의 진행 상황을 보여 준다.

사용 시점

GDPR/PCI 같은 저장 데이터 암호화 요구 사항, 그리고 백업 파일 및 MDF/LDF 파일 도난 대비.

주의

인증서 + private key 백업이나 그 비밀번호를 잃으면 TDE 데이터베이스와 모든 백업을 영구히 읽을 수 없다. 백업은 서버와 떨어진 안전한 보관소에 둔다. 어떤 데이터베이스든 TDE를 활성화하면 tempdb도 암호화된다(서버 전체에 작은 CPU 비용). 암호화 스캔은 I/O가 많다. 2019+에서는 ALTER DATABASE ... SET ENCRYPTION SUSPEND/RESUME으로 일시 중지할 수 있다. AG/log shipping에서는 먼저 모든 대상 서버에 인증서를 설치해야 한다. TDE는 메모리나 네트워크상의 데이터를 암호화하지 않는다.