-- 1. master key 확인
-- master key는 한개만 생성됨
select * from sys.symmetric_keys
--select * from sys.symmetric_keys where name='##MS_DatabaseMasterKey##'
--create master key encryption by password = '$EncryptionPassword12'

-- 2. 인증서 생성
-- 대칭키 생성 및 테이블 컬럼 암호화
select * from sys.certificates
--select * from sys.certificates where name=N'PasswordFieldCertificate'
--create certificate PasswordFieldCertificate with subject ='PasswordFields'

-- 3. 대칭키 생성
create symmetric key PasswordFieldSymmetricKey with algorithm = AES_256 encryption by certificate PasswordFieldCertificate

select * from ST3002
alter table ST3002 add EncryptedPasswd nvarchar(100);

-- 4. 암호화
open symmetric key PasswordFieldSymmetricKey
decryption by certificate PasswordFieldCertificate;

update st3002 set EncryptedPasswd = EncryptByKey(Key_GUID('PasswordFieldSymmetricKey'), PASSWD);
SELECT PASSWD, EncryptedPasswd FROM ST3002

-- 5. 복호화
open symmetric key PasswordFieldSymmetricKey
decryption by certificate PasswordFieldCertificate;
SELECT PASSWD,
EncryptedPasswd,
convert(nvarchar,DecryptByKey(EncryptedPasswd))

--convert(nvarchar,DecryptByKey(EncryptedPasswd2))
FROM ST3002

-- test
select convert(varchar, decryptbykey(encryptbykey(key_guid('PasswordFieldSymmetricKey'),'varchar'))),
convert(nvarchar, decryptbykey(encryptbykey(key_guid('PasswordFieldSymmetricKey'),'varchar'))),
convert(varchar, decryptbykey(encryptbykey(key_guid('PasswordFieldSymmetricKey'),N'Nvarchar'))),
convert(nvarchar, decryptbykey(encryptbykey(key_guid('PasswordFieldSymmetricKey'),N'Nvarchar')))