如何解密 SQL Server 2008 中的存储过程 [英] How to decrypt stored procedure in SQL Server 2008

问题描述

我有一个使用 WITH ENCRYPTION 选项加密的存储过程.现在我想解密那个过程.我已经尝试过一个名为Decryptsp2K"的存储过程，它是在这个论坛中为 SQL 2000 提供的:http://forums.asp.net/t/1516587.aspx/1

但它删除了我的存储过程，而不是解密它.

有没有办法解密 SQL Server 2008 中的存储过程?

解决方案

SQL Server Pro 文章 解密 SQL Server 对象" 在 SQL Server 2008 中仍然有效.

您需要通过 DAC 连接.请参阅 中的文件解密 SQL 2005 存储过程、函数、触发器、views.sql"下载.

总结一下它为以下存储过程定义执行的步骤

CREATE PROC dbo.myproc加密作为选择FOO"

1. 从 sys.sysobjvalues 中的 imageval 列中检索加密对象文本并将其存储在变量 @ContentOfEncryptedObject
2. 根据 DATALENGTH(@ContentOfEncryptedObject)/2 计算 @ObjectDataLength.
3. 生成用 - 字符填充到正确长度的 ALTER PROCEDURE 语句(因此在本例中为 ALTER PROCEDURE [dbo].[myproc] WITH加密为------------)
4. 执行 ALTER 语句，从 sys.sysobjvalues 检索加密版本并将其存储在变量 @ContentOfFakeEncryptedObject 中，然后回滚更改.
5. 生成一个 CREATE PROCEDURE 语句，用 - 字符填充到正确的长度(所以在这种情况下 CREATE PROCEDURE [dbo].[myproc] WITH加密为-----------).这被存储在变量 @ContentOfFakeObject
中

然后它循环 for @i = 1 到 @ObjectDataLength 并使用以下 XOR 计算一次解密一个字符的定义.

NCHAR(UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^(UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))))

<块引用>

更新

Paul White 写了一篇非常好的文章，详细介绍了为什么上述方法有效，并且提供了一种不适用的替代方法依赖于改变对象:WITH 的内部结构加密

I have a stored procedure which is encrypted using the WITH ENCRYPTION option. Now I want to decrypt that procedure. I have already tried a stored procedure called "Decryptsp2K" which is given for SQL 2000 in this forum: http://forums.asp.net/t/1516587.aspx/1

But it deletes my stored procedure, rather than decrypting it.

Is there a way to decrypt a stored procedure in SQL Server 2008?

解决方案

The SQL Server Pro article "Decrypt SQL Server Objects" still works in SQL Server 2008.

You need to connect via the DAC. See the file "Decrypt SQL 2005 stored procedures, functions, triggers, views.sql" in the download.

Just to summarise the steps that it performs for the following stored procedure definition

CREATE PROC dbo.myproc
WITH ENCRYPTION
AS
SELECT 'FOO'

1. Retrieves the encrypted object text from the imageval column in sys.sysobjvalues and stores it in a variable @ContentOfEncryptedObject
2. Calculates @ObjectDataLength from DATALENGTH(@ContentOfEncryptedObject)/2.
3. Generates an ALTER PROCEDURE statement padded out to the correct length with the - character (so in this case ALTER PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS------------)
4. Executes the ALTER statement, retrieves the encrypted version from sys.sysobjvalues and stores that in the variable @ContentOfFakeEncryptedObject then rolls back the change.
5. Generates a CREATE PROCEDURE statement padded out to the correct length with the - character (so in this case CREATE PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS-----------). This gets stored in the variable @ContentOfFakeObject

It then loops through for @i = 1 to @ObjectDataLength and decrypts the definition a character at a time using the following XOR calculation.

NCHAR(
      UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^
      (
          UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^
          UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))
      )
     )

UPDATE

Paul White has written a very nice article that goes into details on why the above works, and that gives an alternate method that doesn't rely on altering the object: The Internals of WITH ENCRYPTION

这篇关于如何解密 SQL Server 2008 中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！