SQL Server 数据库对称加密

--@version 1.0 2016-11-17
--@description: sql server 2008 aes128位对称密钥对数据列加密与解密
--@symmetric:MySymmetric
--@author:钉牙的大白兔

use test;

--创建密钥加密密钥MySymmetric
create symmetric key MySymmetric with algorithm = aes_128 encryption by password='Hello';

--将要加密的字段转化为varbinary类型,存储密文
select Sno,
    Sname_Encrypt=CONVERT(varbinary(max),Sname),
    Ssex,
    Sdept
into Student_Encrypt
from student
where 1<>1;

--打开密钥并进行加密
open symmetric key MySymmetric decryption by password='Hello';

--复制一张表
insert Student_Encrypt(
    Sno,
    Sname_Encrypt,
    Ssex,
    Sdept
    )
select top 5
Sno,
Sname_Encrypt=ENCRYPTBYKEY(KEY_GUID('MySymmetric'),Sname),
Ssex,
Sdept
from Student;

select * from Student_Encrypt;

加密后的数据库信息：

--打开密钥并进行解密
open symmetric key MySymmetric decryption by password='Hello';

select Sno,
          Sname=CONVERT(nvarchar(max),DECRYPTBYKEY(Sname_Encrypt)),
          Ssex,
          Sdept
from Student_Encrypt;

解密后的数据库：