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:<ServerName> 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.