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

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

TDE 암호화 사용하기

jevida(강성욱) 2016.10.11 03:34 Views : 3187

TDE 암호화 사용하기

 

  • Version : SQL Server 2008, 2008R2, 2012

 

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

 

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

 

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

 

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

 

다음 그림은 TDE의 암호화 아키텍처 이다.

 

 

TDE(투명한 데이터 암호화)를 사용하기 위해서는 다음의 단계를 수행 해야 한다.

  • 마스터키 생성
  • 마스터 키로 보호되는 인증서를 만들거나 얻기
  • 데이터베이스 암호화 키를 만들고 인증서로 키 보호
  • 암호화를 사용하도록 데이터베이스 설정

 

 

다음 스크립트는 MyDatabase라는 샘플 데이터베이스에서 TDE를 사용하는 방법이다. 스크립트에서 패스워드는 시스템에서 요구하는 복잡도를 만족여야 한다.

-- The master key must be in the master database.

USE Master;

GO

 

 

-- Create the master key.

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='qwer!@#$asdf5678';

GO

 

-- Create a certificate.

CREATE CERTIFICATE MySQLCert

WITH SUBJECT='MyDatabase DEK';

GO

 

-- Use the database to enable TDE.

USE MyDatabase

GO

 

-- Associate the certificate to MyDatabase.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MySQLCert;

GO

 

-- Encrypt the database.

ALTER DATABASE MyDatabase

SET ENCRYPTION ON;

GO

 

암호의 복잡도가 시스템 요구 사항을 만족하지 못하였을 경우 다음과 같은 에러가 발생 한다.

메시지 15118, 수준 16, 상태 1, 4

암호의 유효성을 검사하지 못했습니다. 암호가 복잡하지 않기 때문에 Windows 정책 요구 사항에 맞지 않습니다.

메시지 15581, 수준 16, 상태 1, 3

작업을 수행하기 전에 데이터베이스에서 마스터 키를 만들거나 세션의 마스터 키를 여십시오.

메시지 15151, 수준 16, 상태 1, 3

인증서 'MySQLCert'() 없거나 권한이 없어서 찾기할 없습니다.

메시지 33106, 수준 16, 상태 1, 3

데이터베이스 암호화 키가 설정되어 있지 않으므로 데이터베이스 암호화 상태를 변경할 없습니다.

메시지 5069, 수준 16, 상태 1, 3

ALTER DATABASE 문이 실패했습니다.

 

정상적으로 키가 생성되었을 때의 경고 구문이다. 인증서가 백업되지 않았다는 경고가 발생한다.

경고: 데이터베이스 암호화 키를 암호화하는 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 없습니다.

 

위의 스크립트에서 다음과 같은 구문에서 암호화 알고리즘을 변경하여 사용할 수 있다. AES_128, AES_192, AES_256, TRIPLE_DES_3KEY를 사용할 수 있다.

-- Associate the certificate to MyDatabase.

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE MySQLCert;

GO

 

다음은 암호화된 데이터베이스를 복원 및 이동하는 방법이다. 암호화된 데이터베이스에는 인증서 없이 액세스 또는 복원할 수 없기 때문에 인증서를 백업하는 것은 매우 중요하다. 따라서 데이터베이스의 인증서 뿐만 아니라 서버 인증서의 백업도 유지 관리 해야 한다.

 

다음 스크립트는 인증서를 백업한다.

USE Master

GO

 

BACKUP CERTIFICATE MySQLCert

TO FILE = 'C:\temp\MySQLCert'

WITH PRIVATE KEY (file='C:\temp\MySQLCertKey',

ENCRYPTION BY PASSWORD='qwer!@#$asdf5678')

 

 

 

다른 SQL Server로 데이터베이스를 이동하거나 복원하려면 새 서버 인스턴스에 인증서를 복원해야 한다. 다음 스크립트는 인증서를 이동하는 방법이다.

USE Master

GO

 

-- Create a new master key.

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'MyNewStrongPassword'

 

-- Restore the certificate.

CREATE CERTIFICATE MySQLCert

FROM FILE='c:\temp\MySQLCert'

WITH PRIVATE KEY (

FILE = 'c:\temp\MySQLCertKey',

DECRYPTION BY PASSWORD='qwer!@#$asdf5678')

 

 

TDE를 모니터링 하는 방법은 sys.certificates 카탈로그 뷰 및 sys.dm_database_encryption_keys 동적 관리 뷰에서 확인 할 수 있다. sys.certificates에서는 생성된 인증서를 볼 수 있다. 다음 예제 스크립트를 통해 알아 보자.

USE master

GO

 

SELECT * FROM sys.certificates

 

-- encryption_state = 3 is encrypted

SELECT * FROM sys.dm_database_encryption_keys

WHERE encryption_state = 3;

 

 

 

TDE를 사용할 때 몇 가지 제한 사항이 있다. 초기 데이터베이스 암호화, 키 변경 또는 데이터베이스 암호 해독 중에는 다음 작업을 수행 할 수 없다.

  • 데이터베이스의 파일 그룹에서 파일 삭제
  • 데이터베이스 삭제
  • 데이터베이스를 오프라인으로 설정
  • 데이터베이스 분리
  • 데이터베이스 파일 또는 파일 그룹을 READ ONLY 상태로 전환

 

CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY 또는 ALTER DATABASE...SET ENCRYPTION 문 실행 중에는 다음 작업을 수행할 수 없다.

  • 데이터베이스의 파일 그룹에서 파일 삭제
  • 데이터베이스 삭제
  • 데이터베이스를 오프라인으로 설정
  • 데이터베이스 분리
  • 데이터베이스 또는 파일 그룹을 READ ONLY 상태로 전환
  • ALTER DATABASE 명령 사용
  • 데이터베이스 또는 데이터베이스 파일 백업 시작
  • 데이터베이스 또는 데이터베이스 파일 복원 시작
  • 스냅숏 만들기

 

다음 작업 또는 상태에서는 CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY 또는 ALTER DATABASE...SET ENCRYPTION 문을 실행할 수 없다.

  • 데이터베이스가 읽기 전용이거나 데이터베이스에 읽기 전용 파일 그룹이 포함된 경우
  • ALTER DATABASE 명령이 실행 중인 경우
  • 데이터 백업이 실행 중인 경우
  • 데이터베이스가 오프라인이거나 복원 상태인 경우
  • 스냅숏이 진행 중인 경우
  • 데이터베이스 유지 관리 태스크

 

 

각자의 비즈니스 환경에 맞는 암호화 방법을 선택하여 데이터를 보호 할 수 있도록 하자.

 

[참고자료]

 



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

No. Subject Author Date Views
1890 장기 트랜잭션 확인 및 경고 설정 jevida(강성욱) 2016.10.11 1416
1889 SQL 데이터 수집툴 DiagManager 사용법 jevida(강성욱) 2016.10.11 1317
1888 Windows Server 2012 R2 그룹 관리 서비스 계정 및 SQL 서버 jevida(강성욱) 2016.10.11 1833
1887 Kerbros 구성 관리자 툴 jevida(강성욱) 2016.10.11 1160
1886 SQL Server Geography 및 Geometory 데이터 형식 jevida(강성욱) 2016.10.11 2317
1885 SQL Server를 이용한 신용카드 보안 -조직이 PCI DSS 준수를 달성 하기 위한 요건 jevida(강성욱) 2016.10.11 1361
» TDE 암호화 사용하기 jevida(강성욱) 2016.10.11 3187
1883 SQL Server 암호화 하기 jevida(강성욱) 2016.10.11 2688
1882 의도하지 않은 분산트랜잭션 사용 jevida(강성욱) 2016.10.11 872
1881 Affinity I/O Mask jevida(강성욱) 2016.10.11 1159
1880 LPE_BATCH 스핀락 jevida(강성욱) 2016.10.11 880
1879 SQL Server 17953 오류 jevida(강성욱) 2016.10.11 973
1878 SQL 연결 18056 오류 jevida(강성욱) 2016.10.11 1359
1877 SQL 2008 R2 Sp1 적용과 9013 오류 - 포맷 섹터 크기에 따른 오류 jevida(강성욱) 2016.10.11 1742
1876 JDBC 로깅 파일 위치 설정 jevida(강성욱) 2016.10.11 956
1875 NUMA Node 메모리 블록 jevida(강성욱) 2016.10.11 1046
1874 AppDomain unloading 오류 로그 – CLR 오류 jevida(강성욱) 2016.10.11 1187
1873 SQL Server IO and Latch 설명 jevida(강성욱) 2016.10.11 3071
1872 마지막 백업 시간 및 DBCC 확인 [1] jevida(강성욱) 2016.10.08 1589
1871 DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1906





XE Login