mssql server 过程加密怎么解密 sqlserver数据库加密解密

一．数据库配置
１．为数据库创建数据库主密钥
脚本代码：
USE Northwind 
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'
GO　　数据库名    Northwind
    密码        P@ssw0rd
 
２．对数据库主密钥进行备份
脚本代码：
      USE Northwind
GO
BACKUP MASTER KEY 
    TO FILE = 'C:/DBFile/DMK.bak'
    ENCRYPTION BY PASSWORD = 'P@ssw0rd!@'
GO数据库名    Northwind
    出力文件    C:/DBFile/DMK.bak
 
３．让SQL2005创建自签名的证书
脚本代码：
USE Northwind
GO
CREATE CERTIFICATE cert_TestCert1 
    ENCRYPTION BY PASSWORD = 'P@ssw0rd'
    WITH SUBJECT = 'TestCert1',
    START_DATE = '1/31/2008',
    EXPIRY_DATE = '1/31/2009'
GO    证书名      cert_TestCert1
    密码        P@ssw0rd
    有效期间    1/31/2008 ～ 1/31/2009
 
４．备份导出证书和私钥
脚本代码：
BACKUP CERTIFICATE cert_TestCert1 
    　　　TO FILE = 'c:/DBFile/TestCert1.cer' 
   　 　WITH PRIVATE KEY 
       　 (DECRYPTION BY PASSWORD = 'P@ssw0rd' , 
        　 FILE = 'c:/DBFile/TestCert1_pvt' , 
        　 ENCRYPTION BY PASSWORD = 'Pa$w0rd')    证书出力文件       c:/DBFile/TestCert1.cer
    证书密码           P@ssw0rd
    私钥密码           Pa$w0rd
 
５．使用证书加密、解密数据（测试是否配置成功）
脚本代码：
DECLARE @cleartext varbinary(200)
DECLARE @cipher varbinary(200)
SET @cleartext = CONVERT(varbinary(200), 'Test text string')
SET @cipher = EncryptByCert(Cert_ID('cert_TestCert1'), @cleartext)
SELECT @cipher
SELECT CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert1'), @cipher, N'P@ssw0rd')) AS [ClearText]    执行结果下图所视：
    
※ 第一行为加密后的数据，第二行为解密后的数据
 
６．对称密钥做成
    脚本代码
CREATE SYMMETRIC KEY TestSymmetricKey
      WITH ALGORITHM = TRIPLE_DES
      ENCRYPTION BY CERTIFICATE cert_TestCert22
    用证书对对称密钥加密
    对称密钥名   TestSymmetricKey
    证书名       cert_TestCert22
二．对加密数据的操作（证书加密）
1．测试表的做成
脚本代码：
      USE Northwind
CREATE TABLE dbo.EmpSalary(
    EmpID int, 
    Title nvarchar(50),
    Salary varbinary(500)
)
GO   注：字段Salary为要加密的字段，要加密的字段必须为varbinary类型（二进制类型）
 
２．插入数据
    SQL文：
　　　　Insert into 
EmpSalary
values
　 ( 1,'CEO',
EncryptByCert(Cert_ID('cert_TestCert1'),
CONVERT(varbinary(200), '6000'))
            )
    注：要把字符串类型的数据先转换成二进制类型后再加密
 
３．更新数据
    SQL文：
        Update
EmpSalary
Set
Salary = 
EncryptByCert(Cert_ID('cert_TestCert1'),
CONVERT(varbinary(200), '6980'))
 
４．删除数据
SQL文：
        delete from EmpSalary
注：无特殊处理
 
５．抽出数据
SQL文：
       Select 
  EmpID,
  Title,
CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert22'), Salary, N'P@ssw0rd')) Salary 
from EmpSalary
where
 CONVERT(varchar(200), DecryptByCert(Cert_ID('cert_TestCert22'), Salary, N'P@ssw0rd')) = '6000'
 
三．对加密数据的操作（对称密钥加密）
１．打开和关闭对称密钥
脚本代码
   OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'
   CLOSE SYMMETRIC KEY TestSymmetricKey
注：在使用对称密钥前打开密钥，使用结束后关闭
 
２．抽出数据
　　SQL文：
　　　　OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'
　　　　Select EmpID,Title, CONVERT(varchar(200), 
DecryptByKey(Salary)) Salary from EmpSalary
　　　　CLOSE SYMMETRIC KEY TestSymmetricKey
 
３．插入数据
　　SQL文：
        OPEN SYMMETRIC KEY TestSymmetricKey DECRYPTION BY CERTIFICATE cert_TestCert22 with PASSWORD = 'P@ssw0rd'
Insert into EmpSalary values (  1 ,'CEO',
     EncryptByKey(Key_GUID('TestSymmetricKey'), '8741'))
        CLOSE SYMMETRIC KEY TestSymmetricKey