用SQL Servre 2012 R2设置表中加密

--创建数据库主密钥

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='passW@ord'

GO

--创建证书

CREATE CERTIFICATE TestCert with SUBJECT = 'Test Certificate'

GO

--创建对称密钥

CREATE SYMMETRIC KEY TestSymmetric WITH ALGORITHM = AES_256

ENCRYPTION BY CERTIFICATE TestCert

GO

--使用对称秘钥加密数据

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;

INSERT INTO TBLUser values('张三', ENCRYPTBYKEY(Key_Guid(N'TestSymmetric'), '123456'));

CLOSE SYMMETRIC KEY TestSymmetric;

GO

--查看加密后的数据

SELECT * FROM TBLUser

GO

--解密数据

OPEN SYMMETRIC KEY TestSymmetric DECRYPTION BY CERTIFICATE TestCert;

SELECT Name, CAST(DecryptByKey(password) as varchar(100)) Password FROM TBLUser;

CLOSE SYMMETRIC KEY TestSymmetric;

GO