SQL Server 数据加密技

数据用数字方式存储在服务器中并非万无一失。实践证明有太多的方法可以智取SQL Server认证保护，最简单的是通过使用没有口令的sa账号。尽管SQL Server远比它以前的版本安全，但攻击者还是有可能获得存储的数据。因此，数据加密成为更彻底的数据保护战略，即使攻击者得以存取数据，还不得不解密，因而对数据增加了一层保护。

 

SQL Server通过将数据加密作为数据库的内在特性解决了这个问题。它除了提供多层次的密钥和丰富的加密算法外，最大的好处是用户可以选择数据服务器管理密钥。SQL Server服务器支持的加密算法如下：

⑴ 对称式密钥加密（Symmetric Key Encryption）：

对称式加密方式对加密和解密使用相同的密钥。通常，这种加密方式在应用中难以实施，因为用同一种安全方式共享密钥很难。但当数据储存在SQL Server中时，这种方式很理想，你可以让服务器管理它。SQL Server提供RC4、RC2、DES和AES系列加密算法。

⑵ 非对称密钥加密（Asymmetric Key Encryption）：

非对称密钥加密使用一组公共/私人密钥系统，加密时使用一种密钥，解密时使用另一种密钥。公共密钥可以广泛的共享和透露。当需要用加密方式向服务器外部传送数据时，这种加密方式更方便。SQL Server支持RSA加密算法的512位、1,024位和2,048位密钥强度。

⑶ 数字证书（Certificate）：

数字证书是一种非对称密钥加密，但是，一个组织可以使用证书并通过数字签名将一组公钥和私钥与其拥有者相关联。SQL Server支持“因特网工程工作组”(IETF) X.509 版本 3 (X.509v3) 规范。一个组织可以SQL Server 使用外部生成的证书，或者可以使用 SQL Server 生成证书。

(4) pwdencrypt,pwdcompare加密(SQL SERVER本身提供的加密函数)

SQL Server 采用多级密钥来保护它内部的密钥和数据，如下图所示：

 

图1 SQL Server采用多级密钥保护它内部的密钥和数据

图中顶层的服务主密钥，安装SQL Server新实例时自动产生和安装，用户不能删除此密钥，但数据库管理员能对它进行基本的维护，如备份该密钥到一个加密文件，当其危及到安全时更新它，恢复它。

 

服务主密钥由DPAPI（Data Protection API）管理。DPAPI在Windows 2000 中引入，建立于Windows的Crypt32 API之上。SQL Server 管理与DPAPI的接口。服务主密钥本身是对称式加密，用来加密服务器中的数据库主密钥。

 

图中引出箭头的密钥或服务用于保护箭头所指的密钥。所以分服务主密钥（service master key）数据库主密钥（database master keys），而数据库主密钥又保护证书（certificates）和非对称密钥（asymmetric keys）。而最底层的对称性密钥（symmetric keys）被证书、非对称密钥或其他的对称性密钥保护。用户只需通过提供密码来保护这一系列的密钥。

 

加密实战

--示例一,使用证书加密数据.

--准备工作, 创建测试数据库TestDB

use master

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

drop database TestDB

 

CREATE DATABASE [TestDB] ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

go

use TestDB

 

--建立测试数据表

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

--数据库主密钥

  --1)必须先在该数据库上创建数据库主密钥才能使用

  create master key encryption by password='P@ssw0rd'

 

  --2)使用数据库主密钥

  --如果数据库主密钥使用服务密钥进行保护，则在使用时会自动打开

  open master key decryption by password='P@ssw0rd'

 

--建立证书一,该证书使用数据库主密钥来加密

CREATE CERTIFICATE Cert_Demo1 

WITH 

  SUBJECT=N'cert1 encryption by database master key',

  START_DATE='2008-01-01',

  EXPIRY_DATE='2099-12-31'

GO

 

--建立证书二,该证书使用密码来加密

CREATE CERTIFICATE Cert_Demo2

  ENCRYPTION BY PASSWORD='liangCK.123'

WITH 

  SUBJECT=N'cert1 encrption by password',

  START_DATE='2008-01-01',

  EXPIRY_DATE='2099-12-31'

GO

 

--此时,两个证书已经建立完,现在可以用这两个证书来对数据加密

--在对表tb做INSERT时,使用ENCRYPTBYCERT加密

 

INSERT tb(data)

  SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书Cert_Demo1加密的内容');  --使用证书Cert_Demo1加密

 

INSERT tb(data)

  SELECT ENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是Cert_Demo2证书加密的内容');  --使用证书Cert_Demo2加密

 

--OK.现在已经对数据加密保证了.现在我们SELECT看看

 

SELECT * FROM tb ;

 

--现在对内容进行解密显示.

--解密时,使用DECRYPTBYCERT

 

SELECT 证书解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),

       证书解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))

FROM tb ;--使用证书解密时,要指定DECRYPTBYCERT的第三个参数,因为在创建时,指定了ENCRYPTION BY PASSWORD.所以这里要通过这个密码来解密.否则解密失败

 

--我们可以看到,如果证书不匹配将无法解密.所以返回NULL

 

GO

 

--删除测试证书与数据表

DROP CERTIFICATE Cert_Demo1;

DROP CERTIFICATE Cert_Demo2;

DROP TABLE tb;

GO

 

--示例二,使用对称密钥加密数据, 对称密钥使用证书来加密. 

--准备工作, 创建测试数据库TestDB

use master

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

drop database TestDB

 

CREATE DATABASE [TestDB] ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

go

use TestDB

 

--创建测试数据表tb

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

 

--建立证书,该证书用于加密对称密钥.

 

CREATE CERTIFICATE Cert_Demo

  ENCRYPTION BY PASSWORD=N'liangCK.123'

WITH

  SUBJECT=N'cert encryption by password',

  START_DATE='2008-01-01',

  EXPIRY_DATE='2099-12-31'

GO

 

--建立对称密钥,该对称密钥由证书加密.

 

CREATE SYMMETRIC KEY Sym_Demo

WITH

   ALGORITHM=DES  --使用DES加密算法

ENCRYPTION BY CERTIFICATE Cert_Demo --使用Cert_Demo证书加密

GO 

 

--要使用Sym_Demo对称密钥.必需使用OPEN SYMMETRIC KEY来打开它

 

OPEN SYMMETRIC KEY Sym_Demo

   DECRYPTION BY CERTIFICATE Cert_Demo

      WITH PASSWORD=N'liangCK.123'

 

--插入加密数据

 

INSERT tb(data)

  SELECT ENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是对称密钥Sym_Demo加密的数据,能显示出来吗?')

  

--关闭密钥

CLOSE SYMMETRIC KEY Sym_Demo

 

--插入完加密数据,现在使用SELECT来查询一下数据

 

SELECT * FROM tb 

 

GO

 

--现在来解密此数据

 

--同样,还是要先打开对称密钥

 

OPEN SYMMETRIC KEY Sym_Demo

   DECRYPTION BY CERTIFICATE Cert_Demo

      WITH PASSWORD=N'liangCK.123'

 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYKEY(data)) --这里可见,数据已经解密出来了.

FROM tb 

 

CLOSE SYMMETRIC KEY Sym_Demo

GO

 

--删除测试

DROP SYMMETRIC KEY Sym_Demo

DROP CERTIFICATE Cert_Demo

DROP TABLE tb

 

--示例三,使用非对称密钥加密数据. 

--准备工作, 创建测试数据库TestDB

use master

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

drop database TestDB

 

CREATE DATABASE [TestDB] ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

go

use TestDB

 

--1、创建非对称密钥

CREATE ASYMMETRIC KEY asy_TestKey WITH ALGORITHM = RSA_1024

ENCRYPTION BY PASSWORD ='123456'

GO

 

SELECT * FROM sys.asymmetric_keys

 

--2、创建示例表

USE testdb

CREATE TABLE test(

EmpID int,

Title nvarchar(50),

Salary varbinary(500)

)

GO

 

--3、向表中插入数据，并对Salary列的数据进行加密

INSERT INTO test VALUES (1, 'CEO ', EncryptByAsymKey(AsymKey_ID( 'asy_TestKey'), '20000 '))

INSERT INTO test VALUES (2, 'Manager ', EncryptByAsymKey(AsymKey_ID( 'asy_TestKey'), '10000 '))

INSERT INTO test VALUES (3, 'DB Admin ', EncryptByAsymKey(AsymKey_ID( 'asy_TestKey'), '5000 '))

GO

 

--4、查看表中存放的数据

SELECT * FROM test

 

--5、解密被加密了的数据列

SELECT EmpID,Title,CONVERT(varchar(20),DecryptByAsymKey(AsymKey_Id('asy_TestKey'),Salary, N'123456'))

FROM test

 

--删除测试

DROP ASYMMETRIC KEY asy_TestKey

DROP TABLE test

 

--示例四,还有一种方法加密数据更简单

--就是使用EncryptByPassPhrase

--准备工作, 创建测试数据库TestDB

use master

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestDB')

drop database TestDB

 

CREATE DATABASE [TestDB] ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

go

use TestDB

 

--创建测试数据表tb

CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000));

GO

INSERT tb(data)

  SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');

 

--查询加密后的数据

select * from tb  

--解密

 

SELECT CONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))

FROM tb 

 

GO

DROP TABLE tb

　 

假如认为对存储在数据库中的数据加密完全是浪费处理器时间和存储空间那就错了。SQL Server中的数据加密是一个非凡的特性，它为客户的数据提供了一个重要的保护层。但应用时要注意，只对那些敏感机密的数据进行保护，因为加密消耗服务器处理器大量的资源，如果对一个有一千万条记录的表的每个字段都加密的话，运行一条没有Where 子句的SELECT 就可能导致服务器性能的彻底崩溃。

 

pwdencrypt, pwdcompare加密:

而PWDENCRYPT与PWDCOMPARE函数刚好是一对兄弟，一个负责加密 、一个负责比对 ，但就是没有解密的函数。 意思也就是說，使用PWDENCRYPT 加密過的資料是無法反解的，類似一種Hash 處理方法。意思也就是说，使用PWDENCRYPT 加密过的资料是无法反解的，类似一种Hash 处理方法。

而且我還發現，使用PWDENCRYPT 函數加密同樣的字串資料，每次回應的結果都會不一樣，但是使用PWDCOMPARE 函數一樣可以對加密過的資料進行比對，以下是簡單的使用方法介紹。而且使用PWDENCRYPT 函数加密同样的字串资料，每次回应的结果都会不一样，但是使用PWDCOMPARE 函数一样可以对加密过的资料进行比对，以下是简单的使用方法介绍。

PWDENCRYPT 函數 select pwdencrypt('ok')

執行後的結果：执行后的结果：

 0x010066E6CDE3C8DE9363BE015AC22966456F230432D6485C6B58 0x010066E6CDE3C8DE9363BE015AC22966456F230432D6485C6B58

注意：每次執行select pwdencrypt( 'ok' ) 的結果都會不一樣，這是一種對密碼保護的機制，只是我不清楚他的演算法。 注意：每次执行select pwdencrypt('ok')的结果都会不一样，这是一种对密码保护的机制。

PWDCOMPARE 函數 select pwdcompare('ok',0x010066E6CDE3C8DE9363BE015AC22966456F230432D6485C6B58)

執行後的結果為1 或0 而已，1 代表密碼比對成功，0 代表密碼比對失敗。执行后的结果为1 或0 而已，1 代表密码比对成功，0 代表密码比对失败。

第一個參數是當初加密時的設定的密碼字串。第一个参数是当初加密时的设定的密码字串。 第二個參數是當時加密後的二進位值 。第二个参数是当时加密后的二进位值 。

事實上，SQL Server 本身在進行密碼驗證時，應該也就是用PWDCOMPARE 函數在比對的，怎麼說呢？事实上，SQL Server 本身在进行密码验证时，应该也就是用PWDCOMPARE 函数在比对的，怎么说呢？ 你可以先執行以下T-SQL 查詢出系統登入帳號的password_hash 欄位：你可以先执行以下T-SQL 查询出系统登入帐号的password_hash 栏位：

SELECT password_hash FROM master.sys.sql_logins WHERE name= 'sa'

然後再將結果用PWDCOMPARE 函數驗證一下你設定的sa 密碼，如果回傳值是1 就代表密碼正確。然后再将结果用PWDCOMPARE 函数验证一下你设定的sa 密码，如果回传值是1 就代表密码正确。

備註：雖然有人說這類undocumented functions 未來可能隨時被取消或刪除，但我卻認為這一組Function 應該會長久存在，只是「沒有文件」而已。declare @A varbinary(500)

set @A=(SELECT password_hash FROM master.sys.sql_logins WHERE name= 'sa')

select pwdcompare('12345', @A)

--测试一张表吧

  declare @t table(name varchar(10),pwd varbinary(256))

  insert @t values('u1',pwdencrypt('1234567'))  

  declare @int int

  select @int=pwdcompare('1234567',(select pwd from @t),0)

  begin

  if @int=1

  print '你输入的密码正确!'

  else

  print '你输入的密码错误!'

end

 

 

 

 

其他的一些理论

 --第一层服务主密钥 

  --备份服务主密钥

  backup service master key to file='c:\smk.bak'

  encryption by password='P@ssw0rd'

 

  Alter service master key regenerate

 

  restore service master key from file='c:\smk.bak'

  decryption by password='P@ssw0rd'

 

--第二层数据库主密钥

  --1)必须先在该数据库上创建数据库主密钥才能使用

  create master key encryption by password='P@ssw0rd'

 

  --2)使用数据库主密钥

  --如果数据库主密钥使用服务密钥进行保护，则在使用时会自动打开

  open master key decryption by password='P@ssw0rd'

 

  --3)查看数据库主密钥状态

  select * from sys.symmetric_keys

 

  --4)备份数据库主密钥

  backup master key to file='c:\dbk.bak'

  encryption by password='P@ssw0rd'

  go

  restore master key from file='c:\dbk.bak'

  decryption by password='P@ssw0rd'

  encryption by password='P@ssw0rd'

--第三层数字证书

 --1)创建自签名

  create certificate cert_Testcert

  encryption by password='p@ssw0rd'

  with subject='TestCert1',

  start_date='1/31/2006',

  expiry_date='1/31/2099'

   

 --2) 创建非对称密钥

  create asymmetric key asy_Key1

  with algorithm=RSA_2048

  encryption by password='P@ssw0rd'

   

 --3) 创建对称密钥

  create symmetric key sy_Key1

  with algorithm=DES

  encryption by certificate cert_Testcert

  --4)使用密钥(使用前必须打开)

  open symmetric key sy_Key1

  decryption by certificate cert_Testcert

  WITH PASSWORD='p@ssw0rd'  
  数据列加密
  －使用对称密钥加密大量的列数据
  －使用证书，非对称密钥保护对称密钥
  －防止绕过加密数据列的攻击
 注:
  在加密列上的索引将变得无效
  加密数据列的长度增长，建议使用varbinary（max）数据类型
  修改已有的DML语句以支持加密的数据列
 
  -----***********示例1 了解数据库加密体系结构*****-----
 
  --************(1) 服务主密钥
  --准备工作

  --创建测试数据库TestDB

CREATE DATABASE [TestDB] ON  PRIMARY

( NAME = N'TestDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'TestDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

go

use TestDB
  --1)备份服务主密钥

  backup service master key to file='f:\smk.bak'

  encryption by password='p@ssw0rd'

 

  --2)生成新的主密钥

  Alter service master key regenerate

 

  --3)从备份文件还原服务主密钥

  Restore service master key from file ='f:\smk.bak'

  decryption by password='p@ssw0rd'

 

  --*************(2) 数据库主密钥

  --1)为数据库创建数据库主密钥

  create master key encryption by password='p@ssw0rd'

 

  --2)查看数据库加密状态

  select [name],is_master_key_encrypted_by_server  

  from sys.databases where name='TestDB'

 

  --3)查看数据库主密钥的信息

  select * from sys.symmetric_keys  

 

  --4)备份数据库主密钥

  backup master key

  to file='f:\dbkey.bak'

  encryption by password='p@ssw0rd'

 

  --5)删除服务主密钥对数据库主密钥的保护, 创建非对称密钥成功,自动使用服务主密钥解密并使用该数据库主密钥

 

  create asymmetric key asy_Testkey1 with algorithm=RSA_1024

   --删除服务主密钥对数据库主密钥的保护

  alter master key

  drop encryption by service master key

   --查看数据库加密状态

  select [name],is_master_key_encrypted_by_server  

  from sys.databases where name='TestDB'

   --创建非对称密钥失败,因为数据库主密钥未打开

  create asymmetric key asy_Testkey2 with algorithm=RSA_1024

   --打开数据库主密钥

  open master key decryption by password='p@ssw0rd'

  select * from sys.openkeys

  --创建非对称密钥成功

  create asymmetric key asy_Testkey2 with algorithm=RSA_1024

  --恢复服务主密钥对数据库主密钥的保护

  alter master key

  add encryption by service master key

  close master key

  --*****(3)证书

  --1)创建自签名证书

  create certificate cert_Testcert

  encryption by password='p@ssw0rd'

  with subject='TestCert1',

  start_date='1/31/2006',

  expiry_date='1/31/2099'

 

  select * from sys.certificates

  --2)从文件导入证书

  Create certificate cert_TestCert2

  From file='f:\Testcert.cer'

  -- 3)备份导出证书和密钥

  backup certificate cert_Testcert

  to file='f:\Testcert.cer'

  with private key

  (decryption by password='p@ssw0rd',

  file='f:\TestCert_pvt',--私钥

  encryption by password='p@ssw0rd' )

  --4)使用证书加解密数据

  --加密:使用证书的公钥

  declare @cleartext varbinary(200)

  declare @cipher varbinary(200)

  set @cleartext=convert( varbinary(200),'Test text string')

  set @cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)

  select @cipher

  --解密:使用证书的私钥

  select convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd')) as [cleartext]

 --5) 删除证书私钥

  alter certificate cert_TestCert

  remove private key

  --加密成功

  declare @cleartext1 varbinary(200)

  declare @cipher1 varbinary(200)

  set @cleartext1=convert( varbinary(200),'Test text string')

  set @cipher1=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)

  select @cipher1

  --解密失败:因为私钥被删除

  select convert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd')) as [cleartext]