SQL DB 암호화 / TDE(Transparent Data Encryption)

MS-SQL 2008 ENT 이상 버전에서 지원되는 TDE 기능에 대한 테스트를 진행 한다. 

STD 에서도 복원은 가능하나, DB 는 사용 할 수 없다. 

OS : Windows 2016

DBMS : MS-SQL 2014 ENT

1. DB 생성

DB 명 : gunnm 

USE master; CREATE DATABASE [gunnm] ON ( NAME = gunnm, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf') LOG ON ( NAME = gunnm_log, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf') GO

2. 암호화 진행

마스터 키 생성

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord!2#' GO

마스터 키로 보호 된 인증서 생성

CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'gunnm_cert' GO

마스터 키 백업

USE master BACKUP SERVICE MASTER KEY TO FILE = 'H:\service_master.key' ENCRYPTION BY PASSWORD = 'Service_PW@1119' GO BACKUP MASTER KEY TO FILE = 'H:\db_master.key' ENCRYPTION BY PASSWORD = 'DB_PW#1119' GO

DB 암호화 키 생성

USE gunnm GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE gunnm_cert  GO 실행 메시지  경고: 데이터베이스 암호화 키를 암호화하는 데 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 수 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 할 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 열 수 없습니다.

DB 암호화 적용

USE gunnm GO ALTER DATABASE gunnm SET ENCRYPTION ON GO

DB 암호화 키 백업

USE master BACKUP CERTIFICATE gunnm_cert TO FILE = 'H:\gunnm_cert.cer' WITH PRIVATE KEY ( FILE = 'H:\gunnm_cert.pvk' , ENCRYPTION BY PASSWORD = 'gunnm_PW$1119' ) GO

DB 백업

BACKUP DATABASE [gunnm] TO  DISK = 'H:\gunnm.bak'; GO

3. 대상 서버에서 DB 복원 진행

원본 서버에서 생성 된 DB 인증서 파일 (.cer / .pvk) 및 DB 백업 파일 (.bak) 을 복사한다. 

마스터 키 생성 : 원본 서버와 동일한 패스워드를 입력할 필요는 없다. 

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord1@3' GO CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'DB_Enc' GO

원본 서버 인증서 파일을 통한 인증서 생성 및 DB 복원

CREATE CERTIFICATE gunnm_cert   FROM FILE = 'H:\gunnm_cert.cer'   WITH PRIVATE KEY (      FILE = 'H:\gunnm_cert.pvk',  DECRYPTION BY PASSWORD = 'gunnm_PW$1119'   ); 실행 메시지 :  메시지 15232, 수준 16, 상태 1, 줄 2 이름이 'gunnm_cert'인 인증서가 이미 있거나 이 인증서가 데이터베이스에 이미 추가되었습니다. 테스트이므로 서버간 이동을 하지 않고, 동일 서버에서 진행되어 발생 된 오류로 추정 -- 등록 인증서 제거 USE master;  DROP CERTIFICATE gunnm_cert; CREATE CERTIFICATE gunnm_cert   FROM FILE = 'H:\gunnm_cert.cer'   WITH PRIVATE KEY (      FILE = 'H:\gunnm_cert.pvk',  DECRYPTION BY PASSWORD = 'gunnm_PW$1119'   ); RESTORE DATABASE [gunnm]   FROM DISK = 'H:\gunnm.bak'   WITH MOVE 'gunnm' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf',        MOVE 'gunnm_log' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf';

4. TDE 모니터링 스크립트

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, encryption_state_desc = CASE encryption_state          WHEN '0'  THEN  'No database encryption key present, no encryption'          WHEN '1'  THEN  'Unencrypted'          WHEN '2'  THEN  'Encryption in progress'          WHEN '3'  THEN  'Encrypted'          WHEN '4'  THEN  'Key change in progress'          WHEN '5'  THEN  'Decryption in progress'          WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'          ELSE 'No Status'          END, percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

USE master GO SELECT * FROM sys.certificates -- encryption_state = 5 is encrypted SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3;

5. 암호화 제거

DB 암호화 비활성화

USE gunnm ALTER DATABASE gunnm SET ENCRYPTION OFF GO

DB 암호화 키 제거

USE gunnm GO DROP DATABASE ENCRYPTION KEY

마스터 키 및 인증서 제거

-- 마스터 키 제거 USE master; drop master key; GO -- 등록 인증서 제거 USE master;  DROP CERTIFICATE gunnm_cert; GO