MSSQL 암호화키(SMK/DMK) 다루기 1/3

by Posted on
 ** SMK(Service Master Key) : 서비스 마스터 키 
  SMK 는 서버 마스터 키로 MSSQL 설치시 자동으로 생성되고 관리되어진다. 정확히 말하면 
  인스턴스생성시 MSSQL 에 의해 자동으로 생성되고 관리되어진다. SMK는 MSSQL 인스턴스가 
  필요한 서비스를 위한 키로 아래와 같이 사용된다.    

  SQL Sever 에 접속한 사용자가 외부 리소스에 접근하기위해 자격증명으로 지정된 사용자 
  인증인 Credential,Linked Server 인증 보안을 위한 Linked Server password, 
  SQL 에 저장된 데이터를 암호화를 보호하는 DMK(Database Master Key). 
  암호화 하는데 쓰인다

 ** DMK(Database Master Key)  데이터 베이스 마스터 키 
  DB 에서 인증서의 비밀키, 비대칭 키, 대칭 키를 보호하기 위해 사용되는 대칭키.
  MSSQL 서버에 있는 DB마다 생성 해 주어야 합니다. DMK 는 대칭키 , 대칭키는 세션기반의 
  키 로서, 사용시 OPEN ~ CLOSE 를 필수로 해줘야하며, 데이터 보호를 위해 쓰이는 키이다.
  만약 SMK 기반으로 DMK 를 작성하면, OPEN ~ CLOSE 가 필요없다.
– 자격증명(Credential)

자격증명(Credential)은 SQL 서버가 C:\ D:\ 와같은 윈도우 리소스 또는 https:// 를 통해 Azure 스토리지를 접근한다던지 등 외주 접속에 자격증명에 필요한 인증에 대한정보를 보호하는데

– Linked Server password

Linked Sever 사용시 자격증명에 대한 정보를 DB 내부에 저장할때, SMK 를 이용해서 암호화 하여 저장한다. 가끔 Linked Server 를 연결하다 보면, 암호해독에 대한 오류가 발생하는 경우가 있는데, 이때는현재 사용중인 DB 정확히는 현재 인스턴스의 SMK 가 손상되었거나, A 인스턴스가 B로 옮겨지면서B 인스턴스 SMK가 A와 일치 하지 않아서 이다.


이때는, 첫번째 방법으로 A 서버가 아직 존재한다면 SMK를 백업하여 B에 리스토어 한다.

① A 서버 인스턴스에서 백업

BACKUP SERVICE MASTER KEY TO FILE = ‘E:\TEMP\SMK_Backup2.key’
ENCRYPTION BY PASSWORD = ‘key!@#!1234’; — 복원할때 사용될 패스워드

② B 서버 인스턴스에서 복원

RESTORE SERVICE MASTER KEY FROM FILE = ‘E:\TEMP\SMK_Backup2.key’
DECRYPTION BY PASSWORD = ‘key!@#!1234’; — 백업할때 입력한 패스워드
FORCE;

② 에서 복원이 안될경우, B 에서 마스터키를 생성하고 해보자

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’;

이때, A서버와 B서버의 버전은 같은지, 그리고 MASTER DB 에서 실행하고 있는지 체크해보자

참고로 재생성된 SMK 를 현재 DMK 가 정상적으로 존재할경우 DMK를 다시 SMK로 암호화 해야한다.
다시 설명하겠지만, DMK 의 경우 대칭키(SYMMETRIC)로 세션기반으로 작동하기때문에
사용전 세션을 열고 사용후 닫기는 필수이다

– DMK 를 SMK 암호화로 보고하기
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘DMK암호’; — DMK 세션 열고
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY — DMK 세션 닫고

두 번째 방법은 SMK 를 재생성하여 손상된 SMK 를 재성성하는 경우이다.

ALTER SERVICE MASTER KEY FORCE REGENERATE

또는 해당 계정의 “계정 암호 다시 설정”을 함으로써 SMK 와의 재연결성을 유지시킬수있다.

ALTER LOGIN “해당계정” WITH PASSWORD = “새비밀번호; — 해당계정의 새비밀번호

만약 기존의 DMK 가 SMK 에 의해 암호화되어 보호되고있다면, DMK 까지 문제가 생길 소지가크니 시중하게 대처하자.


** SMK 조회하기
팁 / SMK 는 MASTER 테이블 에서, SA 권한이 있어야 조회가 가능하다.
아무것도 보이지않으면, 반드시 체크해보자

USE MASTER
SELECT * FROM SYS.SYMMETRIC_KEYS WHERE NAME = ‘##MS_SERVICEMASTERKEY##’;

** SMK 상태 조회하기

SELECT * FROM sys.key_encryptions WHERE key_id = 102;

SMK 가 마스터키(DMK)를 보호하는데 사용되는 경우 이다 그전에, MSSQL 에서 지원하는 암호화의 경우 , 복호가 불가능한 암호화와 , 복호가 가능한 암호화로 나뉘는데 ,

전자의 경우 아래 자주사용는 2가지 방법을 간단하게만 정리하고 넘어가자 예전에 어떤글에서 혹자가 이런 글을 적은 것을 본적이있는데, 암호화가 복호화가 되면 암호화냐? 아무도 풀지못하게 만들어야 암호화 아니냐,, ‘비밀’은 아무도 몰라야 ‘비밀’이데 너만 알고있어 하면서
하는 이야기가 ‘비밀’이 될 수 있느냐 는 이야기와 비슷한거같다. 그래서 MSSQL 에서는 암호화만 가능하고 복호화는 불가능한, 불가능한 이라기보다 복호화를 지원하지 않는PWDENCRYPT , HASHBYTES 2가지 함수에 대해 간단하게 정리해보자

PWDENCRYPT , PWDCOMPARE (PWDENCRYPT 는 varbinayr(128) 필수)

PWDENCRYPT 의 경우 실행될때 마다 계속해서 다른 암호화돈 코드로 암호화하여 돌려준다
이때 타입은 varbinary(128) 필수! 암호화된 코드는 복호화를 지원하지 않아, PWDCOMPARE 함수를 지원한다.

암호화된 문자열과 그 비교할 문자열을 파라미터로 받아 맞으면 1 , 틀리면 0 을 리턴한다.
참고로 PWDENCRYPT 실행할때 마다 같은 문자열에 다른 암호화된 코드로 계속 변경해서 돌려준다

Declare @pwd varbinary(128)
Set @pwd = PWDENCRYPT(‘test’) — PWDENCRYPT 로 암호화
Select PWDCOMPARE(‘test’ , @pwd) — 1 코드와 문자열을 암호화했을대 맞음
Select PWDCOMPARE(‘tost’ , @pwd) — 0 코드와 문자열을 암호화했을대 틀림

HASHBYTES

HASHBYTES ( ”, { @input | ‘input’ } )
::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512
HASHBYTES 경우지정된 알고리즘으로 해쉬해서 리턴하는데, 그값은 항상 같다.
Select HASHBYTES(‘SHA2_256’ , ‘test’) — SHA2_256 알고리즘/ 해쉬암호화 한다.

모든 프로그램이 그렇치만 버전이 올라가면서, 여전히 지원되지 않는것과 차후지원하지 안은 것들이 존재하는데
아쉽게도 암호화 알고리즘에 대한것들이 그런것 같다.
Microsoft 공식문서에 보면 PWDENCRYPT , PWDCOMPARE 사용도 되도록 하지말고 HASHBYTES 권고하고 있고,

HASHBYTES 의 SHA , SHA1 의 경우도 이젠 해킹이 가능해지기때문에 SHA256 또는 SHA512로 사용하도록 권고하고있다.

SMK 의 두번째 용도에 대해 알아보자. 일반적으로 MSSQL 의 데이터 보호를 위해 여러가지 키가
존재하는데 그 키의 존재가 계층적이다. 이는 한 개의 계층이 무너지거나 변경되면, 영원히 복호가 불가능한 데이터로 남게된다. 그런데 그 키들을 모두 보호하는 최상위 계층에 있는 키가 바로 SMK 이다.

SMK는 SQL Server 인스턴스 전반에 걸쳐 Service 에 필요한 보안에 정보를 보호하는데 사용된다고 위에에서 언급했다. SMK 하위에 실제 데이터를 보호하기 위한 또 하나의 마스터키가 존재하는데 DMK(Data Master Key) 라고 한다. 일반적으로 그냥 ‘마스터키’ 라고 호칭하면 DMK 를 말하기도 한다.

DMK 는 인증서 및 대칭키를 보호하는데 사용되며, DB 마다 한 개씩 가질 수 있다.
DMK는 생성,재생성,삭제가 가능하며, SMK 는 인스턴스 마다 1개 , DMK 는 DB 마다 1개가 존재한다.(DMK는 생성시 ‘사용자암호’ 와 Triple DES를 사용하여 암호화 되고 Master DB 에 저장된다.)

** DMK 조회 : DMK 는 DB 마다 한개씩 생성 할 수 있으므로 해당 DB 에서 조회해야한다

USE [조회할DB]
SELECT * FROM sys.symmetric_keys WHERE name = ‘##MS_DatabaseMasterKey##’;

** 데이터 암호화는 인증서, 대칭키, 비대칭 로만 가능하다!
참고로, 사용자는 SMK 와 DMK를 이용해서 직접 데이터를 암호화하거나 복호화 할수는없다.
데이터 암호화하는 인증서, 대칭키, 비대칭키 3가지 방법에 의해 데이터가 암호/복호화 된다.
DMK는 인증서, 대칭키, 비대칭키를 보호하기위해 DMK의 알고리즘으로 키들을 다시 암호화 하는데 쓰이는 대칭키 이다.(SMK도 대칭키)

조금더 실무적으로 접근하자면 비대칭키는 DMK 의 아무 영향을 받지 않는다,
MSDN 에서도 DMK 를 이용해 인증서 ,대칭키 , 비대칭키를 보호 하기위해서 사용되는 대칭키라고 작성되어 있으나,DMK 를 생성후 비대칭키를 생성하면 DMK 에의해 비대칭키를 암호화하여 저장하지만 실제 암호/복호화 하는데 아무 문제가없었다.

** DMK 와 비대칭키의 실무적 연관성

ㆍDMK 생성 → 비대칭키 생성 → 암호화 → 복호화 : 문제없음
ㆍDMK 생성 → 비대칭키 생성 → 암호화 → DMK 삭제 → 복호화 : 문제없음
ㆍDMK 생성 → DMK 삭제 → 비대칭키 생성 → 암호화 → 복호화 : 문제없음
ㆍ비대칭키 생성 → 암호화 → DMK 생성 → 복호화 : 문제없음
ㆍ비대칭키 생성 → DMK 생성 → 암호화 → 복호화 : 문제없음
ㆍ비대칭키 생성 → DMK 생성 → 암호화 → DMK 삭제 → 복호화 : 문제없음

  비대칭키는 DMK에 의존적이지 않으며, 인증서와 대칭키를 위해 존재하는 키라고 생각하면 된다.
  대칭키로 암호화된 데이터는 대칭키가 유출되면, 민감한 데이터가 쉽게, 복호화되어 보여질 수 있다.
 
  대칭키를 DMK로 암호화하여 보호하고, DMK를 SMK로 암호화 하여, 제3자 침입 또는 데이터 유출을, 
  최소한의 권한으로 누군가가 인증서는 접근 할 수 있더라도, DB 는 접근해야 해독이가능하게(DMK), 
  DB 는 접근이 가능하더라도 DB 서버에 접근이가능해야 해독이 가능하게(SMK) 2중,3중 보호층을 구성 
  할 수 가 있다.

  반드시 DMK 또는 SMK 에 의존하는 인증서 , 대칭키만 만들 수 있는 것은 아니다,
  인증서만 또는 대칭키만, DMK + 인증서 , SMK + DMK + 인증서 등 원하는 조합으로 계층을 두어
  두텁게 암호화를 구성할 수 있다.

** 데이터 암호/복호 방법을 조합해서 나열해보면

  ① SMK + DMK + 인증서
  ② SMK + DMK + 인증서 + 대칭키
  ③ DMK + 인증서
  ④ DMK + 인증서 + 대칭키
  ⑤ 인증서 - 단독 생성이 불가 (DMK 가 있어야 인증서 생성 가능 DMK 필요 필수!)
  ⑥ 대칭키
  ⑦ 대칭키 + 대칭키
  ⑧ 대칭키 + 비대칭키
  ⑨ 대칭키 + 인증서
  ⑩ 비대칭키

인증서 생성시 DMK 없이는 인증서 생성이 불가하므로 ⑤번 인증서는 DMK 가 있어야 인증서 생성이 가능하기때문에(③ DMK + 인증서 동일) 제외, ⑧ 대칭키 + 인증서 경우도,
DMK 가 있어야 인증서 생성이 가능하므로 하므로 “DMK + 인증서 + 대칭키”와 동일 2가지 방법을 제외하면 총 7가지 방법이(조합) 가능하다.

Published by pluginn

답글 남기기

이메일 주소는 공개되지 않습니다. 필수 필드는 *로 표시됩니다