sqlserver加密解密

发表于

简介

加密是指通过使用密钥或密码对数据进行模糊处理的过程。在SQL Server中，加密并不能替代其他的安全设置，比如防止未被授权的人访问数据库

加密类型

对称加密(DES,3DES,TDEA,RC5,IDEA,BLOWFISH)、非对称加密（rsa），不可逆加密(md5,sha)

sqlserver中的加密简介

在SQL Server2000和以前的版本，是不支持加密的。所有的加密操作都需要在程序中完成。这导致一个问题，数据库中加密的数据仅仅是对某一特定程序有意义，而另外的程序如果没有对应的解密算法，则数据变得毫无意义。

到了SQL Server2005，引入了列级加密。使得加密可以对特定列执行，这个过程涉及4对加密和解密的内置函数

SQL Server 2008时代，则引入的了透明数据加密（TDE），所谓的透明数据加密，就是加密在数据库中进行，但从程序的角度来看就好像没有加密一样，和列级加密不同的是，TDE加密的级别是整个数据库。使用TDE加密的数据库文件或备份在另一个没有证书的实例上是不能附加或恢复的。

SQL Server中的加密层次结构

ShowImage
由图可知，加密是分层级的。每一个数据库实例都拥有一个服务主密钥(Service Master Key),对应图中的橙色部分。这个密钥是整个实例的根密钥，在实例安装的时候自动生成,其本身由Windows提供的数据保护API进行保护(Data Pertection API)，服务主密钥除了为其子节点提供加密服务之外，还用于加密一些实例级别的信息，比如实例的登录名密码或者链接服务器的信息。

1.创建服务器主秘钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD =’Password’

–创建证书
CREATE CERTIFICATE CertTest
ENCRYPTION BY PASSWORD = ‘Password’
with SUBJECT = ‘CertificateTest’
GO
–创建非对称密钥
CREATE ASYMMETRIC KEY AsymmetricTest
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = ‘Password’;
GO
–创建对称密钥
CREATE SYMMETRIC KEY SymmetricTest
WITH ALGORITHM = AES_256
ENCRYPTION BY PASSWORD = ‘Password’;
GO

SQL Server中的数据列加密(Column-level Encryption)

SQL Server在2005引入了列加密的功能。使得可以利用证书，对称密钥和非对称密钥对特定的列进行加密。在具体的实现上，根据加密解密的方式不同，内置了4对函数用于加密解密:

EncryptByCert() 和DecryptByCert()—利用证书对数据进行加密和解密
EncryptByAsymKey() and DecryptByAsymKey()—利用非对称密钥对数据进行加密和解密
EncryptByKey() and DecryptByKey()—利用对称密钥对数据进行加密和解密
EncryptByPassphrase() and DecryptByPassphrase()—利用密码字段产生对称密钥对数据进行加密和解密
因此，加密数据列使用起来相对比较繁琐，需要程序在代码中显式的调用SQL Server内置的加密和解密函数，这需要额外的工作量，并且，加密或解密的列首先需要转换成Varbinary类型。

例子：
create table test(
id varchar(32) not null,
name varchar(50) not null,
password varchar(2048) null
)

–插入加密后的密码到数据库当中(证书加密)
insert into test (id,name,password)
values(‘1’,’test’,ENCRYPTBYCERT(Cert_ID(‘CertificateTest’),convert(varchar(2048),’test’)))
–查询解密后的密码(证书解密)
select convert(varchar(50),DECRYPTBYCERT(Cert_ID(‘CertificateTest’),password,’Password’) as password from test

–插入加密后的密码到数据库当中(非对称秘钥)
insert into test (id,name,password)
values(‘1’,’test’,EncryptByAsymKey(ASYMKEY_ID(‘AsymmetricTest’),convert(varchar(2048),’test’)))
–查询解密后的密码(非对称秘钥)
select convert(varchar(50),DecryptByAsymKey(ASYMKEY_ID(‘AsymmetricTest’),password,’Password’) as password from test

–插入加密后的密码到数据库当中(对称秘钥)
insert into test (id,name,password)
values(‘1’,’test’,EncryptByKey(Key_GUID(‘SymmetricTest’),’test’))
–查询解密后的密码(对称秘钥)
select convert(varchar(50),DecryptByKey(password)) as password from test

参考地址
细说SQL Server中的加密
SQL - SQL Server 2008 加密與解密實作心得 (一)
SQL - SQL Server 2008 加密與解密實作心得 (二)
T-SQL问题解决集锦——数据加解密
ShowImage