SQLSERVER加密解密函数（⾮对称密钥证书加密对称密钥）ENCRYPTBYASYMKEY() --⾮对称密钥
ENCRYPTBYCERT() --证书加密
ENCRYPTBYKEY() --对称密钥
ENCRYPTBYPASSPHRASE() --通⾏短语（PassPhra）加密
--⾮对称密钥包含数据库级的内部公钥和私钥，它可以⽤来加密和解密SQL Server数据库中的数据，它可以从外部⽂件或程序集中导⼊，也可以在SQL Server数据库中⽣成。它不像证书，不可以备份到⽂件。这意味着⼀旦在SQL Server中创建了它，没有⾮常简单的⽅法在其他⽤户数据库中重⽤相同的密钥。⾮对称密钥对于数据库加密属于⾼安全选项，因⽽需要更多的SQL Server资源。
⼀、⾮对称密钥
--我们看⼀组例⼦：
--⽰例⼀、创建⾮对称密钥
--创建⾮对称密钥使⽤如下命令:
-
-CREATE ASYMMETRIC KEY
--以下语句创建⼀个⾮对称密钥asymDemoKey
u[pratice]
go
CREATE ASYMMETRIC KEY asymDemoKey --创建⾮对称密钥名称
WITH ALGORITHM = RSA_512 --加密安全类型
ENCRYPTION BY PASSWORD ='123!'--密码
--⽰例⼆、查看当前数据库中的⾮对称密钥bec考试内容
--查看当前数据库中的⾮对称密钥
USE[pratice]
go
SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys
--⽰例三、修改⾮对称密钥的私钥密码
--你可以使⽤带有ENCRYPTION BY PASSWORD和DECRYPTION BY PASSWORD选项的
--修改私钥密码
ALTER ASYMMETRIC KEY asymDemoKey--要修改的密钥名称
WITH PRIVATE KEY--私钥
(ENCRYPTION BY PASSWORD ='456',--指定新密码
DECRYPTION BY PASSWORD ='123!')--旧密码是⽤来解密的
--⽰例四、使⽤⾮对称密钥对数据进⾏加密和解密。由于同时需要公钥和密钥，在维护保密数据时使⽤⾮对称密钥来加密数据是⾮常安全的⽅式。但同时⽤于⼤数据集时将消耗更多的资源。不推荐使⽤⾮对称密钥对数据加密，但它仍然是⼀个选择。⼀旦将⾮对称密钥加到数据库，就可以⽤来加密和解
密数据。
--⽤到以下两个sql函数:
--注意，在通过证书加密时，DecryptByAsymKey返回的是varbinary类型的加密数据。
--下⾯是⼀个例⼦：
u[pratice]
go
--创建需要加密的数据
Create Table BankUr
(PKID int primary key identity(1,1)
,UrNo varbinary(1000) null--⼀定要⽤⼆进制数据类型
,CurState datetime not null
)
go
inrt into BankUr
(UrNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID('asymDemoKey'),'137'),GETDATE())
--插⼊⼀条记录，字段UrNo存储了加密的号码值
go
--查看未加密的数据：
SELECT PKID,Curstate,
cast
(DecryptByAsymKey(AsymKey_ID('asymDemoKey'),UrNo,N'123')
as varchar(1000)) as UrNo --需要原始私钥
from BankUr
--⽰例五、删除⾮对称密钥
--命令：DROP ASYMMETRIC KEY 删除指定的⾮对称密钥( /en-us/library/ms188389.aspx)
--例⼦：
DROP ASYMMETRIC KEY asymDemoKey
DROP TABLE[dbo].[BankUr]
--⼩结：
--1、本⽂主要介绍⾮对称密钥的创建、删除、查看以及⽤它来修改私钥、进⾏数据的加密和解密。
--2、⾮对称密钥包含数据库级的内部公钥和私钥，它可以⽤来加密和解密SQL Server数据库中的数据。
--3、⾮对称密钥对于数据库加密属于⾼安全选项，因⽽需要更多的SQL Server资源，不推荐使⽤。
⼆、下⽂将主要介绍相对简单的并且⼴泛应⽤的对称密钥加密(Symmetric Key Encryption)
--SQL Server 2008引⼊透明数据加密（Transparent Data Encryption），即TDE，它允许你完全⽆需修改应⽤程序代码⽽对整个数据库加密。当⼀个⽤户数据库可⽤且已启⽤TDE时，在写⼊到磁盘时在页级实现加密。在数据页读⼊内存时解密。如果数据库⽂件或数据库备份被盗，没有⽤来加密的原始证书将⽆法访问。这⼏乎是SQL Server2008安全选项中最激动⼈⼼的功能了，有了它，我们⾄少可以将⼀些初级的恶意窥视拒之见外。十大暴力城市
--下⾯的两个例⼦将展⽰如何启⽤和维护透明数据加密
--⽰例⼀、启⽤透明加密(TDE)
USE Master
GO
--删除旧主密钥
--Drop MASTER KEY
--GO
--创建主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='B19ACE32-AB68-4589-81AE-010E9092FC6B'
GO
--创建证书,⽤于透明数据加密
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT ='Server-level cert for TDE'
GO
USE[pratice]
GO
--第⼀步：现在开始透明加密
CREATE DATABASE ENCRYPTION KEY--创建数据库加密密钥
WITH ALGORITHM = TRIPLE_DES_3KEY--加密⽅式
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate--使⽤服务器级证书加密
GO
/*
Warning: The certificate ud for encrypting the databa encryption key
has not been backed up.
You should immediately back up the certificate and the private key
associated with the certificate.
If the certificate ever becomes unavailable or
if you must restore or attach the databa on another rver,
you must have backups of both the certificate and the private key
or you will not be able to open the databa.
*/
--第⼆步：打开加密开关
ALTER DATABASE[pratice]SET ENCRYPTION ON
GO
--查看数据库是否加密
SELECT is_encrypted FROM sys.databas
WHERE name ='pratice'品牌少儿英语加盟
View Code
--注意：⼀旦在数据库应⽤了加密，应该⽴刻备份服务器级证书！
--没有加密dek的证书，该数据库将⽆法打开，附加到别的服务器也⽆法使⽤，数据库⽂件亦不会被Hack。
--如果⼀个DBA想要合法地将数据库从⼀个SQL Server实例移动到另⼀个SQL Server实例，
--那么她应该⾸先备份服务器级证书，然后在新的SQL Server实例中创建证书。
--此时可以合法地备份、还原数据库或附加数据及⽇志⽂件。
--⽰例⼆、管理和移除透明加密(TDE)
USE[pratice]
GO
--修改加密算法
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go
SELECT DB_NAME(databa_id) databanm,
CASE encryption_state
WHEN0THEN'No encryption'
WHEN1THEN'Unencrypted'
WHEN2THEN'Encryption in progress'
WHEN3THEN'Encrypted'
WHEN4THEN'Key change in progress'
WHEN5THEN'Decryption in progress'
END encryption_state, key_algorithm, key_length
FROM sys.dm_databa_encryption_keys
/*
对所有⽤户数据库的加密处理也包含对tempdb的处理，因为表连接，临时表都要⽤到tempdb
databanm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/
--除了更改dek的算法，我们也可以更改⽤来加密数据库的服务器级证书（该证书应该定期更改）
USE master
GO
CREATE CERTIFICATE TDE_Server_Certificate_V2
WITH SUBJECT ='Server-level cert for TDE V2'
GO
USE[pratice]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2
--⽤新证书修改DEK
--移除数据库透明加密
ALTER DATABASE[pratice]
SET ENCRYPTION OFF
GO
--移除TDE后，可以删除DEK
USE[pratice]
GO
DROP DATABASE ENCRYPTION KEY
Go
View Code
--注意：如果删除DEK是SQL Server实例中最后⼀个使⽤TDE的⽤户定义数据库，
--在SQL Server实例重启后，tempdb也将变为不加密的状态。
--⼩结：
--1、本⽂主要介绍透明数据加密(TDE)的使⽤。
--2、对DEK的修改同时影响到tempdb数据库的加密状态。
--SQLServer 2008中的代码安全（七）证书加密
--证书可以在数据库中加密和解密数据。
--证书包含密钥对、关于证书拥有者的信息、证书可⽤的开始和结束过期⽇期。
-
-证书同时包含公钥和密钥，前者⽤来加密，后者解密。SQL Server可以⽣成它⾃⼰的证书，也可以从外部⽂件或程序集载⼊。因为可以备份然后从⽂件中载⼊它们，证书⽐⾮对称密钥更易于移植，⽽⾮对称密钥却做不到。这意味着可以在数据库中⽅便地重⽤同⼀个证书。
--注意：证书和⾮对称密钥同样的消耗资源。
--我们看⼀组例⼦：
--⽰例⼀、创建数据库证书wind万点
asss
--创建数据库证书:CREATE SYMMETRIC KEY （/en-us/library/ms187798.aspx）
USE[pratice]
GO
--创建证书
CREATE CERTIFICATE cert_Demo --证书名称
ENCRYPTION BY PASSWORD ='123'--加密证书的密码
WITH SUBJECT ='DB_Encrypt_Demo Databa Encryption Certificate',--证书主题
START_DATE ='3/14/2012', EXPIRY_DATE ='10/20/2016'--起⽌⽇期
GO
--⽰例⼆、查看数据库中的证书
--使⽤⽬录视图ificates(/en-us/library/ms189774.aspx)来查看。
--查看当前数据库中的证书
USE[pratice]
go
--查看证书
SELECT name, pvt_key_encryption_type_desc, issuer_name
FROM ificates
--⽰例三、备份和还原证书
--创建证书后，也可以使⽤BACKUP CERTIFICATE（/en-us/library/ms178578.aspx）
--命令备份到⽂件，为了安全地保存或在其他数据库中还原它。
--备份证书
BACKUP CERTIFICATE cert_Demo
TO FILE='D:\certDemo.BAK'--证书备份路径,⽤来加密
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',--证书私钥⽂件路径，⽤来解密
ENCRYPTION BY PASSWORD ='465',--加密私钥密码
DECRYPTION BY PASSWORD ='123' )--解密私钥密码
--备份后，可以在其他数据库中使⽤这个证书，或使⽤DROP CERTIFICATE命令删除它。
DROP CERTIFICATE cert_Demo
GO
--从备份⽂件中还原证书到数据库中
CREATE CERTIFICATE cert_Demo
FROM FILE='D:\\certDemo.BAK'
WITH PRIVATE KEY (FILE='D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD ='456' ,--解密私钥密码
ENCRYPTION BY PASSWORD ='123')--加密私钥密码
--⽰例四、管理证书的私钥
--使⽤ALTER CERTIFICATE( /en-us/library/ms189511.aspx)
--命令为证书增加或删除私钥。
-
-这个命令允许删除私钥（默认通过数据库主密钥加密）、增加私钥或修改私钥的密码。
--从证书中删除私钥
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY
--从备份⽂件为既有证书重新增加私钥
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE='D:\certDemoPK.BAK',
DECRYPTION BY PASSWORD ='1234GH' ,--解密私钥密码
ENCRYPTION BY PASSWORD ='123')--加密私钥密码
--修改既有私钥的密码
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD ='123',
ENCRYPTION BY PASSWORD ='78913E')
--⽰例五、使⽤证书加密和解密。
--使⽤函数EncryptByCert加密数据。(/zh-cn/library/ms174361.aspx)
USE[pratice]
GO
CREATE TABLE PWDQuestion
(
CustomerID INT ,
radiantPasswordHintQuestion NVARCHAR(200) ,
PasswordHintAnswer NVARCHAR(200)
)
--插⼊测试数据
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
argentina是什么意思
VALUES ( 10, '您出⽣的医院名称?', ENCRYPTBYCERT(CERT_ID('cert_Demo'), '北京四合院家中') )
ts什么意思--查看密⽂
SELECT[PasswordHintQuestion], CAST(PasswordHintAnswer AS VARCHAR(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =10
--查看原⽂
SELECT PasswordHintQuestion,破折号的用法
美容培训学院CAST(DECRYPTBYCERT(CERT_ID('cert_Demo'), PasswordHintAnswer,
N'78913E') AS VARCHAR(200)) PasswordHintAnswer
FROM dbo.PWDQuestion
WHERE CustomerID =10
View Code
--⽰例六、使⽤对称密钥对数据进⾏加密和解密。
--在前⾯的⽂章中，你已经看到打开⽤⾮对称密钥加密的对称密钥的演⽰，
--它分两个步骤，⾸先⽤OPEN SYMMETRIC KEY命令，然后是实际的DecryptByKey函数调⽤。--SQL Server也提供了能够将这两个步骤合⼆为⼀的额外的解密函数：
USE[pratice]
GO
--本例使⽤数据库主密码加密，因⽽不需要密码。
----Create master Key Encryption By password='123ASD!'
----go
--创建⾮对称密钥
CREATE ASYMMETRIC KEY asymDemo_V2
WITH ALGORITHM = RSA_512
--创建对称密钥
CREATE SYMMETRIC KEY sym_Demo_V2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2
--打开对称密钥，插⼊记录
OPEN SYMMETRIC KEY sym_Demo_V2
DECRYPTION BY ASYMMETRIC KEY asymDemo_V2
INSERT dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
VALUES ( 22, '您出⽣的医院名称?', ENCRYPTBYKEY(KEY_GUID('sym_Demo_V2'), '邵逸夫医院') )
CLOSE SYMMETRIC KEY sym_Demo_V2
--此时，使⽤DecryptByKeyAutoAsymKey解密数据，只需要⼀个操作
SELECT CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID('asymDemo_V2'), NULL,
PasswordHintAnswer) AS VARCHAR)
FROM dbo.PWDQuestion
WHERE CustomerID =22
View Code
--⼩结：
-
-1、本⽂主要介绍证书的创建、删除、查看以及⽤它来修改加密⽅式、进⾏数据的加密和解密。
--2、证书加密和⾮对称密钥加密相对对称密钥加密更为消耗资源。
--下⽂将主要介绍SQL Server中最为令⼈⿎舞的透明数据加密(TDE) --SQL Server 2008中的代码安全（六）：对称密钥加密
--证书和⾮对称密钥使⽤数据库级的内部公钥加密数据，并且使⽤数据库级内部私钥解密数据。
--⽽对称密钥相对简单，它们包含⼀个同时⽤来加密和解密的密钥。
--困此，使⽤对称密钥加密数据更快，并且⽤在⼤数据时更加合适。
--尽管复杂度是考虑使⽤它的因素，但它仍然是⼀个很好的加密数据的选择。
--⽰例⼀、创建对称密钥
--
--对称密钥的特性是：在数据库会话中使⽤它对数据进⾏加密和解密前必须⾸先打开。
--
--创建对称密钥使⽤如下命令:CREATE SYMMETRIC KEY 创建对称密钥。
USE [pratice]
go
-- 创建⼀个⽤于加密对称密钥的⾮对称密钥
CREATE ASYMMETRIC KEY symDemoKey --名称
WITH ALGORITHM = RSA_512 --加密算法
ENCRYPTION BY PASSWORD ='TestSYM456!'
