데이터베이스 개발자 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
» TDE 암호화된 데이터베이스 복원 jevida(강성욱) 2017.01.11 2474
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2286
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3127
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2147
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2361
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3324
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4092
2004 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2463
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3040
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3761
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5305
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1612
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1003
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1421
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1361
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3734
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1663
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1586
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574





XE Login