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

Question

因为数据库涉及考卷，较为敏感。
有没有办法，可以一次性把整个数据库的内容加密？
即，即一般人用它自己的username， password就只能打开数据库看到些乱码。而用程序写一个连接conn，里面加某个参数就可以解密的读取数据库的内容。-----------------------------------------------------------------------
现阶段，我只懂得用不同sql语句，对个别field用EncryptByKey(Key_GUID('xxxxx'),CONVERT(varchar(max),field1))
...之类的方法加密以及解密。
但是由于将会应用到一个现有的程序里，如果每个sql语句都改的话，工作量会非常大。所以希望能够只是改动一个conn语句能达到以上目的。
-----------------------------------------------------------------------

Answer 1

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 
-

Answer 2

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

Answer 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 

Answer 4

在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 字符。 */ 

Answer 5

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

Answer 6

这个应该达到……MS SQL 2005 字符串加密函式 -- HashBytesHashBytes提供的加密法有几种 (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_Taiwan_Stroke_CI_AS NOT NULL, 
 
4
 [HashedPW] [varchar](50) COLLATE Chinese_Taiwan_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
  最后...改一下登入机制的验证方式，修改比对的字段后...原本明码的密码就可以随风而逝了，需求达成~

Answer 7

-- =============================================
--功能: 实现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') 

Answer 8

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

Answer 9

以下是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
回复引用

Answer 10

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

Answer 11

1. 建立一个新的用户， 对该用户分配对所有对象的访问权限。
2. deny Public role对所有对象的访问权限。3. 在程序中，让用户登录数据库，如果成功，则对上述新用户建立对数据库的连接访问。

Answer 12

<input type="text">

Answer 13

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

Answer 14

使用透明数据库加密http://blog.csdn.net/ws_hgo/article/details/6927152