출처1 : http://www.sqler.com/bColumn/172595
출처2 : http://social.technet.microsoft.com/Forums/ko-KR/sqlserverko/thread/b1cdf310-bd20-4a7f-a666-8336b50d0fe7/
출처3-1 : http://agentsuh79.blogspot.kr/2013/02/ms-sql-1.html
출처3-2 : http://agentsuh79.blogspot.kr/2013/02/ms-sql-2.html
출처4 : http://blog.naver.com/lovebabyyh/80061399950
출처5 : http://purumae.tistory.com/94
출처6 : http://blog.taks.pe.kr/20

/*
MS-SQL 2005 부터 지원되는 자체 암호화 기능 중 대칭키를 사용해 보도록 하자.
다른 말론 컬럼(COLUMN) 암호화...
먼저 마스터(데이터베이스 마스터) 키를 생성하고, 그 다음 대칭키를 사용하기 위해 인증서를 생성하고 다음은 대칭키를 생성한다. 자세한 설명은 그림봐라..
*/

암호화 하는 방법은 4가지야... 개콘 네가지..
1) 비대칭키를 사용하여 암호화 : EncryptByAsmKey()
2) 인증서를 사용하여 암호화 : EncryptByCert()
3) 대칭키를 사용하여 암호화 : EncryptByKey()
4) 패스워드를 사용하여 암호화 : EncryptByPassPharse()
※ 패스워드를 사용한 암호화는 소스에 하드코딩 됨으로 보안이 쥐약이겠지 그래서 패스워드 방식 빼고 사용하면 되겠지?

근데 왜 대칭키를 쓰냐고 물으신다면 비대칭키, 인증서, 대칭키 방법중 가장 암호화/복호화가 빠르니깐 쓴다.. 데이터가 졸라 많은데 암호화 해봐라...속 터지지...
당근 빠르면 암호화 강도는 대칭키나 인증서에 비해 약하겠지?
그리하야... 데이터는 대칭키로.. 대칭키는 인증서로 암호화를 한다고 한다.

자~~ 이정도면 대칭키를 만들어보자.
백문불여일행...

순서를 이래.. 마스터키 생성/백업 > 인증서 생성/백업 > 대칭키 생성/백업

자~~ 따라오너라~

1. 마스터키 생성 / 백업

-- 마스터 키 생성
-- 만약 마스터 키가 기존 것이 있다면 지워야한다 */
-- 지우는건 아래 쿼리로 지운다.
USE [DB_NAME]
go
DROP MASTER KEY
-- 여기까지가 지우는 마스터키 지우는 쿼리
USE [DB_NAME]
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '[MASTER_KEY_PASSWORD]' -- 마스터키 생성 패스워드

/* 마스터 키 백업 */
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MASTER_KEY_PASSWORD'; -- 마스터키 생성시 사용한 패스워드
BACKUP MASTER KEY TO FILE = 'D:\CERTKEYS\MASTER_BACKUP_KEY' --마스터 키를 백업 할 경로 (미리 디렉토리를 생성해 놔야 한다)
ENCRYPTION BY PASSWORD = 'MASTER_BACKUP_KEY_PASSWORD' -- 마스터키 백업 패스워드

/* 마스터 키 조회 */
SELECT * FROM [DB_NAME].sys.symmetric_keys

2. 인증서 생성 / 백업

/* 인증서 생성 */
/* 만료일자(expiry_date) 지정하지 않으면 기본 값 (1년)으로 지정된다... 그래서 그냥 듬뿍 인심 써서 2050년 으로...
*/

CREATE CERTIFICATE [CERTIFICATE_NAME] -- 인증서 이름
ENCRYPTION BY PASSWORD = '[PASSWORD]' -- 인증서를 생성하는 패스워드
WITH SUBJECT = '[SUBJECT]' --이게 무슨인증서요~ 하는 약간 디테일한 이름 지어줌
, EXPIRY_DATE = '12/31/2050' -- 만료일자 지정

/* 인증서 백업 */
BACKUP CERTIFICATE [CERTIFICATE_NAME] TO FILE = 'D:\CERTKEYS\CERTKEYBACKUP' --인증서 백업 경로 (미리 디렉토리를 생성해놔야 한다.)
WITH PRIVATE KEY (
FILE = 'D:\CERTKEYS\PRIVATEKEY' -- 개인키
, ENCRYPTION BY PASSWORD = '[PRIVATE_BACKUP_KEY_PASSWORD]' --개인키 암호화 패스워드
, DECRYPTION BY PASSWORD = '[PRIVATE_RESTORE_KEY_PASSWORD]' -- 개인키 암호 해독용 패스워드
)
/* 인증서 조회 */
SELECT * FROM [DB_NAME].sys.certificates

3. 대칭키 생성 / 백업

/* 대칭키 생성 */
CREATE SYMMETRIC KEY [SYMMETRIC_KEY_NAME] -- 대칭키 이름
WITH ALGORITHM = AES_256 -- 암호 알고리즘이다. 강하면 강할수록 조타~
, IDENTITY_VALUE = 'DATA ENCRYPTION KEY 2013/02/12' -- 대칭키 다시 만들때 사용하는 데이터
, KEY_SOURCE = 'ACCOUNT DATABASE SYMMETRIC KEY' -- 대칭키 다시 만들때 사용하는 데이터
ENCRYPTION BY CERTIFICATE [CERTIFICATE_NAME]

/* 대칭키 조회 */
SELECT * FROM [DB_NAME].sys.symmetric_keys

/*
대칭키는 백업과 복원이 없어..그래서 query문장을 보관해야해... 안그럼 다른 DBMS에 복사한다거나 복구 할 때 복호화를 할 수 없을 수가 있어.
그래서 query문을 그대로 보관해야해 잘... 특히 IDENTITY_VALUE 나 KEY_SOURCE 항목이 중요해 이게 대칭키를 구분해주는 열할을 하는가봐 난 자세히는 몰라
직접 해보고 내용을 추가할 께
*/

/*
MS-SQL 2005 이상 대칭키 사용 방법
대칭키(Symetric)와 인증키(Certificate)를 OPEN하고 패스워드(Password)를 입력한다.
※옵션(Option) : 패스워드는 인증키 생성시 입력 했으면 입력 해야함
KEY_GUID의 값을 넣기 위한 변수 선언 및 Symetric Key의 name 값으로 KEY_GUID를 가져와야한다.
*/
-- 1. Start Session Open
/*
이거 세트로 해야되고 GUID 값 받는거 선언해야하는거 이해하느라 맨땅에 하루 반나절 해딩했다...
해딩은아니고 이곳 저곳 찾아보다가 막 해보다가 알게되었어...
*/
USE [DB_NAME];
OPEN SYMMETRIC KEY [SYMMETRIC_KEY_NAME]
DECRYPTION BY CERTIFICATE [CERTIFICATE_KEY_NAME]
WITH PASSWORD = '[CERTIFICATE_PASSWORD]'
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = (SELECT KEY_GUID FROM SYS.symmetric_keys WHERE name='[SYMMETRIC_KEY_NAME]')
-- 1. End Session Close 여기 까지가 암호화/복호화 준비 과정 [이 구문은 세트(SET) 라고 보면 된다.]

-- 2. Start ENCRYPTION
/*
수정(UPDATE)를 한다고 치자. 물론 그전에 암호화 해서 넣을 컬럼(COLUMN)을 만들어 줘야겠지?
이놈은 암호화기 때문에 문자가 엄청 길어질꺼잖아 그래서 형식(TYPE)을 VARBINARY를 해서 만들어 줘야해
여기서 잘봐야 할 건 저기 ENCRYPTBYKEY 구문이야, 저걸 해줘야 당근 암호화가 되겠지?
삽입/입력(INSERT) 도 마찬가지로 ENCRYPTBYKEY를 해줘야겠지 대칭키는 ENCRYPTBYKEY 를 써줘야하고 다른 암호화 방식은
ENCRYPTBYPHASE(?) 맞나 암튼 그런걸로 해줘야 해.. 일단 대칭키 방법이 가장 빠르다니깐 뭐 이걸 많이 쓰는 것 같어

*/
UPDATE TEST_TABLE SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, NORMAL_COLUMN)
--또는
UPDATE TEST_TABLE SET ENCRYP_COLUMN = ENCRYPTBYKEY(@GUID, '멍청이')
-- 2. End ENCRYPTION (여기 까지가 암호화)

-- 3. Start DECRYPTION
/*
복호시 CAST나 CONVERT 를 사용해서 TYPE 변환을 해줘야 해. WHY? 그렇게 하지 않으면 이상한 문자로 깨져 나와..
그리고 복호시에는 데이터 타입이 문자(String)이면 NVARCHAR, NCHAR, 숫자면 VARCHAR, CHAR로 해야되.
*/
SELECT col1, CONVERT(VARCHAR(MAX),DECRYPTBYKEY(col2)) FROM TEST_TABLE
SELECT col1, CAST(DECRYPTBYKEY(col2) AS VARCHAR(MAX)) FROM TEST_TABLE
-- 3. End SECRYPTION (여기 가지가 복호화)

-- 4. Start Session Close
/*
그리고 다했으면 세션(SESSION)을 받아줘야 해. 무슨 세션이냐고? 당근 암호화 세션이지..
*/
CLOSE SYMMETRIC KEY [SYMMETRIC_KEY_NAME]

-- 4. End Seeion Close

[MSSQL] 2005/2008 컬럼 대칭키 암호화(복호화)