http://blog.csdn.net/dba_huangzj/article/details/7673714
--创建对称密钥
USE AdventureWorks
GO
CREATE SYMMETRIC KEY SymKey123
WITH ALGORITHM=AES_128 ENCRYPTION BY PASSWORD='P@ssw0rd'
GO
--注意事项:在启用时,需要先OPEN SYMMETRIC KEY 搭配密钥密码,否则所产生的数据都会是null值。而且需要搭配Key_GUID函数来使用
--打开对称密钥
OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
--进行数据加密
SELECT * ,ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
FROM Person.Address
--检查加密后长度,利用datalength()函数
SELECT DATALENGTH(ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(MAX ),AddressLine1)))
FROM Person.Address
GO
--把加密后数据更新到原来另外的列上
UPDATE Person.Address
SET AddressLine2=ENCRYPTBYKEY(KEY_GUID('SymKey123'),CONVERT(VARCHAR(max),AddressLine1))
--解密:解密过程同样需要OPEN SYMMETRIC KEY ,且需要利用DECRYPTBYKEY 和CONVERT函数
OPEN SYMMETRIC KEY SymKey123 DECRYPTION BY PASSWORD='P@ssw0rd';
SELECT AddressID,CONVERT(VARCHAR(MAX ) ,CONVERT (VARCHAR(MAX ),DECRYPTBYKEY(AddressLine2)))
FROM Person.Address