前段时间在客户公司，有一个很活泼的程序员给我展示他写的SQL Server 加密功能。由于测试库和正式库在同一台服务器里，于是一个不小心把正式库的所有存储过程、函数、视图全加密了。还好加密的方式只是在 AS 位置前增加了 WITH ENCRYPTION ，虽然不能查看内容但不影响程序的运行。
即使是这种简单的加密，解密过程也很复杂。为了缓解他焦灼的心情，于是我接下了这个解密的任务。在各种查找和尝试后，问题解决了。顺便也整理了全套加密和解密的方法，以便不时之需。
加密过程
可以参考这篇文章来创建加密的存储过程：（sp_EncryptObject）。此存储过程的方法是在存储过程、函数或视图的“As”位置前加上“with encryption”。如果是触发器的话就在“for”之前加“with encryption”。
具体实现代码如下：（SQL Server 2012 和 2016测试OK）
Use master
Go
if object_ID ( '[sp_EncryptObject]') is not null
Drop Procedure [sp_EncryptObject]
Go
create procedure sp_EncryptObject
(
@Object sysname= 'All'
)
as
/*
当@Object=All的时候，对所有的函数，存储过程，视图和触发器进行加密
调用方法：
1\. Execute sp_EncryptObject 'All'
2\. Execute sp_EncryptObject 'ObjectName'
*/
begin
set nocount on
if @Object <> 'All'
begin
if not exists(select 1 from sys. objects a where a .object_id = object_id( @Object ) And a .type in( 'P', 'V' ,'TR' , 'FN', 'IF' ,'TF' ))
begin
--SQL Server 2008
--raiserror 50001 N'无效的加密对象！加密对象必须是函数，存储过程，视图或触发器。'
--SQL Server 2012
throw 50001, N'无效的加密对象！加密对象必须是函数，存储过程，视图或触发器。' , 1
return
end
if exists( select 1 from sys. sql_modules a where a .object_id = object_id( @Object ) and a .definition is null)
begin
--SQL Server 2008
--raiserror 50001 N'对象已经加密！'
--SQL Server 2012
throw 50001, N'对象已经加密！' ,1
return
end
end
declare @sql nvarchar (max ), @C1 nchar ( 1), @C2 nchar( 1 ),@type nvarchar (50 ),@Replace nvarchar (50 )
set @C1 = nchar( 13 )
set @C2 = nchar( 10 )
declare cur_Object
cursor for
select object_name ( a. object_id ) As ObjectName , a. definition
from sys . sql_modules a
inner join sys. objects b on b .object_id = a. object_id
and b. is_ms_shipped =0
and not exists(select 1
from sys . extended_properties x
where x. major_id =b .object_id
and x. minor_id =0
and x. class =1
and x. name ='microsoft_database_tools_support'
)
where b. type in( 'P' ,'V' , 'TR', 'FN' ,'IF' , 'TF')
and ( b .name =@Object or @Object= 'All' )
and b. name <>'sp_EncryptObject'
and a. definition is not null
order by Case
when b. type = 'V' then 1
when b. type = 'TR' then 2
when b. type in( 'FN' ,'IF' , 'TF') then 3
else 4 end , b. create_date ,b .object_id
open cur_Object
fetch next from cur_Object into @Object, @sql
while @@fetch_status= 0
begin
Begin Try
if objectproperty ( object_id( @Object ),'ExecIsAfterTrigger' )= 0 set @Replace ='As' ; else set @Replace= 'For ';
if (patindex ( '%'+ @C1 +@C2 +@Replace + @C1+ @C2 +'%' , @sql)> 0 )
begin
set @sql= Replace (@sql ,@C1 +@C2 +@Replace + @C1+ @C2 ,@C1 +@C2 +'With Encryption'+ @C1+ @C2 +@Replace + @C1+ @C2 )
end
else if ( patindex( '%' +@C1 +@Replace + @C1+ '%' ,@sql )>0 )
begin
set @sql= Replace (@sql ,@C1 +@Replace + @C1, @C1 +'With Encryption' + @C1+ @Replace +@C1 )
end
else if ( patindex( '%' +@C2 +@Replace + @C2+ '%' ,@sql )>0 )
begin
set @sql= Replace (@sql ,@C2 +@Replace + @C2, @C2 +'With Encryption' + @C2+ @Replace +@C2 )
end
else if ( patindex( '%' +@C2 +@Replace + @C1+ '%' ,@sql )>0 )
begin
set @sql= Replace (@sql ,@C2 +@Replace + @C1, @C1 +'With Encryption' + @C2+ @Replace +@C1 )
end
else if ( patindex( '%' +@C1 +@C2 +@Replace + '%', @sql )>0 )
begin
set @sql= Replace (@sql ,@C1 +@C2 +@Replace , @C1+ @C2 +'With Encryption'+ @C1+ @C2 +@Replace )
end
else if ( patindex( '%' +@C1 +@Replace + '%', @sql )>0 )
begin
set @sql= Replace (@sql ,@C1 +@Replace , @C1+ 'With Encryption' +@C1 +@Replace )
end
else if ( patindex( '%' +@C2 +@Replace + '%', @sql )>0 )
begin
set @sql= Replace (@sql ,@C2 +@Replace , @C2+ 'With Encryption' +@C2 +@Replace )
end
set @type =
case
when object_id ( @Object, 'P' )>0 then 'Proc'
when object_id ( @Object, 'V' )>0 then 'View'
when object_id ( @Object, 'TR' )>0 then 'Trigger'
when object_id ( @Object, 'FN' )>0 or object_id ( @Object, 'IF' )>0 or object_id ( @Object, 'TF' )>0 then 'Function'
end
set @sql= Replace (@sql ,'Create ' + @type, 'Alter ' +@type )
Begin Transaction
exec (@sql )
print N'已完成加密对象(' + @type+ ')：' +@Object
Commit Transaction
End Try
Begin Catch
Declare @Error nvarchar ( 2047)
Set @Error= 'Object: ' +@Object + @C1+ @C2 +'Error: ' + Error_message()
Rollback Transaction
print @Error
print @sql
End Catch
fetch next from cur_Object into @Object, @sql
end
close cur_Object
deallocate cur_Object
end
Go
exec sp_ms_marksystemobject 'sp_EncryptObject' --标识为系统对象
go
执行加密
use test
go
exec sp_EncryptObject 'all' --加密所有内容，指定名称加密指定内容
go
解密过程
因为我所遇到的情况涉及的存储过程比较多，一个个解密不现实。于是在网上搜到了这个工具：dbForge SQL Decryptor 。这是一款免费软件和SSMS长得比较像。这款软件如果用常规账户连接的话解密速度特别慢，推荐使用DAC方式连接，速度飞快。
服务器默认DAC连接是关闭的，可以在服务器示例右键→Facets→外围应用配置器中启用远程DAC。
image.png
dbForge SQL Decryptor通过DAC连接服务器：
image.png
批量解密所有加密存储过程、函数、视图和触发器。
image.png
SSMS也可以使用DAC连接
SSMS通过DAC连接只能在文件→新建→数据库引擎查询处登录。其中服务器名称前要加 admin: ，登录完成默认只显示空白的编辑窗口。
参考文献：
批量解密SQLSERVER数据库中的各种对象的工具dbForge SQL Decryptor2.1.11 对存储过程进行加密和解密(SQL 2008/SQL 2012) MS SQL专用管理员连接DAC
本文为原创文章，转载请注明出处！欢迎关注任前程博客 https://renqiancheng.com/，第一时间看后续精彩文章。
网友评论