如何解密 SQL Server 2008 中的存储过程

How to decrypt stored procedure in SQL Server 2008

提问by Upendra Chaudhari

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

我有一个使用 WITH ENCRYPTION 选项加密的存储过程。现在我想解密那个过程。我已经尝试了一个名为“Decryptsp2K”的存储过程，该过程在此论坛中为 SQL 2000 提供：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?

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

回答by Martin Smith

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

SQL Server Pro 文章“解密 SQL Server 对象”在 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.

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

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 imagevalcolumn in sys.sysobjvaluesand stores it in a variable @ContentOfEncryptedObject
2. Calculates @ObjectDataLengthfrom DATALENGTH(@ContentOfEncryptedObject)/2.
3. Generates an ALTER PROCEDUREstatement padded out to the correct length with the -character (so in this case ALTER PROCEDURE [dbo].[myproc] WITH ENCRYPTION AS------------)
4. Executes the ALTERstatement, retrieves the encrypted version from sys.sysobjvaluesand stores that in the variable @ContentOfFakeEncryptedObjectthen rolls back the change.
5. Generates a CREATE PROCEDUREstatement 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

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

It then loops through for @i = 1 to @ObjectDataLengthand decrypts the definition a character at a time using the following XORcalculation.

然后它循环遍历@i = 1 to @ObjectDataLength并使用以下XOR计算一次一个字符解密定义。

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

更新

Paul White 写了一篇非常好的文章，详细介绍了上述工作的原因，并提供了一种不依赖于更改对象的替代方法：The Internals ofWITH ENCRYPTION

回答by LopesC

If you want to decrypt procedure or any other encrypted object, check out ApexSQL Decrypt.

如果要解密程序或任何其他加密对象，请查看ApexSQL Decrypt。

It's a free standalone tool, with an option to integrate it into SSMS, preview original DDL script, and create Alter or Create decryption scripts.

它是一个免费的独立工具，可选择将其集成到 SSMS、预览原始 DDL 脚本以及创建更改或创建解密脚本。

From a standalone tool you can connect to multiple servers and decrypt multiple objects at once.

通过独立工具，您可以连接到多个服务器并一次解密多个对象。

dbForge SQL Decryptoris the other tool that can help you out in this case as well.

dbForge SQL Decryptor是另一个可以在这种情况下帮助您的工具。

回答by gbn

Many older tools stopped working with SQL Server 2005+. Note you must be using the Dedicated Admin Connection

许多旧工具停止与 SQL Server 2005+ 一起使用。请注意，您必须使用Dedicated Admin Connection

A quick search shows several options.

快速搜索会显示多个选项。

• http://www.sql-shield.com/decrypt-stored-procedure.html

• http://www.sql-shield.com/decrypt-stored-procedure.html

回答by Ankit Bhalala

Decrypt Stored procedure of SQL SERVER

解密SQL SERVER的存储过程

You can use third party tool for decrypting your encrypted stored procedure.

您可以使用第三方工具来解密您的加密存储过程。

Download that tool-: It's freeware

下载该工具-：它是免费软件

https://www.devart.com/dbforge/sql/sqldecryptor/download.html

https://www.devart.com/dbforge/sql/sqldecryptor/download.html

回答by Jineesh Uvantavida

dbForge Sql Decryptorhelped me to decrypt the encrypted stored procedure. Details are available here

dbForge Sql Decryptor帮助我解密了加密的存储过程。详情请点击此处