SQL Server 암호화 계층

   

TechNet BOL을 참고하여 SQL Server 암호화 계층 내용 정리.(주관적인 내용은 없음)

   

SMK: Service Master Key(서비스 마스터 키)

DMK: Database Master Key(데이터베이스 마스터 키)

EKM: Extensible Key Management(확장가능 키 관리)

   

** 대칭키와 비대칭키(symmetric and asymmetric) 개념

참고) http://blog.daum.net/01051716075/3172701

   

1. 대칭 키

   - 암호화 키와 복호화 키가 같은 암호화 방식(비밀키 사용)

   - 키가 안전한 전송방식(비밀 통신망, 직접전달)을 통해 사전에 전달되어야 함

   - 암호화 및 복호화가 빠르지만 키 관리에 어려움이 있음

   - 대표적으로 DES가 있음

      

2. 비대칭 키(공개키 & 비밀키)

   - 암호화 키와 복호화 키가 서로 다름

   - 두개의 키(공개키, 비밀키) 분리

   - 암호화 할 때 사용하는 공개키는 공개된 시스템에 전화번호처럼 공개하고 복호화 할 때 사용하는 비밀키는 안전하게 보관해야 함.

   - 공개키 암호시스템은 대칭키 암호 시스템에 비해 키의 길이가 길고 알고리즘 수행속도가 매우 느리기 때문에 긴 평문을 암호화하는데 부적절

   

   

암호화 계층 (http://technet.microsoft.com/ko-kr/library/ms189586.aspx)

적용 대상: SQL Server 2008, 2008 R2, 2012

   

SQL Server는 계층적 암호화 및 키 관리 인프라로 데이터를 암호화.

   

어느 암호화 방식을 사용할 것인가?

- 성능: 대칭키 > 인증서 또는 비대칭 키

- 보안: 대칭키 < 인증서 또는 비대칭 키

- SMK & DMK는 모두 대칭키 구조

   

↓아래 그림은 암호화 계층의 각 계층이 그 아래의 계층을 암호화하는 모습.

계층의 시작 부분에 대한 액세스는 일반적으로 암호(PW)로 보호.

   

From <http://technet.microsoft.com/ko-kr/library/ms189586.aspx>

   

   

↓같은 정보를 서로 다른 방법으로 접근하는 예

   

From <http://technet.microsoft.com/ko-kr/library/ms189586.aspx>

   

   

   

참고) DPAPI (필요해 보이는 내용만 스크랩, 전체 내용은 아래 URL 참조)

How to troubleshoot the Data Protection API (DPAPI) (http://support.microsoft.com/kb/309408)

   

The Data Protection API (DPAPI) helps to protect data in Windows 2000 and later operating systems. DPAPI is used to help protect private keys, stored credentials (in Windows XP and later), and other confidential information that the operating system or a program wants to keep confidential.

   

- What DPAPI Can Protect (DPAPI에서 보호할 수 있는 항목)

DPAPI helps protect the following items:

• Web page credentials (for example, passwords)

• File share credentials

• Private keys associated with Encrypting File System (EFS), S/MIME, and other certificates

• Program data that is protected using the CryptProtectData() function

   

Example: Certificates and Private Keys

This section describes the difference between personal data and confidential information that DPAPI helps protect. The following list describes the placement of data during an import operation of a certificate and it describes the private key that is associated with that certificate to the user's personal store:

•The certificate is encoded as a binary large object and stored as a binary value in the following file location:

%Userprofile%\Application Data\Microsoft\SystemCertificates\My\Certificates

• Note that the location of the registry key is in the local user's profile. This placement makes sure that only the logon user has access to their own certificates in typical circumstances.

• Certificates are not protected by DPAPI by any default Windows mechanisms. An Access Control List (ACL) is used to define who may load the user's hive and who may read the certificates that are stored in the hive.

• The private key that is associated with the certificate is encrypted by DPAPI and saved (in an encrypted form) in a key container as an individual file in the user's profile in the following folders:

   

◦For RSA Keys:

%Userprofile%\Application Data\Microsoft\Crypto\RSA\User SID

◦For DSA Keys:

%Userprofile%\Application Data\Microsoft\Crypto\DSA\User SID

   

- How DPAPI Works

The terms and concepts that are described in this section have been simplified for the purposes of clarity in the context of this article. Some level of detail has been omitted. For example, this article discusses a value that is derived from the user's password, but it does not describe the details of the algorithm that is used to derive the value. For a detailed description of how DPAPI works, view the Windows Data Protection white paper. To view this white paper, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/ms995355.aspx

   

DPAPI is a function that is used by programs and various operating system components to help protect data for a user. The operation of DPAPI is not visible to the user. DPAPI helps protect data in the security context of the user who runs the program.

   

DPAPI helps protect confidential information by using value data derived from a pseudo-random 512-bit number named a master key.

(DPAPI는 마스터 키라는 512비트 의사 난수(pseudo-random number)에서 파생된 값 데이터를 사용하여 기밀 정보를 보호합니다.)

Windows Server 2003 domain controllers use a 2048-bit RSA key, but only when the domain is running in domain functional level 2 or Windows Server 2003 mode. Each user account has one or more randomly generated master keys. The number of master keys depends on the age of the user's profile. Master keys are renewed at regular intervals. By default, this value is every 90 days.

   

Because master keys contain the data that is required to decrypt all the user's confidential information, the master keys must be protected. They are protected using a value that is derived from the user's password. The password is a unique value that only a user knows. Because the master key is actually encrypted using a value that is derived from the user's password, this value is used interchangeably with the user's password in the descriptions presented in this article.