[ms sql] 백업 암호화 방법

l  SQL Server 백업 암호화 기본 구조

- 마스터 키(DMK)와 인증서를 이용한 백업 암호화 방식

- 마스터 키와 인증서는 master DB에서만 만들어서 관리함. 마스터 키는 1개만 존재 가능하지만 인증서는 용도별로 여러 개 존재할 수 있음.

- 암호화된 백업파일(.bak)은 암호화에 사용된 인증서와 개인키가 없으면 복원 불가.

따라서 인증서(.cer)와 개인 키(.pvk)는 백업해서 안전한 장소에 보관 필요(마스터 키는 백업해둘 필요 없음)

à 인증서와 개인 키 백업 시 비밀번호도 보관 필요

- 백업 파일에 암호화가 되어 있던 없던 복원하는 방법은 똑같음. 단지 복원할 서버에 마스터 키와 인증서(개인키)가 있느냐 없느냐의 차이

 

l  마스터 키와 인증서 생성

- master DB에 마스터 키(DMK)와 백업용으로 사용할 인증서를 생성

USE master;

GO

 

-- 1. 마스터 키 존재 여부 확인 방법

SELECT *

FROM sys.symmetric_keys

WHERE name = '##MS_DatabaseMasterKey##';

GO

 

-- 2. 마스터 키가 없다면 마스터 키 생성

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword!123';

GO

-- 3. 백업 암호화에 사용할 인증서 생성

CREATE CERTIFICATE BackupCert

WITH SUBJECT = 'Database Backup Encryption Certificate';

GO

확인 방법: SELECT * FROM sys.certificates WHERE name = 'BackupCert'; 쿼리를 실행해 BackupCert 행이 정상적으로 조회되는지 확인

- DB 백업 파일(.bak) 복원 과정에서 마스터 키(비밀번호 포함)는 몰라도 상관 없음

 

l  인증서와 개인 키 백업

- 복원을 대비해 인증서를 비밀번호로 암호화하여 파일로 백업해 둠

USE master;

GO

 

BACKUP CERTIFICATE BackupCert

TO FILE = 'C:\SQLBackups\Keys\BackupCert.cer'

WITH PRIVATE KEY (

    FILE = 'C:\SQLBackups\Keys\BackupCert_PrivateKey.pvk',

    ENCRYPTION BY PASSWORD = 'YourCertPassword!123'

);

GO

확인 방법: 지정한 경로(C:\SQLBackups\Keys\)에 .cer 파일과 .pvk 파일이 생성되었는지 확인

- 원본 서버의 인증서만 남아 있다면, 언제든 인증서와 개인 키를 새로운 비밀번호로 다시 백업 받아서 기존 백업파일(.bak)을 복원할 수 있음

 

l  유지 관리 계획 백업 암호화 적용

- 일반 백업과 동일하며 아래와 같이 백업 암호화에 체크를 하면 됨

 

l  복원 방법 – 동일한 서버에서 복원하는 경우

- 백업할 때 사용한 마스터 키와 인증서가 이미 존재하기 때문에 일반 파일 복원과 동일함

l  복원 방법 – 다른 서버에서 복원하는 경우

- 백업할 때 사용한 인증서(.cer)와 개인 키(.pvk)가 없기 때문에 새 서버에 먼저 등록 후 복원 가능

1) 인증서와 개인 키를 안전한 곳(C:\SQLBackups\Keys\)에 복사

2) master DB에 마스터 키(DMK) 생성 (이미 마스터 키가 있다면 이 단계는 생략)

USE master;

GO

 

-- 새 서버의 master DB에 마스터 키 생성 (새 비밀번호 지정)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'NewServerStrongPassword!123';

GO

3) 인증서와 개인 키 복원 (개인 키 암호는 원본 서버에서 내보낼 때 지정한 암호 입력)

USE master;

GO

 

CREATE CERTIFICATE BackupCert

FROM FILE = 'C:\SQLBackups\Keys\BackupCert.cer'

WITH PRIVATE KEY (

    FILE = 'C:\SQLBackups\Keys\BackupCert_PrivateKey.pvk',

    DECRYPTION BY PASSWORD = 'YourCertPassword!123' -- 원본 백업 시 설정했던 키 암호

);

GO

확인 방법: SELECT * FROM sys.certificates WHERE name = 'BackupCert'; 를 실행해 새 서버에 등록되었는지 확인

4) 일반 파일 복원과 동일한 방법으로 복원함