SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 23. 10:37
반응형

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/

 

2015-04-08 / 강성욱 / http://sqlmvp.kr

 

Sqlserver, mssql, DB보안, DB암호화, SQL 암호화, TDE, DB Encryption, SQL암호화, DB 복원, 인증서 복원, 인증서 백업, MASTER KEY 백업

 

반응형

'SQL Server > SQL Server Tip' 카테고리의 다른 글

트랜잭션 로그 백업을 읽고 트랜잭션 발생 시간 및 사용자 찾기  (0) 2015.07.23
RESOURCE_GOVERNOR_IDLE과 쿼리 성능  (0) 2015.07.23
재해복구를 위한 SQL Server 역할 가져오기  (0) 2015.07.23
비관리자 계정에 Profiler 실행 권한 부여하기  (0) 2015.07.23
SQL Server Agent 공유 일정 생성하기  (0) 2015.07.23

'SQL Server/SQL Server Tip'의 다른글

  • 현재글TDE 암호화된 데이터베이스 복원

관련글

티스토리툴바