如何对整个数据库加密?用的是sql server2005

因为数据库涉及考卷，较为敏感。
有没有办法，可以一次性把整个数据库的内容加密？
即，即一般人用它自己的username， password就只能打开数据库看到些乱码。

而用程序写一个连接conn，里面加某个参数就可以解密的读取数据库的内容。

-----------------------------------------------------------------------
现阶段，我只懂得用不同sql语句，对个别field用EncryptByKey(Key_GUID('xxxxx'),CONVERT(varchar(max),field1))
...之类的方法加密以及解密。
但是由于将会应用到一个现有的程序里，如果每个sql语句都改的话，工作量会非常大。

所以希望能够只是改动一个conn语句能达到以上目的。
-----------------------------------------------------------------------

34 个解决方案

#1

等高手瞧瞧

#2

帮顶！

#3

SQL 2005加密数据方法:

设置数据库加密码主密钥
create master key encryption by password = 'fengjicai' 
--drop master key 删除主密钥

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

--建立测试数据表 
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000)); 
GO 
--建立证书一,该证书使用数据库主密钥来加密 
CREATE CERTIFICATE Cert_Demo1 
WITH 
   SUBJECT=N'cert1 encryption by database master key', 
   START_DATE='2008-01-01', 
   EXPIRY_DATE='2008-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='2008-12-31' 
GO 

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

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

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


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

SELECT * FROM tb ; 

--现在对内容进行解密显示. 
--解密时,使用DECRYPTBYCERT 

SELECT 证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)), 
        --使用证书2解密时,要指定DECRYPTBYCERT的第三个参数, 
        --因为在创建时,指定了ENCRYPTION BY PASSWORD. 
        --所以这里要通过这个密码来解密.否则解密失败 
        证书2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123')) 
FROM tb ; 

--我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL 

/* 
证书1解密                                               证书2解密 
-------------------------------------------------- -------------------------------------------------- 
这是证书1加密的内容-liangCK                                  NULL 
NULL                                                这是证书2加密的内容-liangCK 

(2 行受影响) 
*/ 

GO 

--删除测试证书与数据表 
DROP CERTIFICATE Cert_Demo1; 
DROP CERTIFICATE Cert_Demo2; 
DROP TABLE tb; 
GO 


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

--创建测试数据表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='2008-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'这是加密的数据,能显示出来吗?') 
   
--关闭密钥 
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 



--示例三,还有一种方法加密数据更简单 
--就是使用EncryptByPassPhrase 

--建立测试数据表tb 

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

INSERT tb(data) 
   SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容'); 
   
--解密 

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


GO 
-

#4

關注...

#5

MD5加密法,参考:
http://hi.baidu.com/csxbao/blog/item/c07705e71457722bb8382043.html

#6

学习

#7

SQL 2005加密数据方法:

设置数据库加密码主密钥
create master key encryption by password = 'fengjicai' 
--drop master key 删除主密钥

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

--建立测试数据表 
CREATE TABLE tb(ID int IDENTITY(1,1),data varbinary(8000)); 
GO 
--建立证书一,该证书使用数据库主密钥来加密 
CREATE CERTIFICATE Cert_Demo1 
WITH 
   SUBJECT=N'cert1 encryption by database master key', 
   START_DATE='2008-01-01', 
   EXPIRY_DATE='2008-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='2008-12-31' 
GO 

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

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

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


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

SELECT * FROM tb ; 

--现在对内容进行解密显示. 
--解密时,使用DECRYPTBYCERT 

SELECT 证书1解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)), 
        --使用证书2解密时,要指定DECRYPTBYCERT的第三个参数, 
        --因为在创建时,指定了ENCRYPTION BY PASSWORD. 
        --所以这里要通过这个密码来解密.否则解密失败 
        证书2解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123')) 
FROM tb ; 

--我们可以看到,因为第2条记录是证书2加密的.所以使用证书1将无法解密.所以返回NULL 

/* 
证书1解密                                               证书2解密 
-------------------------------------------------- -------------------------------------------------- 
这是证书1加密的内容-liangCK                                  NULL 
NULL                                                这是证书2加密的内容-liangCK 

(2 行受影响) 
*/ 

GO 

--删除测试证书与数据表 
DROP CERTIFICATE Cert_Demo1; 
DROP CERTIFICATE Cert_Demo2; 
DROP TABLE tb; 
GO 


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

--创建测试数据表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='2008-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'这是加密的数据,能显示出来吗?') 
   
--关闭密钥 
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 



--示例三,还有一种方法加密数据更简单 
--就是使用EncryptByPassPhrase 

--建立测试数据表tb 

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

INSERT tb(data) 
   SELECT EncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容'); 
   
--解密 

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


GO 

#8

在SQL Server 2005中同样可以用证书来将其实现。下文中将介绍如何来进行实现： 

--创建主密钥 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'fengjicai' 

--用密码fengjicai创建证书boyi 

CREATE CERTIFICATE boyi 

ENCRYPTION BY PASSWORD = 'fengjicai' 

WITH SUBJECT = 'boyi55 test certificate', 

start_date='02/08/2008', 

EXPIRY_DATE = '02/08/2009'; 

GO 

--建测试表，name字段为要加密的列，数据类型要为varbinary 

--因为加密后的数据是二进制数据 

create table testB(id int identity(1,1),name varbinary(5000)) 

--向测试表中写入一条测试数据 

insert into testB(name) 

select encryptbycert(cert_id('boyi'),'boyi55') 

/* 

附：encryptbycert函数用法 


EncryptByCert ( certificate_ID , { 'cleartext' | @cleartext } ) 

certificate_ID 

数据库中证书的 ID。 


cleartext 

将使用证书进行加密的数据字符串。 


@cleartext 

类型为 nvarchar、char、wchar、varchar 或 nchar 的变量，其中包含将使用证书的公钥进行加密的数据。 

返回类型 

Varbinary，最大大小为 8000 个字节。 

*/ 

--提取加密后数据 

SELECT id, cast(DecryptByCert(Cert_Id('boyi'), 

name, N'fengjicai')as varchar(20)) from testb 

/* 

说明：decryptbycert函数的返回类型为varbinary，所以要将二进制转化为原始类型。 

name为字段名，此处也必须是二进制类型。如果不是要做相应转换。 

附：decryptbycert函数用法 

DecryptByCert ( 

certificate_ID , 

{ 'ciphertext' | @ciphertext } 

[ , { 'cert_password' | @cert_password } ] 

) 

certificate_ID 

数据库中的证书的 ID。 

ciphertext 

已用证书的公钥加密的数据的字符串。 

@ciphertext 

类型为 varbinary 的变量，其中包含已用证书加密的数据。 

cert_password 

用来加密证书私钥的密码。必须为 Unicode 字符。 

@cert_password 

包含密码的变量，该密码用来加密证书的私钥。必须为 Unicode 字符。 

*/ 

#9

引用 3 楼 fredrickhu 的回复:

SQL codeSQL 2005加密数据方法:

设置数据库加密码主密钥create masterkey encryptionby password='fengjicai'--drop master key 删除主密钥--示例一,使用证书加密数据.--建立测试数据表CREATETABLE tb(IDintIDENTITY(1,1),datavarbinary(8000));GO--建立证书一,该证¡­

其实我对对称，非对称，证书之类的加密方式都知道的。
不过，还是没有达到我的要求。
因为要改太大量的sql语句了。
而且，我的数据库已经存在了。如果我每个表都改，就很麻烦了。

#10

引用 9 楼 ddlik 的回复:

引用 3 楼 fredrickhu 的回复:
SQL codeSQL 2005加密数据方法:

设置数据库加密码主密钥create masterkey encryptionby password='fengjicai'--drop master key 删除主密钥--示例一,使用证书加密数据.--建立测试数据表CREATETABLE tb(IDintIDENTITY(1,1),datavarbinary(8000));GO--建立证书一,该证¡­


其实我对对称，非对称，证书之类的加密方式都知道的。
不过，还是没有达到我的要求。
因为要改太大量的sql语句了。
而且，我的数据库已经存在了。如果我每个表都改，就很麻烦了。

试下MD5算法 5楼的

#11

这个应该达到……

MS SQL 2005 字符串加密函式 -- HashBytes

HashBytes提供的加密法有几种 (MD2、MD4、MD5、SHA、SHA1 )，但最常使用的还是 MD5 与 SHA1。而整个使用过程的感觉来说，基本上还算简单~只是有一些"眉角"要注意。首先先测试 HashBytes 运作是否合乎需求： 

1
 select hashbytes('MD5','12345') as MD5,hashbytes('SHA1','12345') as SHA1; 
 

 

 产生以下的结果

MD5
 SHA1
 
0x827CCB0EEA8A706C4C34A16891F84E7B
 0x8CB2237D0679CA88DB6464EAC60DA96345513964
 


针对 HashBytes 的应用测试过程

接下来开一个测试用的 Table 来试试转换的结果如何：

 

 
 
1
 CREATE TABLE [dbo].[HashTest]( 
 
2
 [INDEX_NO] [int] IDENTITY(1,1) NOT NULL, 
 
3
 [PassWD] [varchar](50) COLLATE Chinese_*_Stroke_CI_AS NOT NULL, 
 
4
 [HashedPW] [varchar](50) COLLATE Chinese_*_Stroke_CI_AS NULL, 
 
5
 CONSTRAINT [PK_HashTest] PRIMARY KEY CLUSTERED 
 
6
 ( 
 
7
 [INDEX_NO] ASC 
 
8
 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] 
 
9
 ) ON [PRIMARY] 
 
10
 GO 
 
 
  
 

在 PassWD 字段中输入一些字符串， HashedPW 保持空白，等等要看加密的结果是否正确

INDEX_NO
 PassWD
 HashedPW
 
1
 test
  
 
2
 test2
  
 
3
 test3
  
 

 然后将以 SHA1 加密过的字符串更新到 HashedPW 看看 --

 
 
1
 update hashtest set hashedPW = HashBytes('SHA1', PassWD) 
 
 
  
 


 咦??怎么都变成乱码了啊 O_o...这样子根本不能用啊...再看了 MSDN 之后发现该函数的回传值型态为 varbinary，并不是字符串形态，所以要再使用 sys.fn_VarBinToHexStr() 进行转换的动作 -- 
 

INDEX_NO
 PassWD
 HashedPW
 
1
 test
 咐戋 L s???闺
 
2
 test2
 
 
3
 test3
 >缚 鄀 o Y
 

 

 
 
1
 update hashtest set hashedPW = sys.fn_VarBinToHexStr(HashBytes('SHA1', PassWD)); 
 
 
  
 

输出的结果就正确了 :)

INDEX_NO
 PassWD
 HashedPW
 
1
 test
 0xa94a8fe5ccb19ba61c4c0873d391e987982fbbd3
 
2
 test2
 0x109f4b3c50d7b0df729d299bc6f8e9ef9066971f
 
3
 test3
 0x3ebfa301dc59196f18593c45e519287a23297589
 

 

最后...改一下登入机制的验证方式，修改比对的字段后...原本明码的密码就可以随风而逝了，需求达成~

#12

-- =============================================
--功能: 实现MD5加密算法，返回对字符串的加密结果串
--使用：PRINT dbo.MD5('111111')
-- =============================================
CREATE FUNCTION [dbo].[MD5] 
(  
    @src varchar(255) -- 源字符串
)
RETURNS varchar(255)
WITH EXECUTE AS CALLER
AS
BEGIN
    -- 存放md5加密串(ox)
    DECLARE @smd5 varchar(34)
    -- 加密字符串
    SELECT @smd5 = sys.fn_VarBinToHexStr(hashbytes('MD5', @src));
    SELECT @smd5 = SUBSTRING(@smd5,3,32)    --32位
    -- 返回加密串
    RETURN @smd5
END
GO
--使用方法如下
PRINT dbo.MD5('111111') 

#13

引用 7 楼 lihan6415151528 的回复:

SQL code
SQL 2005加密数据方法:

设置数据库加密码主密钥create masterkey encryptionby password='fengjicai'--drop master key 删除主密钥--示例一,使用证书加密数据.--建立测试数据表CREATETABLE tb(IDintIDENTITY(1,1),datavarbinary(8000));GO--建立证书一,该¡­

这个很好啊！ 

#14

据我所知，在 sql server 2005 中只能用 EncryptByKey/DecryptByKey 对单个列的值进行加密/解密。

你的要求可能无解。

#15

以下是msdn论坛上的答复。到底听谁的？？？

如何对整个数据库加密?用的是sql server2005
2009年9月5日 

登录进行投票因为数据库涉及考卷，较为敏感。
有没有办法，可以一次性把整个数据库的内容加密？
即，即一般人用它自己的username， password就只能打开数据库看到些乱码。

而用程序写一个连接conn，里面加某个参数就可以解密的读取数据库的内容。

-----------------------------------------------------------------------
现阶段，我只懂得用不同sql语句，对个别field用EncryptByKey(Key_GUID('xxxxx'),CONVERT(varchar(max),field1))
...之类的方法加密以及解密。
但是由于将会应用到一个现有的程序里，如果每个sql语句都改的话，工作量会非常大。

所以希望能够只是改动一个conn语句能达到以上目的。
-----------------------------------------------------------------------

回复引用 
全部回复
2009年9月5日 9:12stswordmanMSFT, Moderator 
0登录进行投票没有办法办到，2005自身不提供这种功能。使用第三方解决方案或者升级到2008，使用透明加密
并且2005中的密文大小的上限是8k，大于大字段要现将内容分割后才能加密 
--------------------------------------------------------------------------------
SQL SERVER Engine Test
回复引用 
2009年9月5日 10:14Stone Z  
0登录进行投票既然在设计最开始没有考虑到加密数据的问题，那么目前最简单的事情就是保护好 数据库的用户名和密码

并且删除sql 的windows账户，然后在保护好能登陆本机器的账户（windows）

然后保护好机器的物理安全，这样应该能够很大程度的保证 内容安全。


--------------------------------------------------------------------------------
family as water
回复引用

#16

学习。

#17

好样的，学习中

#18

mark

#19

学习了！！

#20

正发愁呢，这不有学了一招，谢了！

#21

貌似最好采用SQL2008的透明数据加密了。

#22

楼主，请快点结帖给分把

#23

mark


学习哦

#24

1. 建立一个新的用户， 对该用户分配对所有对象的访问权限。
2. deny Public role对所有对象的访问权限。

3. 在程序中，让用户登录数据库，如果成功，则对上述新用户建立对数据库的连接访问。

#25

对数据加密如果不好处理，为何不从登录限制入手呢？

#26

小F，强悍。。
   学习。

#27

没听说过对数据进行加密的,友情帮顶.

#28

<input type="text">

#29

该回复于2011-03-24 09:20:50被版主删除

#30

求关注：如何在别人拷贝了数据库文件的情况下，需要验证通过才能够附加数据库成功?

#31

2008据说可以

2005，只能把字段内容加密后存入，这样，like/>/<都不行了，=还是可以的

#32

该回复于2012-03-19 22:06:42被版主删除

#33

3楼终于让我看到希望了

#34

使用透明数据库加密

http://blog.csdn.net/ws_hgo/article/details/6927152