【转载】破解SQL Server加密的存储过程的方法(含 sql 2000与sql 2005版)

it2022-05-09 54

转载自：http://www.cnblogs.com/zhou__zhou/archive/2010/04/17/sqlserver.html http://www.cnblogs.com/huqingyu/archive/2007/12/17/997296.html

SQLServer2005里使用with encryption选项创建的存储过程仍然和sqlserver2000里一样，都是使用XOR进行了的加密。和2000不一样的是，在2005的系统表syscomments里已经查不到加密过的密文了。要查密文必须使用DAC（专用管理员连接）连接到数据库后，在系统表sys.sysobjvalues查询，该表的列imageval存储了相应的密文。具体可以使用下面的查询：SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) AND valclass = 1 AND subobjid = 1

案例2:有接手别人工作,而对于业务存储过程中使用加密了内容的,维护难受啊,GOOGLE搜索,终于不负有心人.Sql2000的。

CREATE PROCEDURE usp_decrypt(@objectname varchar(50)) AS begin set nocount on --：j9988 copyright:2004.07.15 --V3.2 --破解字节不受限制，适用于SQLSERVER2000存储过程，函数，视图，触发器 --修正上一版"视图触发器"不能正确解密错误 --发现有错，请E_MAIL：j9988@tom.com begin tran declare @objectname1 varchar(100),@orgvarbin varbinary(8000) declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000) DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000) declare @i int,@status int,@type varchar(10),@parentid int declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@objectname) create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int) insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectname) select @number=max(number) from #temp set @k=0 while @k<=@number begin if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k) begin if @type='P' set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectname +';'+rtrim(@k)+' WITH ENCRYPTION AS ' else 'ALTER PROCEDURE '+ @objectname+' WITH ENCRYPTION AS ' end) if @type='TR' begin declare @parent_obj varchar(255),@tr_parent_xtype varchar(10) select @parent_obj=parent_obj from sysobjects where id=object_id(@objectname) select @tr_parent_xtype=xtype from sysobjects where id=@parent_obj if @tr_parent_xtype='V' begin set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 ' end else begin set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 ' end end if @type='FN' or @type='TF' or @type='IF' set @sql1=(case @type when 'TF' then 'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end ' when 'FN' then 'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns char(1) with encryption as begin return @a end' when 'IF' then 'ALTER FUNCTION '+ @objectname+'(@a char(1)) returns table with encryption as return select @a as a' end) if @type='V' set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f' set @q=len(@sql1) set @sql1=@sql1+REPLICATE('-',4000-@q) select @sql2=REPLICATE('-',8000) set @sql3='exec(@sql1' select @colid=max(colid) from #temp where number=@k set @n=1 while @n<=CEILING(1.0*(@colid-1)/2) and len(@sql3)<=3996 begin set @sql3=@sql3+'+@' set @n=@n+1 end set @sql3=@sql3+')' exec sp_executesql @sql3,N'@sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2 end set @k=@k+1 end set @k=0 while @k<=@number begin if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k) begin select @colid=max(colid) from #temp where number=@k set @n=1 while @n<=@colid begin select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectname) and colid=@n and number=@k) if @n=1 begin if @type='P' SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectname +';'+rtrim(@k)+' WITH ENCRYPTION AS ' else 'CREATE PROCEDURE '+ @objectname +' WITH ENCRYPTION AS ' end) if @type='FN' or @type='TF' or @type='IF' SET @OrigSpText2=(case @type when 'TF' then 'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end ' when 'FN' then 'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns char(1) with encryption as begin return @a end' when 'IF' then 'CREATE FUNCTION '+ @objectname+'(@a char(1)) returns table with encryption as return select @a as a' end) if @type='TR' begin if @tr_parent_xtype='V' begin set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 ' end else begin set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 ' end end if @type='V' set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f' set @q=4000-len(@OrigSpText2) set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q) end else begin SET @OrigSpText2=REPLICATE('-', 4000) end SET @i=1 SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2)) WHILE @i<=datalength(@OrigSpText1)/2 BEGIN SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^ (UNICODE(substring(@OrigSpText2, @i, 1)) ^ UNICODE(substring(@OrigSpText3, @i, 1))))) SET @i=@i+1 END set @orgvarbin=cast(@OrigSpText1 as varbinary(8000)) set @resultsp=(case when @encrypted=1 then @resultsp else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end) end) print @resultsp set @n=@n+1 end end set @k=@k+1 end drop table #temp rollback tran end

再有一个Sql 2005 版的

create PROCEDURE [dbo].[usp_decrypt] (@procedure sysname = NULL, @revfl int = 1) AS /**//* 王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com 目前这个存储过程只能解密存储过程，至于解密函数、触发器、视图的存储过程本网站会进一步关注，调用形式为： exec dbo.usp_decrypt procedure,0 如果第二个参数使用1的话，会给出该存储过程的一些提示。 --版本3.0 修正存储过程过长引起的问题 */ SET NOCOUNT ON IF @revfl = 1 BEGIN PRINT '警告：该存储过程会删除并重建原始的存储过程。' PRINT ' 在运行该存储过程之前确保你的数据库有一个备份。' PRINT ' 该存储过程通常应该运行在产品环境的一个备份的非产品环境下。' PRINT ' 为了运行这个存储过程，将参数@refl的值更改为0。' RETURN 0 END DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int select @maxColID = max(subobjid) FROM sys.sysobjvalues WHERE objid = object_id(@procedure) --select @maxColID as 'Rows in sys.sysobjvalues' select @procNameLength = datalength(@procedure) + 29 DECLARE @real_01 nvarchar(max) DECLARE @fake_01 nvarchar(max) DECLARE @fake_encrypt_01 nvarchar(max) DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max) declare @objtype varchar(2),@ParentName nvarchar(max) select @real_decrypt_01a = '' --提取对象的类型如是存储过程还是函数，如果是触发器，还要得到其父对象的名称 select @objtype=type,@parentname=object_name(parent_object_id) from sys.objects where [object_id]=object_id(@procedure) -- 从sys.sysobjvalues里提出加密的imageval记录 SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 order by subobjid) --创建一个临时表 create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL , [real_decrypt] NVARCHAR(MAX) ) --开始一个事务，稍后回滚 BEGIN TRAN --更改原始的存储过程，用短横线替换 if @objtype='P' SET @fake_01='ALTER PROCEDURE '+ @procedure +' WITH ENCRYPTION AS '+REPLICATE(cast('-'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength) else if @objtype='FN' SET @fake_01='ALTER FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END' else if @objtype='V' SET @fake_01='ALTER view '+ @procedure +' WITH ENCRYPTION AS select 1 as col /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' else if @objtype='TR' SET @fake_01='ALTER trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' EXECUTE (@fake_01) --从sys.sysobjvalues里提出加密的假的 SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid = object_id(@procedure) and valclass = 1 order by subobjid ) if @objtype='P' SET @fake_01='Create PROCEDURE '+ @procedure +' WITH ENCRYPTION AS '+REPLICATE(cast('-'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength) else if @objtype='FN' SET @fake_01='CREATE FUNCTION '+ @procedure +'() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/ END' else if @objtype='V' SET @fake_01='Create view '+ @procedure +' WITH ENCRYPTION AS select 1 as col /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' else if @objtype='TR' SET @fake_01='Create trigger '+ @procedure +' ON '+@parentname+'WITH ENCRYPTION AFTER INSERT AS RAISERROR (''N'',16,10) /*'+REPLICATE(cast('*'as nvarchar(max)), datalength(@real_01) /2 - @procNameLength)+'*/' --开始计数 SET @intProcSpace=1 --使用字符填充临时变量 SET @real_decrypt_01 = replicate(cast('A'as nvarchar(max)), (datalength(@real_01) /2 )) --循环设置每一个变量，创建真正的变量 --每次一个字节 SET @intProcSpace=1 --如有必要，遍历每个@real_xx变量并解密 WHILE @intProcSpace<=(datalength(@real_01)/2) BEGIN --真的和假的和加密的假的进行异或处理 SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1, NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^ (UNICODE(substring(@fake_01, @intProcSpace, 1)) ^ UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1))))) SET @intProcSpace=@intProcSpace+1 END --通过sp_helptext逻辑向表#output里插入变量 insert #output (real_decrypt) select @real_decrypt_01 -- select real_decrypt AS '#output chek' from #output --测试 -- ------------------------------------- --开始从sp_helptext提取 -- ------------------------------------- declare @dbname sysname ,@BlankSpaceAdded int ,@BasePos int ,@CurrentPos int ,@TextLength int ,@LineId int ,@AddOnLen int ,@LFCR int --回车换行的长度 ,@DefinedLength int ,@SyscomText nvarchar(max) ,@Line nvarchar(255) Select @DefinedLength = 255 SELECT @BlankSpaceAdded = 0 --跟踪行结束的空格。注意Len函数忽略了多余的空格 CREATE TABLE #CommentText (LineId int ,Text nvarchar(255) collate database_default) --使用#output代替sys.sysobjvalues DECLARE ms_crs_syscom CURSOR LOCAL FOR SELECT real_decrypt from #output ORDER BY ident FOR READ ONLY --获取文本 SELECT @LFCR = 2 SELECT @LineId = 1 OPEN ms_crs_syscom FETCH NEXT FROM ms_crs_syscom into @SyscomText WHILE @@fetch_status >= 0 BEGIN SELECT @BasePos = 1 SELECT @CurrentPos = 1 SELECT @TextLength = LEN(@SyscomText) WHILE @CurrentPos != 0 BEGIN --通过回车查找行的结束 SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText, @BasePos) --如果找到回车 IF @CurrentPos != 0 BEGIN --如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续 While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'') SELECT @BasePos = @CurrentPos+2 INSERT #CommentText VALUES( @LineId, @Line ) SELECT @LineId = @LineId + 1 SELECT @Line = NULL END ELSE --如果回车没找到 BEGIN IF @BasePos <= @TextLength BEGIN --如果@Lines长度的新值大于定义的长度 While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @TextLength-@BasePos+1 ) > @DefinedLength BEGIN SELECT @AddOnLen = @DefinedLength - (isnull(LEN(@Line),0) + @BlankSpaceAdded) INSERT #CommentText VALUES ( @LineId, isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')) SELECT @Line = NULL, @LineId = @LineId + 1, @BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0 END SELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'') if LEN(@Line) < @DefinedLength and charindex(' ', @SyscomText, @TextLength+1 ) > 0 BEGIN SELECT @Line = @Line + ' ', @BlankSpaceAdded = 1 END END END END FETCH NEXT FROM ms_crs_syscom into @SyscomText END IF @Line is NOT NULL INSERT #CommentText VALUES( @LineId, @Line ) select Text from #CommentText order by LineId CLOSE ms_crs_syscom DEALLOCATE ms_crs_syscom DROP TABLE #CommentText -- ------------------------------------- --结束从sp_helptext提取 -- ------------------------------------- --删除用短横线创建的存储过程并重建原始的存储过程 ROLLBACK TRAN DROP TABLE #output

已经打包好的文件下载在这里 http://files.cnblogs.com/zhou__zhou/T-SQL破解加密的存储过程.rar

引用网摘:http://www.windbi.com/showtopic-153.aspxhttp://www.easte.cn/post/SQL_Server_SP_Decrypt_ALL.html

解密SQL2005存储过程要使用DAC模式连接到SQL2005数据库处理

转载自：

http://hi.baidu.com/lzj1981/blog/item/ed436a62f5eb25d7e6113a0f.html

http://www.taotechinfo.com/collect/2010322/n13188930.html

如果你在网上找到破解SQL2005加密的存储过程，运行时提示不成功，那么你可要使用DAC模式连接到SQL2005数据库来处理，方法如下：

1、打开 SQL Server Management Studio 2、登录框按“取消”3、工具栏上的第一个图标：数据库引擎查询4、服务器名称前加“ADMIN:”，如：ADMIN:hostname\sql2005 5、选择“Windows 身份验证”，或输入sa和密码，登录。已打开 SQL Server Management Studio 并连接，忽略1、2步。

如果出现：provider: SQL 网络接口, error: 43 - 在获取专用的管理员连接(DAC)端口时出错。确保正在运行 SQL 浏览器，或检查错误日志中是否有该端口号的错误

原因可能是：The firewall on the server has refused the connection.A specified SQL Server instance name is not valid.The SQL Server Browser service (sqlbrowser) is not started.（打开Sql Server里的 SQL Server Brower 数据库浏览服务）

需要执行：

sp_configure 'remote admin connections', 1GORECONFIGURE这样一般都可以连接成功的

SQL Server2005中如何知道某个存储过程是否已加密？

转载自：http://www.cnblogs.com/gis_gps/archive/2009/01/16/1377133.html

最近在写一个小软件，需要知道当前那些存储过程已经被加密，如果被加密，则调用解密过程，将其解密，还原明文保存。基于sql server2005解密已加密的存储过程，网上有成熟的代码可供参考，但如何通过sql 语句的方式知道当前数据库中的存储过程是否已加密呢？？

最初我就认为加密与不加密的存储过程肯定是一个标记位，存放在某个系统表中，但这个系统表在那里？我却不知道，曾通过google或msdn去查找，但没有找到相关资料，甚至准备写邮件咨询邹建等大侠....

突然想到，原来经常利用sp_helptext查看某个未加密过程的明文，那如果用sp_helptext查看已加密的过程，会有什么反映呢。结果很明确，系统提示：对象***文件已加密！

呵呵，既然sp_helptext知道这个过程已经加密，那我查看一下sp_helptext源码不就可以知道，它是如何判断出该过程是否已加密的吗？

　于是，输入：exec sp_helptext sp_helptext　，屏幕上果然输出sp_helptext的明文，从sp_helptext中我们很容易找到这行code:

select text from syscomments where id = @objid and encrypted = 0............

这段代码意思很明确，查询输出未加密的过程，那么我所需要的就是：

　　select * from syscomments where encrypted = １　

　好了，说到这里，我想各位已经很清楚知道如何解决本文开头所提出的问题！祝各位好运！

===================================================================

示例

加密:

create procedure p_test

with encryption

select * from test

解密：

use [test]exec sp_decrypt 'p_test'

转载于:https://www.cnblogs.com/goto/archive/2012/09/19/2693290.html

相关资源：解密SQL2008加密过的存储过程方法及脚本

专利

最新回复(0)