SQL Server字段级加解密

本文介绍如何对SQL Server字段级加解密，并且可以跨2台SQL Server服务器。使用AES_256对称加密算法，带证书校验。

所有操作都是针对某个库，非实例。

 

1. 查询源端和目标端SQL Server是否已创建DMK（database master key）以及证书certificate

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

select * from sys.certificates

        

 

1. 如果1查询没有DMK和certificate，在源端和目标端创建同样的key

 

--drop SYMMETRIC KEY [key_encryption]

--drop CERTIFICATE [cert_keyProtection]

--drop master key

--创建Master key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'User123$';

GO

--创建证书（过期日期为2099）

CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection',

      START_DATE = '01/01/2017',

      EXPIRY_DATE = '12/31/2099';

GO

--创建对称秘钥

CREATE SYMMETRIC KEY [key_encryption] WITH

    KEY_SOURCE = 'User123$',

    ALGORITHM = AES_256,

    IDENTITY_VALUE = 'User123$'

    ENCRYPTION BY CERTIFICATE [cert_keyProtection];

GO

 

         创建完毕后，查询系统视图确认

         SELECT * FROM sys.symmetric_keys;

select * from sys.certificates;

 

 

 

 

 

当把加密后的密文是varbinary数据类型保存为varchar类型时，如0x4041424320202020,  其中202020是空格的ASCII码，如果ANSI_PADDING 设为OFF,字符串末尾的空格会被sqlserver截掉，从而变成0x40414243，这样回导致数据丢失，没法正常解密。故而需要设置ANSI_PADDING 设为ON：

右键数据库->属性->选项

 

 

1. 在源端数据库创建测试表

   CREATE TABLE [dbo].[A](

    [NAME] [varchar](100) NULL,

    [ID] [varbinary](500) NULL,

    [OUTPUT] [varchar](500) NULL

)

4  创建测试表,并插入一条记录：

     INSERT INTO Operation..A VALUES('123456789024300',NULL,NULL)

5       进行加密处理，将密文存放在ID上,并测试验证 ID列，可行:

    OPEN SYMMETRIC KEY key_encryption 

DECRYPTION BY CERTIFICATE cert_keyProtection; 
    UPDATE Operation..A 
    SET OUTPUT = dbo.BinToHexString(EncryptByKey(Key_GUID('key_encryption'),NAME)); 
    GO 
    CLOSE SYMMETRIC KEY key_encryption 
    GO

解密：

OPEN SYMMETRIC KEY key_encryption

DECRYPTION BY CERTIFICATE cert_keyProtection;

GO

SELECT  NAME,ID,convert(varchar(500),DecryptByKey(dbo.HexStringToBin(OUTPUT)))

from A;   

GO

CLOSE SYMMETRIC KEY key_encryption