SQL Server Magazine

Step 8
In SQL Server 2000, delete the dbo.uspMyProc object and recreate it using the WITH ENCRYPTION option.

IF OBJECT_ID(‘dbo.uspMyProc')
  IS NOT NULL
  DROP PROC dbo.uspMyProc 
  GO 
  CREATE PROC dbo.uspMyProc 
  WITH ENCRYPTION 
  AS SELECT 1 AS MyCol

Step 9
Check the content of the text and ctext columns in syscomments for the newly created stored procedure by using the following code:

SELECT text, ctext, * 
FROM syscomments 
  WHERE id = object_id(‘dbo. 
  uspMyProc')

The text column now contains what appears to be a combination of scrambled characters. You can use online resources that provide automatic hex-to-text conversion to easily check that the hex content found in the ctext column doesn't represent the clear-text version of the object code. Most likely, this is the hex stream that corresponds to the encrypted text.

Note that syscomments contains another column called encrypted, which is set to 1 for the object at hand. You can use the encrypted column to identify encrypted objects in SQL Server.

Step 10
Moving along to SQL Server 2005, we now create the encrypted object just as we did in Step 8 by running the code we used in that step.

Step 11
Working in DAC mode, check the syscomments and sys.sysobjvalues tables for entries that are related to the dbo.uspMyProc stored procedure by running the following code:

SELECT * 
  FROM syscomments 
  WHERE id = object_id(‘dbo. 
  uspMyProc')
  SELECT * 
  FROM sys.sysobjvalues 
  WHERE objid = object_id(‘dbo. 
  uspMyProc')

Somewhat surprisingly, the text and ctext columns in syscomments are now null. However, a hex entry is still available in sys.sysobjvalues. Therefore, it seems safe to assume that this value captures the hex format of the encrypted code.

Cracking Along
The algorithm that I describe in this section has proven efficient in SQL Server 2000 for revealing the underlying code text of obfuscated objects. As it turns out, the same algorithm also works well in SQL Server 2005.

Before we begin decoding database objects, recall that the available resources include the obfuscated object name and owner (or schema), the object type, and the hexadecimal value of the encoded text. Here, the discussion is focused on stored procedures, but the same rules also apply to all other text-based objects.

The de-obfuscation algorithm can be summarized as follows: First, record the hex entry of the encoded text. Then, alter the encoded object by using a dummy ALTER PROC statement and capture the encoded hex stream of this fake entity. To ensure minimal impact on the database, the ALTER statement is executed inside a transaction and is immediately rolled back. A CREATE statement that contains the same dummy content is maintained for reference. Finally, the original stored procedure code is decoded one character at a time by performing an exclusive OR (also known as XOR) operation between characters in the dummy CREATE statement, the original hex value, and the hex stream that corresponds to the fake object. In this section, we'll walk through an example using these steps with a stored procedure called dbo.uspMyEncProc. For simplicity, we assume that the original CREATE statement for this object doesn't exceed 4,000 characters. The enthusiastic reader is encouraged to refer to the T-SQL scripts associated with this article, in which the case of longer code is handled appropriately. You can download the full project scripts at http://www.sqlmag.com, InstantDoc ID 95728.

Prev. page     1 [2] 3     next page