MSSQL 2005 이상 대칭 키 암호화

-- 마스터키 만들기
-- http://msdn2.microsoft.com/ko-kr/library/ms174382.aspx

-- 인증서 만들기
-- http://msdn2.microsoft.com/ko-kr/library/ms187798.aspx

-- 대칭키 생성
-- http://msdn2.microsoft.com/ko-kr/library/ms188357.aspx

-- 데이터를 암호화 해서 넣을때
-- http://msdn2.microsoft.com/ko-kr/library/ms174361.aspx

-- 데이터를 복호화 해서 가지고 올때
-- http://msdn2.microsoft.com/ko-kr/library/ms181860.aspx


예제))

-- 마스터 Key 생성
SELECT * FROM sys.symmetric_keys

IF NOT EXISTS (
SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##'
)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!votmdnjem(22qhekzmrptjfwjd)'   -- 패스워드(22보다 크게 설정)
GO

-- 마스터Key을 삭제 한다.
DROP MASTER KEY
--1. Master Key는 각각의 데이터 베이스에 대하여 분리되어 생성된다.
-- 데이터베이스 상에 하나의 마스터 KEY을 생성할 수 있다.
-- 하나의 데이터베이스 인스턴스에 두개의 마스터 key는 존재할 수 없다.

-- 인증서 생성
SELECT * FROM sys.certificates

IF NOT EXISTS (
SELECT * FROM sys.certificates WHERE name = N'TiwazPasswordFieldCertificate'
)
CREATE CERTIFICATE TiwazPasswordFieldCertificate WITH SUBJECT = 'Password Fields';
GO

DROP CERTIFICATE TiwazPasswordFieldCertificate
go

-- Creating a Symmetric Key
SELECT * FROM sys.symmetric_keys 

/*************** CREATE SYMMETRIC KEY *********************************/
CREATE SYMMETRIC KEY PasswordFieldSymmetricKey   -- Key 명칭
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;

CREATE SYMMETRIC KEY TiwazPasswordFieldSymmetricKey   -- Key 명칭
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;

-- Algorithm
-- DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192 and AES_256
-- Microsoft Windows 2000, Window XP에서는 AES_128, AES_192 and AES_256의 알고리즘은 사용할 수 없다.
-- Win2K인 경우 TRIPLE_DES Algorithm을 사용한다.

OPEN SYMMETRIC KEY PasswordFieldSymmetricKey                -- 대칭 Key
DECRYPTION BY CERTIFICATE TiwazPasswordFieldCertificate;    -- 인증 Key 명칭

-- 암호화 및 복호화시 주의할 점은 원래의 데이터 타입으로 복호화 되어야 한다는 것이다.
-- Varchar 은
SELECT CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터')))
SELECT CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터')))

-- Encrypt 'Varchar' string and then decrypt encrypted data
SELECT
  CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터'))),
  CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), '테스터'))),
  CONVERT(varchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터1'))),
  CONVERT(nvarchar, DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), N'테스터1')))

 

SELECT login_password
     , EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ) ENCRYPT_LOGIN_PASSWORD
     , DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ))  AS NON_CONVERT_LOGIN_PASSWORD
     , CONVERT(varchar,DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'),login_password ))) AS CONVERT_LOGIN_PASSWORD
  from skf_employee

 

-- 파라메터로 암호화 하기
DECLARE @sample_data nvarchar(MAX)
SET @sample_data = N'위의 내용은 SQL SERVER 2005 및 SQL SERVER 2008의 암호화를 설명한 것이다.'

SELECT
CONVERT(nvarchar(MAX), DecryptByKey(EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), @sample_data)))