MSSQL加密数据表(sql2005)

--创建数据库主秘钥
USE adb
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'P8ssw0rd'
GO

--创建存放加密数据的表
CREATE TABLE dbo.SecTable
(ID INT IDENTITY PRIMARY KEY,
DATA NVARCHAR(100))
GO

--创建数字证书，数字证书的私钥被数据库主秘钥加密
CREATE CERTIFICATE TomCert
WITH
    SUBJECT = 'TOM CERTIFICATE',
    EXPIRY_DATE= '01/01/2009'

--创建堆成秘钥，被用证书对其进行加密
CREATE SYMMETRIC KEY Sym_Tom
WITH ALGORITHM = DESX ENCRYPTION BY CERTIFICATE TomCert

--使用证书解开对称秘钥，将数据使用对称秘钥加密后存储与表中，完成后关闭对称秘钥
OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert

INSERT INTO SecTable (DATA) VALUES(EncryptByKey(Key_GUID('Sym_Tom'), N'TOM1'))
CLOSE SYMMETRIC KEY Sym_Tom

--察看被加密的数据内容
SELECT * FROM dbo.SecTable
显示乱码

--使用证书解开堆成秘钥，解密数据表中的数据，完成后关闭对称秘钥
OPEN SYMMETRIC KEY Sym_Tom DECRYPTION BY CERTIFICATE TomCert
SELECT ID, CAST(DecryptByKey(Data) AS NVARCHAR) FROM dbo.SecTable
CLOSE SYMMETRIC KEY Sym_Tom