在SQL Server 2005引入了内建数据加密，通过系统函数、证书、密钥完成加密。

一、通过函数加密。

二、与通过证书授权实现的数字证书相似，SQL Server证书包括了公钥和私钥这一对密钥，是数据库级的安全对象，他们用来加密和解密数据。

 

三、SQL Server还拥有创建非对称密钥和对称密钥的能力。非对称密钥与证书相似，公钥用来加密数据库，私钥用来解密数据。非对称密钥和证书都提供了强大的加密强度，但在完成复杂的加密、解密过程时，需要更多的性能开销。对称密钥更适合对大量数据进行加密，且具有较低性能开销，它是对相同数据进行加密和解密的一个密钥。

 

四、SQL Server将这种加密能力放到加密层次结构中，当安装了SQL Server后，在数据库master中创建名为服务主密钥的服务器级别证书，并将其绑定到SQL Server服务账户登录名。

 

服务主密钥用来加密所有其他数据库证书和创建在SQL Server实例中的密钥。另外，你也可以在用户数据库中创建数据库主密钥，它可以用来加密数据库证书和密钥。

 

五、在SQL Server 2008中引入了透明数据加密，它对整个数据库进行加密，而不需要修改任何访问它的应用程序。数据、日志文件和相关的数据库备份都是加密的。如果数据库被窃取，那么如果没有数据库加密密钥，是不能访问数据的。此外，还引入了可扩展密钥管理的支持，也就是SQL Server可以使用硬件安全模块来存储和管理加密密钥，从而减少数据和实际加密密钥的耦合。

 

 

1、通过通行短语来加密

对于一个不涉及证书和密钥的应急的数据加密，可以直接基于用户提供的密码来加密、解密数据。通行短语允许密码中存在空格，而且密码不会存储在数据库中。使用通行短语可以创建一个易于记忆的句子来加密解密数据。

create table #SecretInfo(secret varbinary(8000) not null) --存放经过加密的二进制数据go--用通行短语来加密insert #SecretInfo(secret)select ENCRYPTBYPASSPHRASE(			'My password used to encrypt this string in 2008.', --通行短语			'This is the text I need to secure.'                --需要加密的文本			)			--用通行短语来解密select CAST(		      DECRYPTBYPASSPHRASE(				   'My password used to encrypt this string in 2008.',				   secret)		      as varchar(100)  --解密后是二进制数据，必须要转化成文本		   )from #SecretInfo

2、主密钥

在SQL Server中把加密放到层次结构形式中，可以提供多级别的安全。SQL Server包含两个用于加密数据的密钥类型。

第一个是服务主密钥，位于层次结构的顶端，在安装SQL Server时自动创建，服务主密钥也可以用来加密其下的数据库主密钥、证书、链接服务器密码。在第一次加密时，服务主密钥会自动生成，并且使用SQL Server服务账户的Windows证书来生成它，如果必须修改SQL Server服务账户，建议使用SQL Server配置管理器，因为这个工具会生成新服务主密钥需要的合适的加密、解密方法，而且保持加密层次结构的完整。

第二个是数据库主密钥，位于加密层次结构中SQL Server安全性的其它层，用来加密数据库证书、非对称密钥、对称密钥。所有数据库都可以只包含一个数据库主密钥，创建时通过服务主密钥对其加密。创建非对称密钥时，可以决定在加密非对称密钥对中的私钥时，是否包含密码。如果不包含密码，将使用数据库主密钥来加密私钥。

--1.1备份服务主密钥backup service master key to file ='c:\smk.bak'                  --服务主密钥备份导出的文件路径encryption by password = 'c:\smk.bak'  --用来加密:包含密钥备份的文件的密码		--1.2还原服务主密钥restore service master keyfrom file = 'c:\smk.bak'decryption by password = 'c:\smk.bak'	--1.3参数force强制替换现有的服务主密钥，可能会使加密的数据不能被解密restore service master keyfrom file = 'c:\smk.bak'decryption by password = 'c:\smk.bak' force--2.数据库主密钥use AdventureWorksgo--2.1创建数据库主密钥create master key encryption by password = 'AdventureWorks'--2.2用新的密码重新生成数据库主密钥alter master keyregenerate with encryption by password = '123456'--2.3在没有用数据库主密钥加密其他密钥时,才可以删除数据库主密钥drop master key--2.4备份数据库主密钥backup master key to file = 'c:\master_key.bak'encryption by password = 'c:\master_key.bak'  --保护备份文件的密码--2.5还原数据库主密钥restore master keyfrom file = 'c:\master_key.bak'decryption by password = 'c:\master_key.bak'  --解密备份文件的密码encryption by password = '1234567'            --还原后的数据库主密钥,进行加密的密码restore master keyfrom file = 'c:\master_key.bak'decryption by password = 'c:\master_key.bak'  --解密备份文件的密码encryption by password = '1234567'            --还原后的数据库主密钥,进行加密的密码force   --只有在主密钥无法恢复或解密失败时，才使用FORCE选项/*===================================================2.6在创建数据库主密钥时，默认使用两种方法对它进行加密：服务主密钥和create master key中使用的密码。如果不希望通过服务主密钥对数据库主密钥进行加密，那么可以删除服务主密钥加密。需要特别注意的是，如果删除了服务主密钥加密，那么有sysadmin权限的SQL登录名，有了数据库主密钥的密码，才能访问加密数据。因为服务主密钥允许有sysadmin权限的用户，自动对数据库主密钥进行解密，访问加密数据。=====================================================*/--从数据库主密钥,删除服务主密钥加密alter master keydrop encryption by service master key--如果要重新使用服务主密钥来加密，必须打开数据库主密钥open master keydecryption by password = 'AdventureWorks'--然后服务主密钥重新被添加到数据库主密钥中alter master keyadd encryption by service master key--关闭数据库主密钥close master key

3、非对称密钥加密

非对称密钥包含了数据库级的内部公钥和私钥，可以用来加密和解密SQL Server数据库中的数据。公钥用来加密数据，而私钥用来解密数据，会用创建非对称密钥时提供的密码，或者数据库主密钥，对私钥进行加密来保护私钥。

非对称密钥可以从外部文件或程序集中导入，并且可以在数据库中生成。不像证书，非对称密钥不可以备份到文件，也就是创建了非对称密钥后没有简单的办法在其他数据库中重用相同的密钥。

非对称密钥是数据加密的高级选项，当用在大数据集时，非对称密钥进行加密相对于对称密钥加密来说，比较耗费资源，对称密钥使用一个密钥来加密和解密，速度更快。

用非对称密钥加密时，不用打开，只需要引用非对称密钥的id。

--1.创建非对称密钥create asymmetric key asymKeywith algorithm = RSA_512              --加密算法:RSA_512,RSA_1024,RSA_2048ENCRYPTION BY PASSWORD = 'asymKey123' --加密私钥的密码，如果不提供密码,                                      --会自动用数据库主密钥对私钥进行加密                                    create asymmetric key asymKey1        with algorithm = RSA_512        --这里没有提供密码，所以自动用数据库主密码来加密--2.查询非对称密钥select name,                           --费对称密钥名称       asymmetric_key_id,              --非对称密钥id       pvt_key_encryption_type_desc,   --私钥的加密方法       algorithm_desc,                 --加密算法       key_length,                     --键的长度              sid,       string_sid,       public_key                      --公钥from sys.asymmetric_keyscreate table booksellerbankrouting(booksellerid int not null primary key, bankroutingNBR varbinary(300) not null)--3.用非对称密钥来加密加密 insert into dbo.booksellerbankroutingvalues(   1,   ENCRYPTBYASYMKEY(asymkey_id('asymKey'),                        --非对称密钥的id                    'this string will be encrypted with asymkey.' --要加密的文体                   )  )  --4.直接查询显示的是乱码select CAST(bankroutingNBR as varchar(100))from dbo.booksellerbankrouting--5.解密,显示正确的数据select CAST(DECRYPTBYASYMKEY(                   asymkey_id('asymKey'), --非对称密钥id                   bankroutingNBR,        --需要解密的文本                   N'asymKey123'          --非对称密钥的私钥的密码,用这个密码来解密,                                          --取得私钥,再用私钥来解密被加密的文本                                          --此参数为nvarchar类型,所以字符串前要加N                 )              as varchar(100)           )from dbo.booksellerbankrouting--6.删除非对称密钥,如果已经用非对称密钥建了数据,那么不可以删除,否则数据会无法解密drop asymmetric key asymKey1

4、对称密钥加密

证书和非对称密钥使用数据库级内部的公钥来加密，并且使用数据库级内部的私钥来解密。对称密钥相对简单，包含了一个同时用来加密和解密的密钥，所以对称密钥加密数据更快，并且用在大数据集时更加合适。

 

--1.1创建非对称密钥create asymmetric key asymSymkeywith  algorithm = RSA_512ENCRYPTION BY PASSWORD ='12345ABCDE'/*====================================================对称密钥可以使用的加密算法包括：	des,desx,triple_des,triple_des_3key,	rc2,rc4,	aes_128,aes_192,aes_256	需要用：非对称密钥、其他对称密钥、证书、密码 来加密对称密钥======================================================*/--1.2创建对称密钥,这里用非对称密钥来加密对称密钥CREATE symmetric key symKeywith algorithm = triple_desencryption by asymmetric key asymSymKey--2.查看对称密钥select name,       symmetric_key_id,       key_length,       algorithm_desc from sys.symmetric_keys--3.修改对称密钥加密方式,原来是用非对称密钥来加密的,现在改为用密码加密--3.1打开对称密钥open symmetric key symKeydecryption by asymmetric key asymSymkeywith password = '12345ABCDE'  --这儿的解密密码是非对称密钥的私钥的加密密码                              --先用这个密码解密，取得私钥                              --然后用这个私钥解密，取得对称密钥--3.2先增加密码alter symmetric key symKeyadd encryption by password = 'ABCDE12345'--3.3再删除非对称密钥加密,如果先删除非对称密钥加密,--会报错:无法删除 非对称密钥'asymSymKey'所创建的加密alter symmetric key symKeydrop encryption by asymmetric key asymSymKeyclose symmetric key symKey--4.用对称密钥加密create table ttt(id int not null primary key,  Question varchar(300) not null, Answer varbinary(200) not null)--4.1先打开对称密钥open symmetric key symKeydecryption by password = 'ABCDE12345'--4.2添加数据insert into tttvalues(1,       'what is your name?',       ENCRYPTBYKEY(key_guid('symKey'), --对称密钥的guid                    'abcdefg'           --需要加密的文本                   )      )--4.3关闭对称密钥close symmetric key symKey--5.用对称密钥解密--5.1先打开对称密钥open symmetric key symKeydecryption by password = 'ABCDE12345'--5.2显示解密数据select ttt.id,       ttt.Question,       ttt.Answer,       cast(DECRYPTBYKEY(Answer) as varchar(200)) --解密函数不需要对称密钥的guidfrom ttt--5.3关闭对称密钥close symmetric key symKey--6.删除对称密钥drop symmetric key symKey

 

5、证书加密

证书包含密钥对（公钥和私钥）、证书拥有者的信息、证书可用的开始和结束过期日期。

证书的公钥用来加密数据，私钥用来解密数据。SQL Server可以生成证书，也可以从外部文件或程序集载入。由于可以备份证书，然后从文件载入，所以证书比非对称密钥更容易迁移，而非对称密钥则不能这样，所以多用户数据库可以方便的重用一个证书。

证书和非对称密钥加密都提供了一种非常安全的加密数据的方式，但是这种强度很高的加密方式也带来了较大的性能消耗，使用证书或非对称加密非常大的数据集，可能会对运行环境产生很大的开销，而使用对称密钥相对的开销较低。

 

--1.1创建证书create certificate certEncrypt encryption by password = 'cert12345'with subject = 'encryption certificate',     start_date = '2012-10-01',     expiry_date = '2012-10-31'--1.2.1创建数据库主密钥     create master key encryption by password = 'excel12345'--1.2.2不提供密码时,会自动用数据库主密钥来加密私钥create certificate certEncrypt1with subject = 'encryption certificate',     start_date = '2012-10-01',     expiry_date = '2012-10-31'--1.2.3删除证书,然后才可以删除数据库主密钥,否则会报错drop certificate certencrypt1drop master key--2.查看证书信息select name,       certificate_id,       pvt_key_encryption_type_desc,--对私钥加密方式的说明       is_active_for_begin_dialog,  --对于当前会话，是否启用了证书加密       issuer_name,                 --证书颁发者的名称       subject,                     --证书的主题       start_date,       expiry_date,       pvt_key_last_backup_date     --上一次导出证书的私钥的日期和时间from sys.certificates--3.备份证书--备份证书时用于加密私钥的密码与对证书的私钥进行加密的密码,不是同一个密码backup certificate certEncryptto file = 'c:\certEncrypt.bak'   --证书的备份文件with private key ( file = 'c:\certEncrypt_privateKey.bak',--私钥文件 decryption by password = 'cert12345',  --这个密码是在创建证书时指定的,                                        --用于在备份密钥之前对私钥进行解密的密码      encryption by password = 'encryptCert123456'--在解密取得私钥后,                                             --在将密钥写入私钥的备份文件之前,                                             --对私钥进行加密的密码)--4.还原证书--4.1先删除证书,这样才能重建证书drop certificate certEncrypt--4.2然后从证书备份文件和私钥文件中,再次创建证书create certificate certEncryptfrom file = 'c:\certEncrypt.bak' with private key ( file = 'c:\certEncrypt_privateKey.bak', decryption by password = 'encryptCert123456',  --用于解密私钥文件中的私钥的密码 encryption by password = 'cert12345'           --用于加密私钥的密码)--5.管理证书的私钥--5.1删除私钥的密码，这样默认通过数据库主密钥对私钥进行加密alter certificate certEncryptremove private key--5.2通过备份的私钥文件，为已经存在的证书重新增加私钥的密码alter certificate certencryptwith private key( file = 'c:\certEncrypt_privateKey.bak', decryption by password = 'encryptCert123456', encryption by password = 'cert12345')--5.3修改私钥的密码alter certificate certencryptwith private key( decryption by password = 'cert12345', encryption by password = '12345cert')--6.使用证书加密,解密create table t(id int not null primary key, question varchar(100) not null, answer varbinary(200) not null)--6.1加密数据insert into tvalues(1,       'who are you?',       ENCRYPTBYCERT(                      cert_id('certencrypt'),  --证书的id                      'kaka'                   --需要加密的文本                    )      )--6.2直接查看,发现是乱码select ID,       question,       answer,       CAST(answer AS varchar(200))from t--6.3解密,显示正确数据select ID,       question,       answer,       CAST(DECRYPTBYCERT(                   cert_id('certencrypt'),                   answer,                   N'12345cert'    --私钥的密码,用来解密取得私钥,再用私钥来解密数据                  ) AS varchar(200)            )from t--7.使用对称密钥执行解密，而该对称密钥则使用非对称密钥进行自动解密create table tx(id int not null primary key, question varchar(100) not null, answer varbinary(200) not null)--7.1非对称密钥的私钥用数据库主密钥加密--7.1.1创建数据库主密钥create master key encryption by password = 'excel12345'--7.1.2创建非对称密钥,自动用数据库主密钥加密私钥create asymmetric key asymKeywith algorithm = RSA_512--7.1.3创建对称密钥,用非对称加密的私钥来加密对称密钥CREATE symmetric key symKeywith algorithm = TRIPLE_DESENCRYPTION BY asymmetric key asymKey--7.1.4打开对称密钥open symmetric key symKeydecryption by asymmetric key asymKey--7.1.5用对称密钥加密insert into txvalues(1,       'who are you?',       ENCRYPTBYKEY(KEY_GUID('symKey'),                    'kk')       )--7.1.6关闭对称密钥close symmetric key symKey--7.1.7由于非对称加密的私钥是由数据库主密钥加密的,所以不需要提供私钥的密码,--首先用数据库主密钥解密,取得非对称加密的私钥,--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本select id,       question,       answer,       cast(DecryptBykeyAutoAsymkey(                    asymkey_id('asymKey'), --用于保护对称密钥的非对称密钥的ID                    null,                  --用于保护非对称密钥私钥的密码                                           --如果私钥受数据库主密钥保护，则该值可以是 NULL                    answer                   )               as varchar)from txdelete from tx--7.1非对称密钥的私钥用密码来加密--7.2.1创建非对称密钥create asymmetric key asymKey1with algorithm = RSA_512encryption by password = 'asymKey123456'--7.2.2创建对称密钥,用非对称加密的私钥来加密对称密钥CREATE symmetric key symKey1with algorithm = TRIPLE_DESENCRYPTION BY asymmetric key asymKey1--7.2.3打开对称密钥open symmetric key symKey1decryption by asymmetric key asymKey1with password = 'asymKey123456'    --非对称密钥的私钥的密码--7.2.4用对称密钥加密insert into txvalues(1,       'who are you?',       ENCRYPTBYKEY(KEY_GUID('symKey1'),                    'kk')       )--7.2.5关闭对称密钥close symmetric key symKey1--7.2.6提供非对称加密的私钥的密码,首先用这个密码来解密,取得私钥,--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本select id,       question,       answer,       cast(DecryptBykeyAutoAsymkey(                    asymkey_id('asymKey1'),--用于保护对称密钥的非对称密钥的ID                    N'asymKey123456',      --保护非对称密钥私钥的密码,类型为nvarchar                                           --如果私钥受数据库主密钥保护，则该值可以是 NULL                    answer                   )               as varchar)from txdelete from tx--7.3用证书的私钥用密码来加密--7.3.1创建证书create certificate certEncrypt1 encryption by password = 'cert12345'with subject = 'encryption certificate',     start_date = '2012-10-01',     expiry_date = '2012-10-31'--7.3.2创建对称密钥,用证书的私钥来加密对称密钥CREATE symmetric key symKey11with algorithm = TRIPLE_DESENCRYPTION BY certificate certEncrypt1--7.3.3打开对称密钥open symmetric key symKey11decryption by certificate certEncrypt1with password = 'cert12345'    --非对称密钥的私钥的密码--7.3.4用对称密钥加密insert into txvalues(1,       'who are you?',       ENCRYPTBYKEY(KEY_GUID('symKey11'),                    'kk')       )--7.3.5关闭对称密钥close symmetric key symKey11--7.3.6提供证书的私钥的密码,首先用这个密码来解密,取得私钥,--然后用私钥解密,取得对称加密的公钥,用公钥解密被加密的文本select id,       question,       answer,       cast(DecryptBykeyAutoCert(                 cert_id('certEncrypt1'),--用于保护对称密钥的证书的ID                 N'cert12345',           --保护证书的私钥的密码,类型为nvarchar                                         --如果私钥受数据库主密钥保护，则为NULL                 answer              )             as varchar)from tx--8.删除证书drop certificate certEncrypt

 

6、透明数据加密

SQL Server 2008引入了透明数据加密，称为TDE，运行不修改应用程序的代码来完整的加密数据库文件。当用户数据库可用，并且启用了TDE时，数据写入磁盘时会在页级别进行加密，当数据读入内存时进行解密。

如果数据文件或数据库备份丢失，那么如果没有用于加密DEK的服务器证书，数据库是不可以被还原或者正常的附加到其他SQL Server实例上的。

如果需要正常的将一个数据库从一个实例上移动到另一个实例上，那么可以备份服务器级别的证书，并且在另一个实例上创建这个证书，这样就可以还原TDE的备份或者附加数据文件和日志文件了

 

--1.打开主数据库,这样才能创建服务器级别的证书use mastergo--可以生成下面的密码select NEWID()--2.创建数据库主密钥create master key encryption by password = '123A4C23-DDA2-437A-A320-FF3B787E3B8B'--3.1创建服务器级别的证书,用数据库主密钥来加密证书的私钥create certificate TDE_SERVER_CERTIFICATEwith subject = 'Server-level certificate for TDE'--3.2备份服务器级别的证书,由于这个证书是用数据库主密码加密的,--所以在备份时,证书的私钥由数据库主密钥自动解密,在备份到私钥文件时,必须进行再次加密--如果没有备份私钥文件,没有加密密码,会使得从备份创建的证书无法解密backup certificate TDE_SERVER_CERTIFICATEto file ='c:\TDE_SERVER_CERTIFICATE.bak'with private key( file = 'c:\TDE_SERVER_CERTIFICATE_private_key.bak', encryption by password = '123456789abcedfg'   --用来加密证书私钥的密码)--3.3删除服务器级别的证书drop certificate TDE_SERVER_CERTIFICATE--3.4从备份创建服务器证书create certificate TDE_SERVER_CERTIFICATEfrom file ='c:\TDE_SERVER_CERTIFICATE.bak'with private key(  file = 'c:\TDE_SERVER_CERTIFICATE_private_key.bak',  decryption by password = '123456789abcedfg'   --用来解密证书私钥的密码)--打开需要启用透明数据加密的数据库use excelgo--4.创建以透明方式加密数据库的加密密钥--DEK是用来加密整个数据库的加密密钥create database encryption keywith algorithm = TRIPLE_DES_3KEY  --AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY encryption by server certificate TDE_SERVER_CERTIFICATE--5.启用加密alter database excelset encryption on--6.验证数据库是否真正加密select name,       is_encryptedfrom sys.databaseswhere database_id = DB_ID()--7.1修改DEK密钥的算法强度alter database encryption keyregenerate with algorithm = AES_128--7.2返回所有数据库中相关DEK状态的信息select DB_NAME(database_id),       case encryption_state            when 0 then 'no encryption'            when 1 then 'unencrypted'            when 2 then 'encryption in progress'            when 3 then 'encrypted'            when 4 then 'key change in progress'            when 5 then 'decryption in progress'       end encryption_state,              key_algorithm,       key_length               from sys.dm_database_encryption_keys--8.1创建一个新的服务器级证书use mastergocreate certificate TDE_SERVER_CERTIFICATE_Xwith subject = 'Server-level certificate for TDE X'USE wcGO--8.2修改加密DEK的服务器级证书alter database encryption keyencryption by server certificate TDE_SERVER_CERTIFICATE_X--9.1从数据库中移除TDEalter database excelset encryption off--9.2删除DEKdrop database encryption key