【网学网提醒】：网学会员为广大网友收集整理了，SQLServer数据加密技术，希望对大家有所帮助！
数据用数字方式存储在服务器中并非万无一失.实践证明有太多的方法可以智取SQLServer认证保护,最简单的是通过使用没有口令的sa账号.尽管SQLServer远比它以前的版本安全,但攻击者还是有可能获得存储的数据.因此,数据加密成为更彻底的数据保护战略,即使攻击者得以存取数据,还不得不解密,因而对数据增加了一层保护.SQLServer通过将数据加密作为数据库的内在特性解决了这个问题.它除了提供多层次的密钥和丰富的加密算法外,最大的好处是用户可以选择数据服务器管理密钥.SQLServer服务器支持的加密算法如下:⑴对称式密钥加密(SymmetricKeyEncryption):对称式加密方式对加密和解密使用相同的密钥.通常,这种加密方式在应用中难以实施,因为用同一种安全方式共享密钥很难.但当数据储存在SQLServer中时,这种方式很理想,你可以让服务器管理它.SQLServer提供RC4,RC2,DES和AES系列加密算法.⑵非对称密钥加密(AsymmetricKeyEncryption):非对称密钥加密使用一组公共/私人密钥系统,加密时使用一种密钥,解密时使用另一种密钥.公共密钥可以广泛的共享和透露.当需要用加密方式向服务器外部传送数据时,这种加密方式更方便.SQLServer支持RSA加密算法的512位,1,024位和2,048位密钥强度.⑶数字证书(Certificate):数字证书是一种非对称密钥加密,但是,一个组织可以使用证书并通过数字签名将一组公钥和私钥与其拥有者相关联.SQLServer支持"因特网工程工作组"(IETF)X.509版本3(X.509v3)规范.一个组织可以SQLServer使用外部生成的证书,或者可以使用SQLServer生成证书.(4)pwdencrypt,pwdcompare加密(SQLSERVER本身提供的加密函数)SQLServer采用多级密钥来保护它内部的密钥和数据,如下图所示:
图1SQLServer采用多级密钥保护它内部的密钥和数据图中顶层的服务主密钥,安装SQLServer新实例时自动产生和安装,用户不能删除此密钥,
但数据库管理员能对它进行基本的维护,如备份该密钥到一个加密文件,当其危及到安全时更新它,恢复它.服务主密钥由DPAPI(DataProtectionAPI)管理.DPAPI在Windows2000中引入,建立于Windows的Crypt32API之上.SQLServer管理与DPAPI的接口.服务主密钥本身是对称式加密,用来加密服务器中的数据库主密钥.图中引出箭头的密钥或服务用于保护箭头所指的密钥.所以分服务主密钥(servicemasterkey)数据库主密钥(databasemasterkeys),而数据库主密钥又保护证书(certificates)和非对称密钥(asymmetrickeys).而最底层的对称性密钥(symmetrickeys)被证书,非对称密钥或其他的对称性密钥保护.用户只需通过提供密码来保护这一系列的密钥.加密实战--示例一使用证书加密
数据示例一,使用证书加密数据示例一使用证书加密数据.
--准备工作,创建测试数据库TestDBusemasterIFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'TestDB')dropdatabaseTestDBCREATEDATABASE[TestDB]ONPRIMARY
(NAME=N'TestDB',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=N'TestDB_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB_log.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)gouseTestDB--建立测试数据表CREATETABLEtb(IDintIDENTITY(1,1),datavarbinary(8000));GO--数据库主密钥--1)必须先在该数据库上创建数据库主密钥才能使用createmasterkeyencryptionbypassword='P@ssw0rd'--2)使用数据库主密钥--如果数据库主密钥使用服务密钥进行保护,则在使用时会自动打开openmasterkeydecryptionbypassword='P@ssw0rd'--建立证书一,该证书使用数据库主密钥来加密CREATECERTIFICATECert_Demo1WITH
SUBJECT=N'cert1encryptionbydatabasemasterkey',START_DATE='2008-01-01',EXPIRY_DATE='2099-12-31'GO--建立证书二,该证书使用密码来加密CREATECERTIFICATECert_Demo2ENCRYPTIONBYPASSWORD='liangCK.123'WITHSUBJECT=N'cert1encrptionbypassword',START_DATE='2008-01-01',EXPIRY_DATE='2099-12-31'GO--此时,两个证书已经建立完,现在可以用这两个证书来对数据加密--在对表tb做INSERT时,使用ENCRYPTBYCERT加密INSERTtb(data)SELECTENCRYPTBYCERT(CERT_ID(N'Cert_Demo1'),N'这是证书Cert_Demo1加密的内容');--使用证书Cert_Demo1加密
INSERTtb(data)SELECTENCRYPTBYCERT(CERT_ID(N'Cert_Demo2'),N'这是Cert_Demo2证书加密的内容');--使用证书Cert_Demo2加密
--OK.现在已经对数据加密保证了.现在我们SELECT看看SELECT*FROMtb;--现在对内容进行解密显示.--解密时,使用DECRYPTBYCERTSELECT证书解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo1'),data)),证书解密=CONVERT(NVARCHAR(50),DECRYPTBYCERT(CERT_ID(N'Cert_Demo2'),data,N'liangCK.123'))FROMtb;--使用证书解密时,要指定DECRYPTBYCERT的第三个参数,因为在创建时,指定了ENCRYPTIONBYPASSWORD.所以这里要通过这个密码来解密.否则解密失败--我们可以看到,如果证书不匹配将无法解密.所以返回NULLGO
--删除测试证书与数据表DROPCERTIFICATECert_Demo1;DROPCERTIFICATECert_Demo2;DROPTABLEtb;GO
--示例二使用对称密钥加密数据对称密钥使用证书来加密示例二,使用对称密钥加密数据对称密钥使用证书来加密使用证书来加密.示例二使用对称密钥加密数据,
--准备工作,创建测试数据库TestDBusemasterIFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'TestDB')dropdatabaseTestDBCREATEDATABASE[TestDB]ONPRIMARY
(NAME=N'TestDB',FILENAME=N'C:\ProgramFiles\MicrosoftSQL
Server\MSSQL.1\MSSQL\DATA\TestDB.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=N'TestDB_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB_log.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)gouseTestDB--创建测试数据表tbCREATETABLEtb(IDintIDENTITY(1,1),datavarbinary(8000));GO--建立证书,该证书用于加密对称密钥.CREATECERTIFICATECert_DemoENCRYPTIONBYPASSWORD=N'liangCK.123'WITHSUBJECT=N'certencryptionbypassword',START_DATE='2008-01-01',EXPIRY_DATE='2099-12-31'GO--建立对称密钥,该对称密钥由证书加密.CREATESYMMETRICKEYSym_DemoWITHALGORITHM=DES--使用DES加密算法ENCRYPTIONBYCERTIFICATECert_Demo--使用Cert_Demo证书加密GO
--要使用Sym_Demo对称密钥.必需使用OPENSYMMETRICKEY来打开它OPENSYMMETRICKEYSym_DemoDECRYPTIONBYCERTIFICATECert_DemoWITHPASSWORD=N'liangCK.123'--插入加密数据INSERTtb(data)SELECTENCRYPTBYKEY(KEY_GUID(N'Sym_Demo'),N'这是对称密钥Sym_Demo加密的数据,能显示出来吗?')--关闭密钥CLOSESYMMETRICKEYSym_Demo--插入完加密数据,现在使用SELECT来查询一下数据SELECT*FROMtbGO--现在来解密此数据--同样,还是要先打开对称密钥OPENSYMMETRICKEYSym_DemoDECRYPTIONBYCERTIFICATECert_DemoWITHPASSWORD=N'liangCK.123'SELECTCONVERT(NVARCHAR(50),DECRYPTBYKEY(data))--这里可见,数据已经解密出来了.FROMtbCLOSESYMMETRICKEYSym_DemoGO--删除测试DROPSYMMETRICKEYSym_DemoDROPCERTIFICATECert_DemoDROPTABLEtb
--示例三,使用非对称密钥加密数据示例三使用对称密钥加密数据.使用非示例
--准备工作,创建测试数据库TestDB
usemasterIFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'TestDB')dropdatabaseTestDBCREATEDATABASE[TestDB]ONPRIMARY
(NAME=N'TestDB',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=N'TestDB_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB_log.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)gouseTestDB--1,创建非对称密钥CREATEASYMMETRICKEYasy_TestKeyWITHALGORITHM=RSA_1024ENCRYPTIONBYPASSWORD='123456'GOSELECT*FROMsys.asymmetric_keys--2,创建示例表USEtestdbCREATETABLEtest(EmpIDint,Titlenvarchar(50),Salaryvarbinary(500))GO--3,向表中插入数据,并对Salary列的数据进行加密INSERTINTOtestVALUES(1,'CEO',EncryptByAsymKey(AsymKey_ID('asy_TestKey'),'20000'))INSERTINTOtestVALUES(2,'Manager',EncryptByAsymKey(AsymKey_ID('asy_TestKey'),'10000'))INSERTINTOtestVALUES(3,'DBAdmin',EncryptByAsymKey(AsymKey_ID('asy_TestKey'),'5000'))GO--4,查看表中存放的数据SELECT*FROMtest--5,解密被加密了的数据列
SELECTEmpID,Title,CONVERT(varchar(20),DecryptByAsymKey
(AsymKey_Id('asy_TestKey'),Salary,N'123456'))FROMtest--删除测试DROPASYMMETRICKEYasy_TestKeyDROPTABLEtest
--示例四,还有一种方法加密数据更简单示例四还有一种方法加密数据更简单示例
--就是使用EncryptByPassPhrase--准备工作,创建测试数据库TestDBusemasterIFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'TestDB')dropdatabaseTestDBCREATEDATABASE[TestDB]ONPRIMARY
(NAME=N'TestDB',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=N'TestDB_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB_log.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)gouseTestDB--创建测试数据表tbCREATETABLEtb(IDintIDENTITY(1,1),datavarbinary(8000));GOINSERTtb(data)SELECTEncryptByPassPhrase(N'这是密码,用来加密的',N'这是要加密的内容');--查询加密后的数据select*fromtb--解密SELECTCONVERT(NVARCHAR(50),DECRYPTBYPASSPHRASE(N'这是密码,用来加密的',data))FROMtbGODROPTABLEtb
假如认为对存储在数据库中的数据加密完全是浪费处理器时间和存储空间那就错了.SQLServer中的数据加密是一个非凡的特性,它为客户的数据提供了一个重要的保护层.但应用时要注意,只对那些敏感机密的数据进行保护,因为加密消耗服务器处理器大量的资源,如果对一个有一千万条记录的表的每个字段都加密的话,运行一条没有Where子句的SELECT就可能导致服务器性能的彻底崩溃.pwdencrypt,pwdcompare加密加密:而PWDENCRYPT与PWDCOMPARE函数刚好是一对兄弟,一个负责加密,一个负责比对,但就是没有解密的函数.意思也就是说,使用PWDENCRYPT加密过的资料是无法反解的,类似一种Hash处理方法.而且使用PWDENCRYPT函数加密同样的字串资料,每次回应的结果都会不一样,但是使用PWDCOMPARE函数一样可以对加密过的资料进行比对,以下是简单的使用方法介绍.selectpwdencrypt('ok')执行后的结果:0x010066E6CDE3C8DE9363BE015AC22966456F230432D6485C6B58注意:每次执行selectpwdencrypt('ok')的结果都会不一样,这是一种对密码保护的机制.selectpwdcompare('ok',0x010066E6CDE3C8DE9363BE015AC22966456F230432D6485C6B58)执行后的结果为1或0而已,1代表密码比对成功,0代表密码比对失败.第一个参数是当初加密时的设定的密码字串.第二个参数是当时加密后的二进位值.事实上,SQLServer本身在进行密码验证时,应该也就是用PWDCOMPARE函数在比对的,怎么说呢?你可以先执行以下T-SQL查询出系统登入帐号的password_hash栏位:SELECTpassword_hashFROMmaster.sys.sql_loginsWHEREname='sa'然后再将结果用PWDCOMPARE函数验证一下你设定的sa密码,如果回传值是1就代表
密码正确.declare@Avarbinary(500)set@A=(SELECTpassword_hashFROMmaster.sys.sql_loginsWHEREname='sa')selectpwdcompare('12345',@A)
--测试一张表吧
declare@ttable(namevarchar(10),pwdvarbinary(256))insert@tvalues('u1',pwdencrypt('1234567'))declare@intintselect@int=pwdcompare('1234567',(selectpwdfrom@t),0)beginif@int=1print'你输入的密码正确!'elseprint'你输入的密码错误!'end
其他的一些理论
--第一层服务主密钥--备份服务主密钥backupservicemasterkeytofile='c:\smk.bak'encryptionbypassword='P@ssw0rd'Alterservicemasterkeyregeneraterestoreservicemasterkeyfromfile='c:\smk.bak'decryptionbypassword='P@ssw0rd'--第二层数据库主密钥--1)必须先在该数据库上创建数据库主密钥才能使用createmasterkeyencryptionbypassword='P@ssw0rd'--2)使用数据库主密钥--如果数据库主密钥使用服务密钥进行保护,则在使用时会自动打开openmasterkeydecryptionbypassword='P@ssw0rd'--3)查看数据库主密钥状态select*fromsys.symmetric_keys--4)备份数据库主密钥backupmasterkeytofile='c:\dbk.bak'encryptionbypassword='P@ssw0rd'gorestoremasterkeyfromfile='c:\dbk.bak'decryptionbypassword='P@ssw0rd'encryptionbypassword='P@ssw0rd'--第三层数字证书--1)创建自签名createcertificatecert_Testcertencryptionbypassword='p@ssw0rd'withsubject='TestCert1',start_date='1/31/2006',expiry_date='1/31/2099'--2)创建非对称密钥createasymmetrickeyasy_Key1withalgorithm=RSA_2048encryptionbypassword='P@ssw0rd'
--3)创建对称密钥createsymmetrickeysy_Key1withalgorithm=DESencryptionbycertificatecert_Testcert--4)使用密钥(使用前必须打开)opensymmetrickeysy_Key1decryptionbycertificatecert_TestcertWITHPASSWORD='p@ssw0rd'数据列加密-使用对称密钥加密大量的列数据-使用证书,非对称密钥保护对称密钥-防止绕过加密数据列的攻击注:在加密列上的索引将变得无效加密数据列的长度增长,建议使用varbinary(max)数据类型修改已有的DML语句以支持加密的数据列-----***********示例1了解数据库加密体系结构*****------************(1)服务主密钥--准备工作--创建测试数据库TestDBCREATEDATABASE[TestDB]ONPRIMARY
(NAME=N'TestDB',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB.mdf',SIZE=3072KB,MAXSIZE=UNLIMITED,FILEGROWTH=1024KB)LOGON(NAME=N'TestDB_log',FILENAME=N'C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\DATA\TestDB_log.ldf',SIZE=1024KB,MAXSIZE=2048GB,FILEGROWTH=10%)gouseTestDB--1)备份服务主密钥backupservicemasterkeytofile='f:\smk.bak'encryptionbypassword='p@ssw0rd'--2)生成新的主密钥Alterservicemasterkeyregenerate--3)从备份文件还原服务主密钥Restoreservicemasterkeyfromfile='f:\smk.bak'decryptionbypassword='p@ssw0rd'--**
***********(2)数据库主密钥
--1)为数据库创建数据库主密钥createmasterkeyencryptionbypassword='p@ssw0rd'--2)查看数据库加密状态select[name],is_master_key_encrypted_by_serverfromsys.databaseswherename='TestDB'--3)查看数据库主密钥的信息select*fromsys.symmetric_keys--4)备份数据库主密钥backupmasterkeytofile='f:\dbkey.bak'encryptionbypassword='p@ssw0rd'--5)删除服务主密钥对数据库主密钥的保护,创建非对称密钥成功,自动使用服务主密钥解密并使用该数据库主密钥createasymmetrickeyasy_Testkey1withalgorithm=RSA_1024--删除服务主密钥对数据库主密钥的保护altermasterkeydropencryptionbyservicemasterkey--查看数据库加密状态select[name],is_master_key_encrypted_by_serverfromsys.databaseswherename='TestDB'--创建非对称密钥失败,因为数据库主密钥未打开createasymmetrickeyasy_Testkey2withalgorithm=RSA_1024--打开数据库主密钥openmasterkeydecryptionbypassword='p@ssw0rd'select*fromsys.openkeys--创建非对称密钥成功createasymmetrickeyasy_Testkey2withalgorithm=RSA_1024--恢复服务主密钥对数据库主密钥的保护altermasterkeyaddencryptionbyservicemasterkeyclosemasterkey--*****(3)证书--1)创建自签名证书createcertificatecert_Testcertencryptionbypassword='p@ssw0rd'withsubject='TestCert1',start_date='1/31/2006',expiry_date='1/31/2099'
select*fromsys.certificates--2)从文件导入证书Createcertificatecert_TestCert2Fromfile='f:\Testcert.cer'--3)备份导出证书和密钥backupcertificatecert_Testcerttofile='f:\Testcert.cer'withprivatekey(decryptionbypassword='p@ssw0rd',file='f:\TestCert_pvt',--私钥encryptionbypassword='p@ssw0rd')--4)使用证书加解密数据--加密:使用证书的公钥declare@cleartextvarbinary(200)declare@ciphervarbinary(200)set@cleartext=convert(varbinary(200),'Testtextstring')set@cipher=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)select@cipher--解密:使用证书的私钥selectconvert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd'))as[cleartext]--5)删除证书私钥altercertificatecert_TestCertremoveprivatekey--加密成功declare@cleartext1varbinary(200)declare@cipher1varbinary(200)set@cleartext1=convert(varbinary(200),'Testtextstring')set@cipher1=EncryptByCert(Cert_ID('cert_TestCert'),@cleartext)select@cipher1--解密失败:因为私钥被删除selectconvert(varchar(200),DecryptByCert(Cert_ID('cert_TestCert'),@cipher,N'p@ssw0rd'))as[cleartext]
