Decrypt SQL Server Objects

When users create stored procedures, functions, and other SQL Server objects, the text body of those objects is immediately available to anyone who has sufficient (often minimal) permissions. You can see how easy it is to expose object content by creating a stored procedure and scripting its content in Query Analyzer, SQL Server Management Studio (SSMS), Windows PowerShell, or a third-party scripting tool. In many cases, developers and software vendors want to obscure the underlying schema of their products to protect intellectual property and confidential information.

A common way to achieve this goal is by specifying the WITH ENCRYPTION clause inside the CREATE or ALTER statement for each object deployed. However, although the WITH ENCRYPTION option makes object accessibility and readability more difficult, it doesn't prevent advanced users from accessing the code. (See the sidebar "A Brief Background on Encoding and Encryption.") By doing so, users can extract, troubleshoot, and examine code that might otherwise not be easily available. The simple do-it-yourself T-SQL algorithm in this article lets you decode the content of encrypted database objects.

SQL Server 2000 stores the content of text-based objects (i.e., stored procedures, functions, views, triggers) in a system table called syscomments. This table contains an id column that holds the object id and a text column of type NVARCHAR(4000) that stores the CREATE statement in clear text (possibly over multiple rows). In addition, the ctext column is the hexadecimal representation of the corresponding text column entry, which is needed for the decryption process outlined below.

With SQL Server 2005, the text content of each object is available in the system table sys.sql_modules. Here, object_id is the identifier of each object and the definition column captures the object text. Interestingly enough, the hex stream needed for the decryption isn't found in sys.sql_modules. However, SQL Server 2005 offers the syscomments system table as a backward compatible system view (which contains the ctext varbinary column). The inclusion of this view implies that the binary (or the equivalent hex) representation of the DDL is stored somewhere in the database. To track it down and find the binary data source, follow these steps.

Step 1

Log in to a user database in SQL Server 2000 and create a test object by running the following script:

CREATE PROC dbo.uspMyProc   AS SELECT 1 AS MyCol

Step 2

Get the hex representation of the object by executing on the same database the following query:

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

At this point, we have the hex format of the object text. We'll use this information to determine and verify the data source in SQL Server 2005.

Step 3

Log in to a user database in SQL Server 2005 in dedicated administrator connection (DAC) mode, which gives you access to and lets you query system tables and views. Open SSMS, start a new query, type ADMIN: as your server name in the connection dialog box, and enter the sa credentials to log in.

Step 4

Take a snapshot of database tables and their row-counts by executing the following code:

SELECT OBJECT_NAME(id), rows  FROM sysindexes   WHERE indid IN (0, 1)  ORDER BY OBJECT_NAME(id)

You can store the results; an Excel spreadsheet works well. This query returns the name and number of rows in each database table, information that we'll use in upcoming steps for comparison purposes.

Step 5

Create the same object as you did in Step 1, this time in SQL Server 2005 on the database you selected in Step 3. Log in to the database in normal (not DAC) mode and run the following command:

CREATE PROC dbo.uspMyProc   AS SELECT 1 AS MyCol

Step 6

Repeat Step 4 and compare the query results to the results you recorded earlier. It should appear that a new row was added to a table called sys.sysobjvalues, which has a binary column called imageval. Therefore, it's likely that the sys.sysobjvalues table might be the sought-after source of data. This assumption is validated in the next step.

Step 7

Using the DAC connection, get and compare the SQL Server 2005 hex value from sys.sysobjvalues to the hex value you recorded in Step 2 for SQL Server 2000. You can get the value by running the following query:

SELECT imageval FROM sys.sysobjvalues WHERE objid = object_id(‘dbo.uspMyProc')

Voila The result of the query in Step 7 is identical to the ctext column value we captured in Step 2, which proves that the sys.sysobjvalues table indeed contains the hex sequence we want.

To continue, it's important to understand how SQL Server treats the text body of objects that are created WITH ENCRYPTION. The next steps reveal that treatment.

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.

Action 1. Get the encoded text and object code length. In SQL Server 2000, execute the code that you see in Listing 1. For SQL Server 2005 (in DAC mode), you can run the code in Web Listing 1.

Action 2. Build a CREATE statement for a fake object with the same owner/ schema and name as the original item. The actual code of the fake object must have the WITH ENCRYPTION clause, and it must be at least as long as the original encrypted stored procedure, as the code in Listing 2 shows. You can meet this requirement by filling the text body with dashes (or any other valid T-SQL syntax). In addition, we keep the CREATE statement in a variable for later use.

Action 3. Prepare an ALTER statement with the same fake content as in the last action, as the code in Listing 3 shows. Then, execute this statement to obtain the encrypted hex value for the fake item. When the ALTER statement is run, the original object will be overwritten. To ensure that the database isn't changed, the ALTER PROC operation is run within a transaction and is quickly rolled back after the needed information is retrieved.

Action 4. Decode the desired text by applying an XOR operator on the encrypted version of the original object, the CREATE statement of the fake object, and the hex stream of the encrypted fake item, as the code in Listing 4 shows. And that's it. The variable @ContentOfDecryptedObject now holds the decrypted content of dbo.uspMyEncProc.

Encrypted Isn't Always Inaccessible

In the SQL Server 2000's Help files associated with the CREATE PROC statement, the documentation says, "ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement." This description, which is somewhat misleading, has been updated and corrected in SQL Server 2005 Books Online (BOL) and on the MSDN forums to say, "[ENCRYPTION] indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format… However, the text will be available to privileged users." As I've shown, this is indeed the case in practice. For those who really want to protect their code, I offer words of wisdom from long-time SQL Server MVP BP Margolin, who once said, "Your intellectual property is better protected with legal agreements."