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 (http:// www.sqlmag.com, InstantDoc ID 95728).
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."