데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

TDE 암호화된 데이터베이스 복원

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server 2008부터 도입된 암호화 솔루션인 TDE는 전체 데이터베이스를 암호화 하고 암호화된 데이터베이스에 액세스하는 응용프로그램에 완전히 투명하게 하도록 하는 기능을 제공한다.

 

TDE는 고급 암호화 표준(AES) 또는 트리플 DES(3DES) 암호화를 사용하여 데이터베이스 파일(MDF)과 로그파일(LDF) 모두에 저장된 데이터를 암호화 한다. 이 암호화 에서는 DEK(데이터베이스 암호화 키)를 사용하며 이 키는 복구하는 동안 사용할 수 있도록 데이터베이스 부트 레코드에 저장된다. DEK는 서버의 master 데이터베이스에 저장된 인증서 또는 EKM 모듈로 보호되는 비대칭 키를 사용하여 보호되는 대칭 키 이다.

 

데이터베이스 파일 암호화는 페이지 수준에서 수행된다. 암호화된 데이터베이스 페이지는 암호화 된 후 디스크에 작성되고 메모리로 읽어 들일 때 암호화 해독 된다. TDE로 암호화된 데이터베이스의 크기가 증가되지 않는다. 암호화 및 암호화 해독 작업은 SQL Server에 의해 백그라운드 스레드로 예약된다.

 

TDE는 데이터베이스 백업도 암호화 된다. 이것은 백업 미디어를 도난 당한 경우 중요한 정보 손실에 대한 보호기능을 제공한다.

 

 

이번 포스트에서는 TDE로 암호화된 데이터베이스를 다양한 시나리오를 통해서 복구방법에 대해서 알아본다.

 

아래 스크립트는 실습에서 사용할 TDE를 사용한 데이터베이스를 생성한다. 인증서를 생성하고 인증서 백업을 진행 한다.

USE [master];

GO

 

-- Create the database master key

-- to encrypt the certificate

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!';

GO

 

-- Create the certificate we're going to use for TDE

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Cert for Test';

GO

 

-- Back up the certificate and its private key

-- Remember the password!

BACKUP CERTIFICATE TDECert

TO FILE = N'C:\SQLBackups\TDECert.cer'

WITH PRIVATE KEY (

FILE = N'C:\SQLBackups\TDECert_key.pvk',

ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'

);

GO

 

-- Create our test database

CREATE DATABASE [RecoveryWithTDE];

GO

 

-- Create the DEK so we can turn on encryption

USE [RecoveryWithTDE];

GO

 

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDECert;

GO

 

-- Exit out of the database. If we have an active

-- connection, encryption won't complete.

USE [master];

GO

 

-- Turn on TDE

ALTER DATABASE [RecoveryWithTDE]

SET ENCRYPTION ON;

GO

 

 

 

아래 스크립트는 암호화 되어있는 데이터베이스를 확인 한다. Encryption_state 값이 3인 경우는 암호화된 상태를 의미한다.

SELECT DB_Name(database_id) AS 'Database', encryption_state

FROM sys.dm_database_encryption_keys;

 

 

 

TED로 암호화된 데이터베이스를 백업 한다.

BACKUP DATABASE [RecoveryWithTDE]

TO DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak';

GO

 

 

 

  1. 인증서가 없는 새로운 인스턴스에서 TED로 암호회된 데이터베이스 복원

인증서가 없는 곳에서 TDE가 적용된 데이터베이스 백업을 복원한 내용이다. 인증서를 찾을 수 없다는 메시지와 함께 오류가 발생한다.

-- Attempt the restore without the certificate installed

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

  1. 인증서가 없는 새로운 인스턴스에서 인증서를 생성하여 데이터베이스 복원

새로운 인스턴스에서 동일한 이름의 인증서를 생성한다. 마스터키를 생성할 때 기존의 마스터키의 비밀번호와 다르게 생성 하였다. 복원 시도를 하였을 때 인증서를 찾을 수 없다는 메시지와 함께 오류가 발생한다.

-- Let's create the database master key and a certificate with the same name

-- But not from the files. Note the difference in passwords

CREATE MASTER KEY

ENCRYPTION BY PASSWORD = 'SecondServerPassw0rd!';

GO

 

-- Though this certificate has the same name, the restore won't work

CREATE CERTIFICATE TDECert

WITH SUBJECT = 'TDE Cert for Test';

GO

 

-- Since we don't have the corrected certificate, this will fail, too.

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

  1. 복사한 인증서에 권한상속을 부여하여 인증서 복원 후 데이터베이스 복원

인증서를 새로운 인스턴스로 복사하여 인증서에 대한 권한 상속을 수정하여 인증서 복원 작업을 진행하였다. 인증서 파일이 손상되었다는 메시지와 함께 오류가 발생한다.

 

 

 

 

-- Let's drop the certificate and do the restore of it...

-- But without the private key

DROP CERTIFICATE TDECert;

GO

 

-- Restoring the certificate, but without the private key.

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\SQLBackups\TDECert.cer'

GO

 

-- We have the correct certificate, but not the private key.

-- This should fail as well.

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

  1. 마스터키를 사용하여 인증서 복원 후 데이터베이스 복원

마스터키를 사용하여 데이터베이스 암호화에 사용된 인증서를 복원하여 데이터베이스를 정상적으로 복원 하였다.

-- Let's do this one more time. This time, with everything,

-- Including the private key.

DROP CERTIFICATE TDECert;

GO

 

-- Restoring the certificate, but without the private key.

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\SQLBackups\TDECert.cer'

WITH PRIVATE KEY (

FILE = N'C:\SQLBackups\TDECert_key.pvk',

DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'

);

GO

 

-- We have the correct certificate and we've also restored the

-- private key. Now everything should work. Finally!

RESTORE DATABASE [RecoveryWithTDE]

FROM DISK = N'C:\SQLBackups\RecoveryWithTDE_Full.bak'

WITH MOVE 'RecoveryWithTDE' TO N'C:\SQLData\RecoveryWithTDE_2ndServer.mdf',

MOVE 'RecoveryWithTDE_log' TO N'C:\SQLData\RecoveryWithTDE_2ndServer_log.mdf';

GO

 

 

 

TDE로 암호화된 데이터베이스를 복원하기 위해서는 인증서가 반드시 필요하며 인증서는 항상 백업해서 잘 관리할 수 있도록 해야 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2030 Error 17053 타사 네트워크 장치의 SMB 파일 공유 오류 jevida(강성욱) 2017.01.11 1151
2029 최신 SQL Server Native Client 설치하기 jevida(강성욱) 2017.01.11 1784
2028 Sys.dm_os_waiting_tasks를 활용한 실행중인 병렬쿼리 확인 jevida(강성욱) 2017.01.11 1522
2027 DBCC DROPCLEANBUFFERS가 작동하지 않을 때 jevida(강성욱) 2017.01.11 1678
2026 손상된 부트페이지 복구하기 jevida(강성욱) 2017.01.11 1826
2025 Temp table 객체 생성시 세션간 충돌하지 않는 이유 jevida(강성욱) 2017.01.11 1624
2024 SQL Server 데이터베이스 메일 계정 수정 jevida(강성욱) 2017.01.11 2240
2023 XEvent(확장이벤트)를 활용한 활성 로그 모니터링 하기 jevida(강성욱) 2017.01.11 2211
2022 특정 사용자에 대한 트랜잭션 로그 찾기 jevida(강성욱) 2017.01.11 2234
2021 SQL Server I/O 서브시스템 레이턴시 확인 jevida(강성욱) 2017.01.11 1707
2020 실행계획의 물리 및 논리연산자 설명 jevida(강성욱) 2017.01.11 1801
2019 SQL Server Page Life Expectancy (PLE) jevida(강성욱) 2017.01.11 2334
2018 백업 압축과 추적플래그 3042 jevida(강성욱) 2017.01.11 2067
2017 SQL Server에서 MySQL 링크드서버 연결하기 jevida(강성욱) 2017.01.11 4475
2016 SOS_SCHEDURLER_YIELD 대기와 쿼리 식별 jevida(강성욱) 2017.01.11 3406
2015 랜덤 캐릭터 생성하기 jevida(강성욱) 2017.01.11 2212
2014 트랜잭션로그 파일이 손상된 데이터베이스 복원 하기 jevida(강성욱) 2017.01.11 4293
2013 트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기 jevida(강성욱) 2017.01.11 2708
2012 RESOURCE_GOVERNOR_IDLE과 쿼리 성능 jevida(강성욱) 2017.01.11 2024
» TDE 암호화된 데이터베이스 복원 jevida(강성욱) 2017.01.11 2473





XE Login