Background

The ability to “decrypt” an object in SQL Server be it stored procedure, view, function or trigger is something that I have run into a few times. In this post I will show you how you can “decrypt” any sql object using a simple script and highlight the things to be aware of.

Firstly my annoying use of “air quotes” is something that you would have noticed and that is because when you use “WITH ENCRYPTION” you are actually only obfuscating the object definition. The process to “decrypt” is very straight forward and requires only one pre-requiste the Dedicated Admin Connection (DAC) which I will explain later, but first I want to talk about how this solution came to be needed.

Solution History

When I first needed to decrypt objects for SQL Server 2000 I had always used a solution posted by Joseph Gama which is based on work by shoeboy. Here is a link to that SQL 2000 solution. However when I went to use this on SQL 2005 it no longer worked…

The reason why the SQL 2000 solution no longer functioned was because the encrypted object values were no longer present in the ctext column of the syscomments table. They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).

The Dedicated Admin Connection (DAC) is a single connection for the administrator of SQL Server to access the instance. This is important because it gives access to objects that are otherwise hidden.

So that is the basic background to this updated solution. I needed to decrypt objects in later version of SQL Server and so I rewrote a solution using the same decryption methods of the original, but using the new location for the encrypted objects definitions. I also wanted to avoid dropping the encrypted object(s) and so changed this to an alter with rollback, as well as adding in the new object types.

I had kept my rewrite to myself for sometime until it came up on the forum in 2010  http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e7056ca8-94cd-4d36-a676-04c64bf96330/ .

I have since come to realize that perhaps this is not as visible as it could be and so I have given it a new home here where hopefully I can get feedback and address any bugs (undocumented features) without resurrecting that post.

How it works

The decryption method works as it has done in all similar versions of this code.

Encrypted Object ^ Template Object ^ Dummy Encrypted Object

For those interested in exactly what is going on please see the XOR_cipher wikipedia entry.

Getting it to work

In order to decrypt objects as I said you will need to access an object that is not available via a standard connection so for this you will need to enable Dedicated Admin Connection (DAC) if not already enabled.

EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Additionally if you are using SQL Express you will need to enable trace flag 7806.
Then with this done open SSMS, choose File, New, Database Engine Query and set your servername to be admin:ServerName

The last step is to change the code lines to reference the object you want the underlying text for and then check the messages tab.
Below is the code:
https://github.com/jongurgul/code/blob/master/sqlserver/decryption/tsql_object_decryption.sql